Sql - 排名函数

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)

本作品采用《CC 协议》，转载必须注明作者和本文链接