HIVE窗口函数合集 -- 持续更新

建议参考阿里云 MaxCompute 的这份 窗口函数 的文档，写得非常详细，强烈推荐！

NTILE – 将分组数据按照顺序切片，并返回切片值

NTILE(NUMBER) OVER(PARTITION BY COL1 ORDER BY COL2)：按照分组排序后将数据切分为n个分区:
若组内数据比分区数多（一组8条数据，要分为6个分区）：则先分满6个（1至6）在从小到大分区（即1至2）最后分区是（11223456）
若组内数据比分区数少（一组8条数据，要分为10个分区）：则按照顺序分区即可（12345678）

RANK – 计算跳跃排名

RANK() OVER(PARTITION BY COL1 ORDER BY COL2):分组排序后将数据组内标出排序序号，相同值排序序号并列且占下一位排序序号
例如科目1有学生成绩如下：100,90,90,80,70
select rank() over(partition by subject order by score desc) 排序结果为 1,2,2,4,5

DENSE_RANK – 计算连续排名

DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2):分组排序后组内标出排序序号，相同排序序号并列且不会占下一位排序序号
例如科目1有学生成绩如下：100,90,90,80,70
select dense_rank() over(partition by subject order by score desc) 排序结果为 1,2,2,3,4

ROW_NUMBER – 计算行号

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2):分组排序后组内标出排序序号，相同排序序号不并列
例如有一科目,学生成绩如下：100,90,90,80,70
select row_number() over(partition by subject order by score desc) 排序结果为 1,2,3,4,5
可参考NTILE,ROW_NUMBER,RANK,DENSE_RANK

LAG – 按偏移量取当前行之前第几行的值

LAG(COL2,NUMBER,DEFAULT) OVER(PARTITION BY COL1 ORDER BY COL2):向前（这里根据COL2排序后来判断前后，不论COL2升序还是降序，取往排序后的相对于当前行的前(上)第NUMBER行数据）
例如按照降序排序后比较当前序号与相差4个序号的学生的成绩差值
有一科目学生成绩如下：100,90,90,80,70,60,50,40
每个当前数据都要和相对当前数据的前四位做差值，即从70开始（因为100,90,90,80他们都没有前面的4位）。70和100做差，60和90做差，50和90做差，40和80做差
前4位（100,90,90,80）没有做差对象此处填写传入的第三个参数DEFAULT
select score,lag(score,4,0) over(partition by subject order by score desc) as irank
最后数据如下：

score	irank
100	0
90	0
90	0
80	0
70	100
60	90
50	90
40	80

LEAD – 按偏移量取当前行之后第几行的值

LEAD(COL2,NUMBER,DEFAULT) OVER(PARTITION BY COL1 ORDER BY COL2):区别于LAG，是向后(下)取NUMBER位
例如有一科目学生成绩如下：100,90,90,80,70,60,50,40
select score,lead(score,4,0) over(partition by subject order by score desc) as irank
最后数据如下：

score	irank
100	70
90	60
90	50
80	40
70	0
60	0
50	0
40	0

FIRST_VALUE – 计算组内排第一的值

FIRST_VALUE() OVER(PARTITION BY COL1 ORDER BY COL2):分组排序后取组内排序第一位的值
例如有两门学科学生成绩如下：A学科100,90,80,70；B学科90,85,75,60
现在取该学科(即分组)第一的学生成绩与每一名学生的成绩做差
select subject,score,first_value(score) over(partition by subject order by score desc) as irank
最后数据如下：

学科	每名学生成绩	学科内最好成绩
A	100	100
A	90	100
A	80	100
A	70	100
B	90	90
B	85	90
B	75	90
B	60	90

LAST_VALUE – 计算组内排倒数第一的值

LAST_VALUE() OVER(PARTITION BY COL1 ORDER BY COL2):区别于FIRSE_VALUE，是取组内排序最后一位的值
例如有两门学科学生成绩如下：A学科100,90,80,70；B学科90,85,75,60
现在取该学科(即分组)第一的学生成绩与最后一名学生的成绩做差
select subject,score,last_value(score) over(partition by subject order by score desc) as irank
最后数据如下：

学科	每名学生成绩	学科内最差成绩
A	100	70
A	90	70
A	80	70
A	70	70
B	90	60
B	85	60
B	75	60
B	70	60

可参考LAG,LEAD,FIRST_VALUE,LAST_VALUE

PERCENT_RANK – 组内当前行RANK值-1/分组内总行-1

PERCENT_RANK() OVER(PARTITION BY COL1 ORDER BY COL2):分组内当前行的RANK值-1/分组内总行数-1
例如有两门学科学生成绩如下：A学科100,90,80,70；B学科90,85,75,60
现在取该学科(即分组)第一的学生成绩与最后一名学生的成绩做差
select subject,score,persent_rank() over(partition by subject order by score desc) as irank
最后数据如下：

学科	score	irank
A	100	0.0
A	90	0.333…
A	80	0.666…
A	70	1.0
B	90	0.0
B	85	0.333…
B	75	0.666…
B	70	1.0

PS:

• 第一行0.0计算方法：当前行序号为1，组内总行数为4。则（1-1）/（4-1） = 0.0
• 第二行0.333…计算方法：当前行序号为2，组内总行数为4。则（2-1）/（4-1） = 0.333…
• 依次逻辑类推其他irank,得出计算结果

CUME_DIST – 当前值的行数/分组内总行数

CUME_DIST() OVER(PARTITION BY COL1 ORDER BY COL2):
order by col2 asc:为小于等于当前值的行数/分组内总行数
order by col2 desc:为大于等于当前值的行数/分组内总行数
例如有两门学科学生成绩如下：A学科100,90,80,70；B学科90,85,75,60
现在取该学科(即分组)第一的学生成绩与最后一名学生的成绩做差
select subject,score
,cume_dist() over(partition by subject order by score desc) as irank_desc
,cume_dist() over(partition by subject order by score asc) as irank_asc
最后数据如下：

学科	score	irank_desc	irank_asc
A	100	0.25	1.0
A	90	0.5	0.75
A	80	0.75	0.5
A	70	1.0	0.25
B	90	0.25	1.0
B	85	0.5	0.75
B	75	0.75	0.5
B	70	1.0	0.25

可参考percent_rank / cume_dist

MEDIAN – 计算中位数。

返回组内中位数
select subject,score,median(score) over(partition by subject) as median_num

subject	score	median_num
A	100	90
A	90	90
A	80	90
B	95	75
B	75	75
B	60	75

GROUPING SETS

指定多维度分组聚合

CUBE

根据GROUP BY的维度的所有组合进行聚合
下图中with cube等价于 grouping sets( (), (idate), (itype), (idate,itype) ),即分区字段全排列

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。
下图中with rollup等价于 grouping sets( (), (idate), (idate,itype) ),即分区字段按左字段排列

GROUPING_ID

用于区别结果属于哪个分组

WINDOW子句

n PRECEDING：向前n行；
CURRENT：当前行；
n FOLLOWING：向后n行；
UNBOUNDED：边界；
例如：

• 1.起点到当前行的聚合
  between UNBOUNDED PRECEDING and CURRENT ROW
• 2.当前行及后面所有行
  between CURRENT ROW and UNBOUNDED FOLLOWING
• 3.当前行和前面一行做聚合
  between CURRENT ROW and 1 PRECEDING
• 4.当前行和后面一行做聚合
  between CURRENT ROW and 1 FOLLOWING
• 4.当前行和前一行和后一行做聚合
  between 1 PRECEDING and 1 FOLLOWING