row_number() ,postgresql rank() over, dense_rank()的区别

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