杰晶网络

postgresql rank() over, dense_rank(), row_number()用法区别
数据库 2024/11/23 佚名
3 2

如下学生表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)

4、总结

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)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

标签：
postgresql,rank,over,dense_rank,row_number
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
www.imxmx.com 杰晶网络
8,675无损音乐
1,324高清电影
213破解软件
120,141站长资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/11/23

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新动态

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网