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 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!