Hive 窗口函数使用（2）

一、排名函数
注意：排名函数可以跟Over()，但是不能定义window_clause.
在计算名次前，需要先排序！
RANK: 允许并列，一旦有并列跳号！
ROW_NUMBER: 行号！ 连续的，每个号之间差1！
DENSE_RANK： 允许并列，一旦有并列不跳号！
CUME_DIST： 从排序后的第一行到当前值之间数据 占整个数据集的百分比！
PERCENT_RANK： rank-1/ 总数据量-1
NTILE(x): 将数据集均分到X个组中，返回每条记录所在的组号

select *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score

select *,ntile(5) over()
from score

count row_number rank dense_rank
3 1 1 1
3 2 1 1
2 3 3 2
1 4 4 3

一般 rk<=3
二、练习
score.name | score.subject | score.score
// 按照科目进行排名

select *,rank() over(partition by subject order by score desc)
from score

// 给每个学生的总分进行排名
// 输出4条记录
select name,sumscore,rank() over( order by sumscore desc)
from
(select name,sum(score) sumscore
from score
group by name) tmp

// 求每个学生的成绩明细及给每个学生的总分和总分排名

select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name) sumscore
from score) tmp

// 只查询每个科目的成绩的前2名
select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2

//查询学生成绩明细，并显示当前科目最高分
select *,max(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score

 

//查询学生成绩，并显示当前科目最低分
select *,min(score) over(partition by subject)
from score

或
select *,FIRST_VALUE(score) over(partition by subject order by score )
from score