postgresql rank() over, dense_rank(), row_number()用法區別

Posted on by

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

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：