服务器之家

服务器之家 > 正文

postgresql rank() over, dense_rank(), row_number()用法区别

时间:2021-02-26 18:04     来源/作者:zdp072

如下学生表student,学生表中有姓名、分数、课程编号,需要按照课程对学生的成绩进行排序

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 的任意组合,如下:

?
1
2
3
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却没有提供这样的语法。

这次我提供的表结构如下,

?
1
2
3
4
5
    Table "ytt.t1"
 Column |   Type   | Modifiers
--------+-----------------------+-----------
 i_name | character varying(10) | not null
 rank | integer    | not null

我模拟了20条数据来做演示。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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:

第一种:

完整的带有排名字段以及排序。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

第二种:

带有完整的排名字段但是没有排序。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

第三种:

没有任何排名字段,也没有任何排序字段。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

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

原文链接:http://www.linuxidc.com/Linux/2015-04/116349.htm

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
Intellij idea2020永久破解,亲测可用!!!
Intellij idea2020永久破解,亲测可用!!! 2020-07-29
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
返回顶部