用戶投資表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、計算每個人每日的累計投資金額 （中級）
根據(jù)用戶投資表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?自關(guān)聯(lián)
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、找出連續(xù)投資三日及以上的用戶、開始日期、連續(xù)天數(shù) （高級）
userid?用戶開始日期連續(xù)天數(shù)
12019/1/14
22019/1/13
思路：通過 row_number 增加排序列 ?rn， 然后用 ?datesub(investdate,rn) ,如果連續(xù)會得到一個固定值 ?，分組后記錄數(shù)大于等于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;
