一、函数说明
- RANK() 排序相同时会重复,总数不会变
- DENSE_RANK() 排序相同时会重复,总数会减少
- ROW_NUMBER() 会根据顺序计算
- PERCENT_RANK:百分比排序
二、示例说明
示例表:
(1)创建hive表并导入数据
create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/score.txt' into table score;
(2)计算每门学科的成绩排名、
select
*,
rank() over(partition by subject order by score desc) r,
dense_rank() over(partition by subject order by score desc) dr,
row_number() over(partition by subject order by score desc) rr
from
score;
查询结果:
注;表格可能有点错位,按序查看。
(3)求出每门学科前三名的学生
select
*,
r
from
(select
*,
rank() over(partition by subject order by score desc) r,
dense_rank() over(partition by subject order by score desc) dr,
row_number() over(partition by subject order by score desc) rr
from
score) as t2
where r<=3;
查询结果: