hive窗口函数

over()开窗

按我的理解，开窗函数就是开出一个小窗口，对小窗口内的数据统计处理。

累计计算窗口函数

sum() over()

比如我们有个需求要查2018年每个月销售额及年累计的销售额
如下是我们trade表

select a.month, a.amount, sum(a.amount) over(order by a.month)
from
(select month(dt) month, sum(pay_amount) amount
from trade
where year(dt)=2018
group by month(dt)) as a

image.png

但是我们要查2018和2019的怎么办？
over函数中要进行分组

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

image.png

• avg() over()
  需求：计算每个月近3月的平均销售额

select a.ym, a.amount, avg(a.amount) over(order by ym rows between 2 preceding and current row)
from 
(select substr(dt, 1,7) ym, sum(pay_amount) amount
from user_trade
where substr(dt, 1,7) between '2017-01' and '2019-01'
group by substr(dt, 1,7)
order by substr(dt, 1,7)) a;

image.png

• rows between unbounded preceding and current row 包括本行和之前所有行
• rows between current row and unbounded following 包括本行和之后所有的行
• rows between 3 preceding and current row 包括本行和之前3行
• row between 3 preceding and 1 following 从前三行到下1行

分区排序窗口函数

row_number() over([partition by order by])
每一行都有唯一序号
rank() over([partition by order by])
跳跃式序号
比如小王和小红的成绩都是90，并列班级第7名，那么下一名的序号就是9,8被跳过
dense_rank() over([partition by order by])
密集序号
比如小王和小红的成绩都是90，并列班级第7名，那么下一名的序号就是8

分组排序窗口函数

ntile(n) over(...)
ntile(n) over([partition by ... order by ...])
将分组数据按照顺序切分为n片，返回当前切片值

偏移分析窗口函数

lag和lead分析函数可以在同一次查询中取出同一字段的前n行的数据和后n行数据作为独立的列。这个功能如同pandas中的shift函数一样
lag(exp_str, offset, default_val) over(partition by ... order by ...)
lead(exp_str, offset, default_val) over(partition by ... order by ...)