大数据第51天—Mysql练习题12道之六-今年10月份第一次购买商品的金额-杨大伟
请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）
1 create table test_six_ordertable 2 ( 3 `userid` string COMMENT '购买用户', 4 `money` decimal(10,2) COMMENT '金额', 5 `paymenttime` string COMMENT '购买时间', 6 `orderid` string COMMENT '订单id' 7 ) 8 row format delimited fields terminated by '\t';
1 --插入数据 2 insert into table test_six_ordertable values('1',1,'2017-09-01','1'); 3 insert into table test_six_ordertable values('2',2,'2017-09-02','2'); 4 insert into table test_six_ordertable values('3',3,'2017-09-03','3'); 5 insert into table test_six_ordertable values('4',4,'2017-09-04','4'); 6 7 insert into table test_six_ordertable values('3',5,'2017-10-05','5'); 8 insert into table test_six_ordertable values('6',6,'2017-10-06','6'); 9 insert into table test_six_ordertable values('1',7,'2017-10-07','7'); 10 insert into table test_six_ordertable values('8',8,'2017-10-09','8'); 11 insert into table test_six_ordertable values('6',6,'2017-10-16','60'); 12 insert into table test_six_ordertable values('1',7,'2017-10-17','70');
1 -- 写出所有用户中在今年10月份第一次购买商品的金额 2 select 3 userid, 4 `money`, 5 paymenttime, 6 orderid 7 from 8 ( 9 select 10 userid, 11 `money`, 12 paymenttime, 13 orderid, 14 rank() over(partition by userid order by paymenttime) rank_time 15 from test_six_ordertable 16 where date_format(paymenttime,'yyyy-MM') = '2017-10' 17 ) a 18 where rank_time=1;
