Hive的Rank函数_hive rank

一、函数说明

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

1
2
3
4
5
6
7

（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;
1
2
3
4
5
6
7

查询结果：

注;表格可能有点错位，按序查看。

（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;
1
2
3
4
5
6
7
8
9
10
11
12

查询结果：