函數(shù)名(字段名) over(子句) 

函數(shù)名(字段名) over(partition by <要分列的組> order by <要排序的列> rows between <數(shù)據(jù)范圍>) 

rows between 2 preceding and current row # 取本行和前面兩行
rows between unbounded preceding and current row # 取本行和之前所有的行 
rows between current row and unbounded following # 取本行和之后所有的行 
rows between 3 preceding and 1 following # 從前面三行和下面一行，總共五行 
# 當(dāng)order by后面沒有rows between時(shí)，窗口規(guī)范默認(rèn)是取本行和之前所有的行
# 當(dāng)order by和rows between都沒有時(shí)，窗口規(guī)范默認(rèn)是分組下所有行（rows between unbounded preceding and unbounded following） 

-- 現(xiàn)有2018~2020某電商平臺(tái)訂單信息表user_trade
create table user_trade (
 user_name  varchar(20) COMMENT '用戶名',
 piece  int COMMENT '購(gòu)買數(shù)量',
 price  double COMMENT '價(jià)格',
 pay_amount double COMMENT '支付金額',
 goods_category varchar(20) COMMENT '商品品類',
 pay_time date COMMENT '支付日期'
);

-- 需求1: 查詢出2019年每月的支付總額和當(dāng)年累積支付總額 
select a.mon,a.pay_amount,sum(a.pay_amount) over(order by a.mon) as sum_amount
from(
select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) = '2019'
group by month(a.pay_time)
) a ;
-- 需求2:查詢出2018-2019年每月的支付總額和當(dāng)年累積支付總額
select a.*,sum(a.pay_amount) over(partition by a.year order by a.mon) as sum_amount
from(
select year(a.pay_time) as year,month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) in('2018','2019')
group by year(a.pay_time),month(a.pay_time)
) a ;

-- 需求3: 查詢出2019年每個(gè)月的近三月移動(dòng)平均支付金額
select a.mon,a.pay_amount,
avg(a.pay_amount) over(order by a.mon rows between 2 preceding and current row) as avg_amount
from(
select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) = '2019'
group by month(a.pay_time)
) a ;

-- 需求4: 查詢出每四個(gè)月的最大月總支付金額
select 
a.mon,
a.pay_amount,
max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amount
from(
select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
group by SUBSTRING(a.pay_time,1,7)
)a ;

-- 需求4: 查詢出每四個(gè)月的最大月總支付金額
select 
a.mon,
a.pay_amount,
max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amount
from(
select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
group by SUBSTRING(a.pay_time,1,7)
)a ;

-- 需求6: 查詢出將2020年2月的支付用戶，按照支付金額分成5組后的結(jié)果
select 
a.user_name,
sum(a.pay_amount) as pay_amount,
ntile(5) over(order by sum(a.pay_amount) desc) as level
from user_trade a
where SUBSTRING(a.pay_time,1,7) = '2020-02'
group by a.user_name;
-- 需求7: 查詢出2020年支付金額排名前30%的所有用戶
select a.user_name,a.pay_amount
from (
select 
a.user_name,
sum(a.pay_amount) as pay_amount,
ntile(10) over(order by sum(a.pay_amount) desc) as level
from user_trade a
where year(a.pay_time) = '2020'
group by a.user_name
) a 
where a.level in(1,2,3);

-- 需求8: 查詢出King和West的時(shí)間偏移(前N行)
select a.user_name,a.pay_time,
lag(a.pay_time,1,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag1,
-- 沒有傳入偏移量，那么默認(rèn)就是1，找不到的話，此處也沒有給默認(rèn)值，為null
lag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lag2,
lag(a.pay_time,2,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag3,
lag(a.pay_time,2) over(partition by a.user_name order by a.pay_time) as lag4
from user_trade a 
where a.user_name in('King','West');

-- 需求9: 查詢出支付時(shí)間間隔超過100天的用戶數(shù)
select count(distinct a.user_name)
from (
select a.user_name,a.pay_time,
lag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lg
from user_trade a 
) a 
where DATEDIFF(a.pay_time,a.lg) >100;
# 需求9運(yùn)行結(jié)果為180
-- 需求10: 查詢出每年支付時(shí)間間隔最長(zhǎng)的用戶
select c.years,c.user_name,c.pay_days 
from(
select b.years,b.user_name,datediff(b.pay_time,b.lg) as pay_days,
rank() over(partition by b.years order by datediff(b.pay_time,b.lg) desc) as rk 
from (
select year(a.pay_time) as years,a.user_name,a.pay_time,
lag(a.pay_time) over(partition by a.user_name,year(a.pay_time) order by a.pay_time) as lg
from user_trade a 
) b 
where b.lg is not null
) c 
where c.rk = 1;