1 明确数据分析目标
统计所有订单中的每种分类对应的商品的个数,构建词云图
2 创建用于保存数据分析结果的表
create table app_cat_cnt(
id int primary key auto_increment,
daystr varchar(20),
catName varchar(100),
cnt int
);
3 编写SQL语句进行数据分析
select
substring(a.createTime,1,10) as daystr,
c.catName,
count(*) as cnt
from ods_finebi_order_goods a
join ods_finebi_goods b on a.goodsId = b.goodsId
join ods_finebi_good_cats c on b.goodsCatId = c.catId
group by c.catName, substring(a.createTime,1,10)
order by cnt desc;
4 加载到结果表中
insert into app_cat_cnt
select
null,
substring(a.createTime,1,10) as daystr,
c.catName,
count(*) as cnt
from ods_finebi_order_goods a
join ods_finebi_goods b on a.goodsId = b.goodsId
join ods_finebi_good_cats c on b.goodsCatId = c.catId
group by c.catName, substring(a.createTime,1,10)
order by cnt desc;