postgresql rank() over, dense_rank(), row_number()用法区别
如下学生表student,学生表中有姓名、分数、课程编号,需要按照课程对学生的成绩进行排序
select * from jinbo.student;
id | name | score | course
-+ -+ -+
5 | elic | 70 | 1
4 | dock | 100 | 1
3 | cark | 80 | 1
2 | bob | 90 | 1
1 | alice | 60 | 1
10 | jacky | 80 | 2
9 | iris | 80 | 2
8 | hill | 60 | 1
7 | grace | 50 | 2
6 | frank | 70 | 2
6 | test | | 2
(11 rows)
1、rank over () 可以把成绩相同的两名是并列,如下course = 2 的结果rank值为:1 2 2 4 5
select name,
score,
course,
rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-+ -+ +
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
2、dense_rank()和rank over()很相似,可以把学生成绩并列不间断顺序排名,如下course = 2 的结果rank值为:1 2 2 3 4
select name,score,
course,
dense_rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-+ -+ +
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 3
grace | 50 | 2 | 4
(11 rows)
3、row_number 可以把相同成绩的连续排名,如下 course = 2 的结果rank值为:1 2 3 4 5
select name,score,
course,
row_number() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-+ -+ +
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 6
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 3
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果,可以如下:
rank over(partition by course order by score desc nulls last)
partition by 用于结果集分组,如果没有指定,会把整个结果集作为一个分组
rank 、dense_rank 、row_numer 都是不同方式的结果集组内排序,一般都结合over 字句出现,over 字句里 会有 partition by、order by、last、first 的任意组合,如下:
rank() over(partition by a,b order by a, order by b desc);
rank() over(partition by a order by b nulls first)
rank() over(partition by a order by b nulls last)
补充:Oracle或者PostgreSQL的row_number over 排名语法
PostgreSQL 和Oracle 都提供了 row_number() over() 这样的语句来进行对应的字段排名,很是方便。MySQL却没有提供这样的语法。
这次我提供的表结构如下,
Table ytt.t1
Column | Type | Modifiers
+ +
i_name | character varying(10) | not null
rank | integer | not null
我模拟了20条数据来做演示。
t_girl=# select * from t1 order by i_name;
i_name | rank
+
Charlie | 12
Charlie | 12
Charlie | 13
Charlie | 10
Charlie | 11
Lily | 6
Lily | 7
Lily | 7
Lily | 6
Lily | 5
Lily | 7
Lily | 4
Lucy | 1
Lucy | 2
Lucy | 2
Ytt | 14
Ytt | 15
Ytt | 14
Ytt | 14
Ytt | 15
(20 rows)
在PostgreSQL下,我们来对这样的排名函数进行三种不同的执行方式1:
完整的带有排名字段以及排序。
t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;
i_name | rank | rank_number
+ + -
Charlie | 13 | 1
Charlie | 12 | 2
Charlie | 12 | 3
Charlie | 11 | 4
Charlie | 10 | 5
Lily | 7 | 1
Lily | 7 | 2
Lily | 7 | 3
Lily | 6 | 4
Lily | 6 | 5
Lily | 5 | 6
Lily | 4 | 7
Lucy | 2 | 1
Lucy | 2 | 2
Lucy | 1 | 3
Ytt | 15 | 1
Ytt | 15 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Ytt | 14 | 5
(20 rows)
带有完整的排名字段但是没有排序。
t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;
i_name | rank | rank_number
+ + -
Charlie | 12 | 1
Charlie | 12 | 2
Charlie | 13 | 3
Charlie | 10 | 4
Charlie | 11 | 5
Lily | 6 | 1
Lily | 7 | 2
Lily | 7 | 3
Lily | 6 | 4
Lily | 5 | 5
Lily | 7 | 6
Lily | 4 | 7
Lucy | 1 | 1
Lucy | 2 | 2
Lucy | 2 | 3
Ytt | 14 | 1
Ytt | 15 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Ytt | 15 | 5
(20 rows)
没有任何排名字段,也没有任何排序字段。
t_girl=# select i_name,rank, row_number() over() as rank_number from t1;
i_name | rank | rank_number
+ + -
Lily | 7 | 1
Lucy | 2 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Charlie | 12 | 5
Charlie | 13 | 6
Lily | 7 | 7
Lily | 4 | 8
Ytt | 14 | 9
Lily | 6 | 10
Lucy | 1 | 11
Lily | 7 | 12
Ytt | 15 | 13
Lily | 6 | 14
Charlie | 11 | 15
Charlie | 12 | 16
Lucy | 2 | 17
Charlie | 10 | 18
Lily | 5 | 19
Ytt | 15 | 20
(20 rows)
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 postgresql rank() over, dense_rank(), row_number()用法区别
相关文章
- docker快速拉起任意版本PostgreSQL测试环境
- postgresql 数据库中replace和translate的用法说明
- PostgreSQL limit的神奇作用详解
- Postgresql 如何选择正确的关闭模式
- PostgreSQL 查找当前数据库的所有表操作
- FREEBSD安装POSTGRESQL笔记
- PostgreSQL 22027: trim_error 报错 故障修复 远程处理
- Postgresql:开启你的数据之旅(进入postgresql)
- 索引索引PostgreSQL数组:极大提高查询性能.(postgresql数组)
- 历程PostgreSQL: 从历史足迹到发展潮流(postgresql发展)
- PostgreSQL新建数据库:快速搭建实现数据存储 (postgresql新建数据库)
- PostgreSQL持续锁表:保证可靠性(postgresql锁表)
- 解决PostgreSQL乱码问题(postgresql乱码)
- PostgreSQL中文手册详解数据库操作技巧(postgresql中文手册)
- 修复PostgreSQL:解决数据库难题(postgresql问题)
- 利用 PostgreSQL 脚本加速数据库操作!(postgresql脚本)
- 深入解析PostgreSQL扩展:优化数据库性能与拓展功能(postgresql扩展)
- 学习PostgreSQL数据库必备!观看高质量视频教程(postgresql视频)
- PostgreSQL语法简介(postgresql语法)
- 深入探索PostgreSQL特性,助力数据库开发与管理(postgresql特性)
- 管理PostgreSQL:优化内存管理提升数据库性能(postgresql内存)