hive开窗/窗口函数

想象这样一种场景，既想保留所有数据，又想得到按某几列分组的聚合值，或者再对数据进行排序，要如何实现呢？这时候开窗函数就有了用武之地，聚合函数每组只保留一个值，而开窗函数可以在不减少原表行数的情况下，实现分组和排序的功能。

语法规则

窗口函数 over (partition by <用于分组的列名> order by <用于排序的列名> [desc] <倒序排列>)

排位函数

括号里留空，不写参数

1. rank() 相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。
2. dense_rank() 相等的值排名相同，但序号从1到n连续。如果有两个人都排在第3名，则第五个人排在第4名。
3. row_number() 相等的值对应的排名不同，序号从1到n连续。可以理解为行号。

select *,
   rank() over (partition by class order by score desc) as ranking,
   dense_rank() over (partition by class order by score desc) as dese_rank,
   row_number() over (partition by class order by score desc) as row_num
from test.demo_windows;

聚合函数

sum()，count()，max()，min()， avg() 等
聚合函数作为窗口函数相当于截止到当前数据的累计值

select *,
   sum(score) over (partition by class order by score desc) as current_sum,
   avg(score) over (partition by class order by score desc) as current_avg,
   count(score) over (partition by class order by score desc) as current_count,
   max(score) over (partition by class order by score desc) as current_max,
   min(score) over (partition by class order by score desc) as current_min
from test.demo_windows;

从上图的结果我们可以发现，当order by 排序的字段相同时，相同的数据会一起计算出来，要注意计算的是截止到当前值而不是当前行。如果要实现逐行累计，则需要添加语句：

rows between unbounded preceding and current row

表明范围，从第一行到当前行

select *,
   sum(score) over (partition by class order by score desc rows between unbounded preceding and current row) as current_sum,
   avg(score) over (partition by class order by score desc rows between unbounded preceding and current row) as current_avg,
   count(score) over (partition by class order by score desc rows between unbounded preceding and current row) as current_count,
   max(score) over (partition by class order by score desc rows between unbounded preceding and current row) as current_max,
   min(score) over (partition by class order by score desc rows between unbounded preceding and current row) as current_min
from test.demo_windows;

偏移函数

1. lead(col,n,默认值) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值，当往下第n行为NULL时取默认值，如不指定则为NULL。
2. lag(col,n,默认值) ：与lead相反，用于统计窗口内往上第n行值。
3. first_value()：取分组内排序后，截止到当前行，第一个值
4. last_value()： 取分组内排序后，截止到当前行，最后一个值

select *, 
lead(name,1) over (partition by class order by score desc) as lead_1, 
lead(name,1,999) over (partition by class order by score desc) as lead_null, 
lag(name,1) over (partition by class order by score desc) as lag_1,
lag(name,1,999) over (partition by class order by score desc) as lag_null,
first_value(name) over (partition by class order by score desc) as first_value,
last_value(name) over (partition by class order by score desc) as last_value
from test.demo_windows ;

分布函数

1. ntile(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布，各个切片能放的数据条数最多相差1。
   按分位数统计的时候可以用，比如取销量前四分之一的数据，筛选ntile(4) = 1 即为想要的结果

select *, 
	ntile(2) over (partition by class order by score desc) as nt_2, 
	ntile(3) over (partition by class order by score desc) as nt_3 
from test.demo_windows ;

2. CUME_DIST() 小于等于当前值的行数/分组内总行数；如果是降序排列，则统计大于等于当前值的行数/总行数。
3. PERCENT_RANK() 分组内当前行的RANK值-1/分组内总行数-1。

select *, 
cume_dist() over (partition by class order by score desc) as cume_dist, 
percent_rank() over (partition by class order by score desc) as percent_rank 
from test.demo_windows ;

参考文章：
https://zhuanlan.zhihu.com/p/92654574
https://blog.youkuaiyun.com/dingchangxiu11/article/details/83145151