用户投资表invest
userid 用户investdate
投资日期
investamount
投资金额
12019-01-01100
12019-01-02200
12019-01-03300
12019-01-04400
22019-01-01200
22019-01-02300
22019-01-03400
32019-01-01300
32019-01-02400
create table invest(userid string ,investdate string ,investamount string);
insert into invest values(1,'2019-01-01',100);
insert into invest values(1,'2019-01-02',200);
insert into invest values(1,'2019-01-03',300);
insert into invest values(1,'2019-01-04',400);
insert into invest values(2,'2019-01-01',200);
insert into invest values(2,'2019-01-02',300);
insert into invest values(2,'2019-01-03',400);
insert into invest values(3,'2019-01-01',300);
insert into invest values(3,'2019-01-02',400);
insert into invest values(3,'2019-01-04',400);
insert into invest values(1,'2019-01-06',400);
insert into invest values(2,'2019-01-05',400);
1、平均每次投资大于300的用户 （初级）
select userid, avg(investamount)
from invest
group by userid
having avg(investamount) > 300
2、计算每个人每日的累计投资金额 （中级）
根据用户投资表invest表，生成如下表：
用户userid 投资日期investdate投资金额investamount累计投资
12019/1/1100100
12019/1/2200300
12019/1/3300600
12019/1/44001000
22019/1/1200200
22019/1/2300500
22019/1/3400900
32019/1/1300300
32019/1/2400700
方法一、sum() over（）
select userid, investdate ,investamount, sum(investamount) over(partition by userid order by investdate)
from invest
方法二、通过userid 自关联
select a.userid, a.investdate ,a.investamount, sum(b.investamount)
from invest a left join invest b on a.userid = b.userid
where a.investdate >= b.investdate
group by a.userid, a.investdate ,a.investamount
3、找出连续投资三日及以上的用户、开始日期、连续天数 （高级）
userid 用户开始日期连续天数
12019/1/14
22019/1/13
思路：通过 row_number 增加排序列 rn， 然后用 datesub(investdate,rn) ,如果连续会得到一个固定值 ，分组后记录数大于等于3的
select userid,startdate,count(1)
from
(
select userid,
investdate,
row_number() over(partition by userid order by investdate) rn,
date_sub(investdate,row_number() over(partition by userid order by investdate) -1) startdate
from invest
) a
group by userid,startdate
having count(1) >= 3;
