【SQL】分数排名

目录

语法

需求

示例

分析

代码

语法

 dense_rank() over (order by score desc) as 'rank'

 SQL 中的一个窗口函数（Window Function），用于对查询结果集中的行进行排名。这个函数特别适用于需要对某个字段（如分数、销售额等）进行排序并赋予排名的场dense_rank()：

• dense_rank()
  • dense_rank() 是一个窗口函数，用于为结果集中的每一行分配一个唯一的排名。
  • 与 rank() 函数不同，dense_rank() 在处理相同值时不会跳过排名。也就是说，如果有两行具有相同的值，它们将被赋予相同的排名，而下一行的排名将紧接着这两个相同的排名之后，不会跳过任何数字。
• over (order by score desc)：
  • over 子句定义了窗口函数的操作范围，以及如何在结果集中对行进行排序。
  • order by score desc 指定了排序的依据是 score 字段，并且是按照降序（从高到低）排序。这意味着分数最高的行将被赋予最高的排名。
• as 'rank'：
  • as 'rank' 是一个别名子句，用于将 dense_rank() 函数的结果列命名为 rank。
  • 在 SQL 中，单引号通常用于字符串字面量。如 MySQL可能允许使用单引号作为别名，但这并不是标准 SQL 的做法。

<窗口函数> OVER ([PARTITION BY <分区列>] [ORDER BY <排序列>] [ROWS <行范围>])

窗口函数可以对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，而不会改变原始查询结果的行数或顺序。与常规的聚合函数（如SUM、AVG、COUNT等）不同，窗口函数允许在结果集中的每一行上进行计算，而不需要将数据分组。窗口函数提供了对每一行的子集（称为窗口）执行计算的能力，同时保留原始数据行的上下文信息。

• <窗口函数>：代表要执行的窗口函数，如SUM、AVG、ROW_NUMBER等。
• PARTITION BY 子句：用于将数据分成不同的分区，窗口函数将在每个分区内执行。类似于查询语句中的GROUP BY子句，但窗口函数不会将分区内的数据行合并成一行输出。
• ORDER BY 子句：定义数据的排序方式，决定窗口函数的计算顺序。排序选项通常用于数据的分类排名。
• ROWS 子句：指定窗口的范围，可以是行数、区间等。用于实现复杂的分析功能，如计算累计和、移动平均等。

需求

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
id 是该表的主键（有不同值的列）。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。

编写一个解决方案来查询分数的排名。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。
 

示例

输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

分析

分数应按从高到低排列。

考虑 order by，order by desc可以使得分数从高到底排列

如果两个分数相等，那么两个分数的排名应该相同。

在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

需要输出排名，考虑 rank()函数。

要求相同分数的排名应该相同，进一步需要dense_rank()函数

与 rank() 函数不同，dense_rank() 在处理相同值时不会跳过排名。也就是说，如果有两行具有相同的值，它们将被赋予相同的排名，而下一行的排名将紧接着这两个相同的排名之后，不会跳过任何数字。

引入rank列按照降序展现排名

order by score desc 指定了排序的依据是 score 字段，并且是按照降序（从高到低）排序。这意味着分数最高的行将被赋予最高的排名。dense_rank() over (order by score desc) as 'rank'

代码

select 
    score,
    dense_rank() over (order by score desc) as 'rank'
from Scores