Hive排名函数

row_number() rank() dense_rank()

1、row_number()

row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
 

效果如下：
98		1
97		2
97		3
96		4
95		5
95		6

没有并列名次情况，顺序递增

2、rank()

生成数据项在分组中的排名，排名相等会在名次中留下空位

效果如下：
98		1
97		2
97		2
96		4
95		5
95		5
94		7
有并列名次情况，顺序跳跃递增

3、dense_rank()

生成数据项在分组中的排名，排名相等会在名次中不会留下空位

效果如下：
98		1
97		2
97		2
96		3
95		4
95		4
94		5
有并列名次情况，顺序递增

4、案例演示

stu_score.txt

1 gp1808 80
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86

建表，加载数据：

create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited 
fields terminated by ' ';

load data local inpath '/home/hivedata/stu_score.txt' overwrite into table stu_score;

create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited 
fields terminated by ' ';

load data local inpath '/home/hivedata/stu_score.txt' overwrite into table stu_score;

需求一：对每个班级的每次考试按照考试成绩倒序

 

select *,dense_rank() over(partition by classno order by  score desc) from stu_score;

select *,dense_rank() over(order by score desc) `全年级排名`  from stu_score;

 需求二：获取每次考试的排名情况

select *,
-- 没有并列，相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1,
-- rank()：有并列，相同名次空位
rank() over(distribute by classno sort by score desc) rn2,
-- dense_rank()：有并列，相同名次不空位
dense_rank() over(distribute by classno sort by score desc) rn3
from stu_score;
运行结果：

26      gp1805  94      1       1       1
21      gp1805  94      2       1       1
25      gp1805  92      3       3       2
20      gp1805  92      4       3       2
23      gp1805  88      5       5       3
27      gp1805  86      6       6       4
22      gp1805  86      7       6       4
24      gp1805  80      8       8       5
19      gp1805  80      9       8       5
11      gp1807  92      1       1       1
16      gp1807  92      2       1       1
10      gp1807  90      3       3       2
14      gp1807  88      4       4       3
13      gp1807  86      5       5       4
18      gp1807  86      6       5       4
12      gp1807  84      7       7       5
17      gp1807  84      8       7       5
15      gp1807  80      9       9       6
7       gp1808  98      1       1       1
2       gp1808  92      2       2       2
5       gp1808  88      3       3       3
9       gp1808  86      4       4       4
4       gp1808  86      5       4       4
8       gp1808  84      6       6       5
3       gp1808  84      7       6       5
1       gp1808  80      8       8       6
6       gp1808  70      9       9       7

需求三：求每个班级的前三名

select * from (
select * ,dense_rank() over(partition by classno order by score desc) as paiming from stu_score) t  where  paiming <=3;