hiveSQL基本语句四----各种窗口函数

一、累计计算

–1、sum(…) over(…)

步骤：
1)一个分组条件，累计求一个变量的和
2）先选出一个表，并按照条件分好组
3）在2表基础上，sum(求和项) over(分组项)

select a.month,a.pay_amount,
sum(a.pay_amount) over(order by a.month)
from
(select month(dt) month,sum(pay_amount) pay_amount
from user_trade
where year(dt)=2018
group by month(dt)) a;

–2、sum(…) over(partition by … order by …)

注意：
partition by 起到分块，分别累加的作用
order by 按照什么顺序累加

select a.year,a.month,a.pay_amount,
sum(a.pay_amount) over(partition by a.year order by a.month)
from
(select year(dt) year,month(dt) month,sum(pay_amount) pay_amount
from user_trade
where year(dt) in (2017,2018)
group by year(dt),month(dt)) a;

3、平均值avg(…) over(…)

例：近3个月平均值

select a.month,a.pay_amount,
avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row)
from
(select month(dt) month,sum(pay_amount) pay_amount
from user_trade
where year(dt)=2018
group by month(dt)) a;

总结：
sum(A)/avg(A) over(partition by B order C rows between D and E)
rows between unbounded preceding and current row #本行和之前所有行
rows between current row and unbounded following #本行和之后所有行
rows between 2 preceding and current row #本行和前两行
rows between 2 preceding and 2 following #从前两行到后两行，共五行

二、分区排序窗口函数

1、排序窗口函数

• row_number() over(partition by A order by B) 依次排序不重复

• rank() over(partition by A order by B) 出现相同排名，跳跃排序，即1,1,3,4,4,6

• dense_rank() over(partition by A order by B)
  出现相同排名，不跳跃排序，即1,1,2,3,3,4

例：2019年1月，用户购买商品品类数量的排名

select user_name,
count(distinct goods_category),
row_number() over(order by count(distinct goods_category)),
rank() over(order by count(distinct goods_category)),
dense_rank() over(order by count(distinct goods_category))
from user_trade
where substr(dt,1,7)='2019-01'
group by user_name;

很常用的场景：
2019年支付金额排名在10,20,30的用户

select a.user_name
from
(select user_name,dense_rank() over(order by sum(pay_amount) desc) pay_order
from user_trade
where year(dt)=2019
group by user_name) a
where a.pay_order in (10,20,30);

三、分组排序窗口函数

1、ntile(n) over(partition by … order by …)
n为切片数
若切片不均匀，则多余的算在第一组
不支持rows between
例：2019年1月的支付用户，按支付金额分成五组

select user_name,
sum(pay_amount)
ntile(5) over (order by sum(pay_amount) desc) as level
from user_trade
where substr(dt,1,7)='2019-01'
group by user_name;

四、偏移分析窗口函数

1、lag(exp_str,offset,defval) over(patition by … order by …)
取出同一字段前offset行数据
lead(exp_str,offset,defval) over(patition by … order by …)
取出同一字段后offset行数据
exp_str：字段名称
offset：偏移量
defval：默认值，偏移量超出表范围时，该字段使用的值。若不设置该值，超出时用NULL填写

例：支付时间间隔超过100天的用户数

select count(distinct a.user_name)
from
(select user_name,
dt,
lead(dt) over(partition by user_name order by dt) lead_dt
from user_trade
where dt>'0') a
where a.lead_dt is not null and datediff(a.lead_dt,a.dt) > 100;

练习：每个城市，不同性别，2018年支付金额最高的TOP3用户（user_trade,user_info)
此处自己的理解是partition和order by的参数都要提前分组好，实际是只要order by的已经分组好可以实现窗口即可，
分组部分可以在窗口中按照partition自己分并显示分组后结果，无需提前group by准备好

select c.city,c.sex,c.user_name
from
(select b.city city,
b.sex sex,
a.user_name user_name,
a.sum_pay sum_pay,
dense_rank() over(partition by b.city,b.sex order by a.sum_pay desc) dr
from
(select user_name,sum(pay_amount) sum_pay
from user_trade
where year(dt)=2018
group by user_name) a
left join user_info b
on a.user_name=b.user_name    #此处自己写的时候在后面加了group by b.city,b.sex，会报错
) c
where c.dr < 4;