rank() over 函数

• 为结果集分区里的每一行分配一个排名

复制
  RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )
  // partition by 对结果集进行分组
  // order by 对结果集中的行进行排序

• 下面用实例进行说明
• ranks 表结果如下

复制
  alex=# \d ranks;
                         Table "public.ranks"
    Column  |         Type          | Collation | Nullable | Default 
  ----------+-----------------------+-----------+----------+---------
   c        | character varying(10) |           |          |
   group_id | integer               |           |          |
 
   // 数据
  alex=# select * from ranks;
   c | group_id
  ---+----------
   A |        1
   A |        1
   B |        2
   B |        2
   B |        3
   C |        3
   E |        3
   C |        2
  (8 rows)

• 对其结果进行排名

复制
  alex=# select group_id,c, rank() over (order by c) rank_number from ranks;
   group_id | c | rank_number
  ----------+---+------------- 
          1 | A |           1 
          1 | A |           1 
          2 | B |           3 
          3 | B |           3 
          2 | B |           3 
          3 | C |           6 
          2 | C |           6 
          3 | E |           8
  (8 rows)
  // 可以看到第一行和第二行的值相同，所以排名也相同，从第三行开始等级为3，并且跳过了等级2

• 分组后进行排名

复制
  alex=# select group_id,c, rank() over (partition by group_id order by c) rank_number from ranks;
   group_id | c | rank_number
  ----------+---+------------- 
          1 | A |           1 
          1 | A |           1 
          2 | B |           1 
          2 | B |           1 
          2 | C |           3 
          3 | B |           1 
          3 | C |           2 
          3 | E |           3
  (8 rows)
  // 可以看到分组之后才进行的排名

dense_rank() 函数

• 进行不间断顺序排名

复制
  alex=# select group_id,c, dense_rank() over (partition by group_id order by c) rank_number from ranks;
   group_id | c | rank_number
  ----------+---+------------- 
          1 | A |           1 
          1 | A |           1 
          2 | B |           1 
          2 | B |           1 
          2 | C |           2 
          3 | B |           1 
          3 | C |           2 
          3 | E |           3
  (8 rows)
  // 可以看到第5条数据  (2 | C |           2)
     其中虽然是group_id=2中的第三条数据，但是其rank_number是2，因为第一条和第二条数据c的值都是B,所以排名为2，此时排名是连续的

row_number() 函数

• 进行连续排名

复制
  alex=# select group_id,c, row_number() over (partition by group_id order by c) rank_number from ranks;
   group_id | c | rank_number
  ----------+---+------------- 
          1 | A |           1 
          1 | A |           2 
          2 | B |           1 
          2 | B |           2 
          2 | C |           3 
          3 | B |           1 
          3 | C |           2 
          3 | E |           3
  (8 rows)
  // 可以看到 group 分组后 rank_number是连续的

案例，求一个班级中成绩排名前二的信息

• 表结构如下

复制
alex=# \d+ class_score
                                             Table "public.class_score"
    Column  |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
  ----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
   id       | integer               |           |          |         | plain    |             |              |
   class_id | integer               |           |          |         | plain    |             |              |
   name     | character varying(50) |           |          |         | extended |             |              |
   score    | double precision      |           |          |         | plain    |             |              |
  Access method: heap

• 查询sql及结果如下

复制
  alex=# select * from (select class_id,name,score,rank() over(partition by class_id order by score desc)  rank from class_score) t where t.rank <=2;
   class_id | name  | score | rank
  ----------+-------+-------+------ 
          1 | yang  |    88 |    1 
          1 | li    |    87 |    2 
          2 | song  |   100 |    1 
          2 | haung |    89 |    2
  (4 rows)