实践rank,dense_rank,row_number区别

最新推荐文章于 2024-10-22 10:46:08 发布

原创最新推荐文章于 2024-10-22 10:46:08 发布 · 504 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive

Hive 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍SQL中的row_number(), dense_rank() 和 rank() 函数的使用方法及区别，并通过实例演示如何进行分组排序，适用于数据库操作及数据分析人员。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、新建表
create table score(name string,subject string,score string);
2、插入数据
insert into score values('1','En','80'),('2','En','80'),('3','En','90'),('4','En','60'),('5','En','70'),('6','En','81'),('8','En','50')
insert into score values('1','Math','80'),('2','Math','80'),('3','Math','90'),('4','Math','60'),('5','Math','70'),('6','Math','81'),('8','Math','50')
3、查询
select * from score

score.name score.subject score.score
0 1 En 80
1 2 En 80
2 3 En 90
3 4 En 60
4 5 En 70
5 6 En 81
6 8 En 50
7 1 Math 80
8 2 Math 80
9 3 Math 90
10 4 Math 60
11 5 Math 70
12 6 Math 81
13 8 Math 50
4、row_number()
select subject,score,row_number() over(partition by subject order by score desc) rank
from score

subject score rank
7 En 90 1
8 En 81 2
9 En 80 3
10 En 80 4
11 En 70 5
12 En 60 6
13 En 50 7
0 Math 90 1
1 Math 81 2
2 Math 80 3
3 Math 80 4
4 Math 70 5
5 Math 60 6
6 Math 50 7
5、dense_rank()
select subject,score,dense_rank() over(partition by subject order by score desc) rank
from score
subject score rank
0 Math 90 1
1 Math 81 2
2 Math 80 3 <----允许有并列名次出现
3 Math 80 3 <----并列名次之后继续按照顺序排名
4 Math 70 4
5 Math 60 5
6 Math 50 6
7 En 90 1
8 En 81 2
9 En 80 3
10 En 80 3
11 En 70 4
12 En 60 5
13 En 50 6

6、rank()

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

subject score rank
0 Math 90 1
1 Math 81 2
2 Math 80 3 <----并列排名之后继续递增排序
3 Math 80 3
4 Math 70 5
5 Math 60 6
6 Math 50 7
7 En 90 1
8 En 81 2
9 En 80 3
10 En 80 3
11 En 70 5
12 En 60 6
13 En 50 7
7、总结
语法(用法)：
rank() over([partition by col1] order by col2)
dense_rank() over([partition by col1] order by col2)
row_number() over([partition by col1] order by col2)
其中[partition by col1]可省略。

区别
三个分析函数都是按照col1分组内从1开始排序

row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
dense_rank() 是连续排序，两个第二名仍然跟着第三名
rank() 是跳跃拍学，两个第二名下来就是第四名
8、删除表
drop table score