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 |
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