大数据第48天—Mysql练习题12道之三-表STG.ORDER-杨大伟
已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)
1 drop table if exists test_three_ORDER; 2 3 4 create table test_three_ORDER 5 6 7 ( 8 9 10 `Date` String COMMENT '下单时间', 11 12 13 `Order_id` String COMMENT '订单ID', 14 15 16 `User_id` String COMMENT '用户ID', 17 18 19 `amount` decimal(10,2) COMMENT '金额' 20 21 22 ) 23 24 25 row format delimited fields terminated by '\t';
1 --插入数据 2 insert into table test_three_ORDER values ('2017-10-01','10029011','1000003251',19.50); 3 insert into table test_three_ORDER values ('2017-10-03','10029012','1000003251',29.50); 4 insert into table test_three_ORDER values ('2017-10-04','10029013','1000003252',39.50); 5 insert into table test_three_ORDER values ('2017-10-05','10029014','1000003253',49.50); 6 insert into table test_three_ORDER values ('2017-11-01','10029021','1000003251',130.50); 7 insert into table test_three_ORDER values ('2017-11-03','10029022','1000003251',230.50); 8 insert into table test_three_ORDER values ('2017-11-04','10029023','1000003252',330.50); 9 insert into table test_three_ORDER values ('2017-11-05','10029024','1000003253',430.50); 10 insert into table test_three_ORDER values ('2017-11-07','10029025','1000003254',530.50); 11 insert into table test_three_ORDER values ('2017-11-15','10029026','1000003255',630.50); 12 insert into table test_three_ORDER values ('2017-12-01','10029027','1000003252',112.50); 13 insert into table test_three_ORDER values ('2017-12-03','10029028','1000003251',212.50); 14 insert into table test_three_ORDER values ('2017-12-04','10029029','1000003253',312.50); 15 insert into table test_three_ORDER values ('2017-12-05','10029030','1000003252',412.50); 16 insert into table test_three_ORDER values ('2017-12-07','10029031','1000003258',512.50); 17 insert into table test_three_ORDER values ('2017-12-15','10029032','1000003255',612.50);
1 -- 1）给出 2017年每个月的订单数、用户数、总成交金额。 2 3 select 4 date_format(`date`,'yyyy-MM') `date`, 5 count(*) `订单数`, 6 count(distinct(user_id)) `用户数`, 7 sum(amount) `总成交金额` 8 from test_three_ORDER 9 group by date_format(`date`,'yyyy-MM'); 10 11 -- 2）给出2017年11月的新客数(指在11月才有第一笔订单) 12 select 13 t1.user_id 14 from 15 ( 16 select 17 user_id 18 from test_three_ORDER 19 where date_format(`date`,'yyyy-MM') = '2017-11' 20 group by user_id 21 ) t1 22 left join 23 ( 24 select 25 user_id 26 from test_three_ORDER 27 where date_format(`date`,'yyyy-MM') < '2017-11' 28 group by user_id 29 ) t2 30 on t1.user_id = t2.user_id 31 where t2.user_id is null; 32 -- 第二种写法 33 select 34 count(User_id) `11月新客数` 35 from 36 ( 37 SELECT 38 User_id, 39 Order_id, 40 `Date`, 41 LAG (`DATE`,1,0) over(partition by User_id order by `Date`) preOrder 42 FROM 43 test_three_ORDER 44 ) t1 45 where date_format(`date`,'yyyy-MM')='2017-11' and preOrder=0;
