1 明确数据分析目标
对所有商品的商家时间进行统计,统计每个小时上架商品的个数
2 创建用于保存数据分析结果的表
create table app_hour_goods(id int primary key auto_increment,daystr varchar(20),hourstr varchar(20),cnt int
);
3 编写SQL语句进行数据分析
selectsubstring(createTime,1,10) as daystr,substring(createTime,12,2) as hourstr,count(*) as cnt
from ods_finebi_goods
group bysubstring(createTime,1,10),substring(createTime,12,2)
order by hourstr;
4 加载数据到结果表中
insert into app_hour_goods
selectnull,substring(createTime,1,10) as daystr,substring(createTime,12,2) as hourstr,count(*) as cnt
from ods_finebi_goods
group bysubstring(createTime,1,10),substring(createTime,12,2)
order by hourstr;