大数据第51天—Mysql练习题12道之六-今年10月份第一次购买商品的金额-杨大伟

请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）

 

create table test_six_ordertable
(
    `userid` string COMMENT '购买用户',
    `money` decimal(10,2) COMMENT '金额',
    `paymenttime` string COMMENT '购买时间',
    `orderid` string COMMENT '订单id'
)
row format delimited fields terminated by '\t';

 

--插入数据
insert into table test_six_ordertable values('1',1,'2017-09-01','1');
insert into table test_six_ordertable values('2',2,'2017-09-02','2');
insert into table test_six_ordertable values('3',3,'2017-09-03','3');
insert into table test_six_ordertable values('4',4,'2017-09-04','4');

insert into table test_six_ordertable values('3',5,'2017-10-05','5');
insert into table test_six_ordertable values('6',6,'2017-10-06','6');
insert into table test_six_ordertable values('1',7,'2017-10-07','7');
insert into table test_six_ordertable values('8',8,'2017-10-09','8');
insert into table test_six_ordertable values('6',6,'2017-10-16','60');
insert into table test_six_ordertable values('1',7,'2017-10-17','70');

-- 写出所有用户中在今年10月份第一次购买商品的金额
select
    userid,
    `money`,
    paymenttime,
    orderid
from
(
    select
        userid,
        `money`,
        paymenttime,
        orderid,
        rank() over(partition by userid order by paymenttime) rank_time
    from test_six_ordertable
    where date_format(paymenttime,'yyyy-MM') = '2017-10'
) a
where rank_time=1;