排序和排名窗口函数

RANK()、DENSE_RANK() 和 ROW_NUMBER() 都是 SQL 中的窗口函数，用于为结果集中的行分配排名或序号。它们的主要区别在于处理并列排名时的行为。以下是它们的详细对比：

1. ROW_NUMBER()

ROW_NUMBER() 为每一行分配一个唯一的序号，序号从 1 开始，按指定的排序规则递增。即使有并列的行，ROW_NUMBER() 也会为每一行分配一个唯一的序号。

语法

ROW_NUMBER() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
)

• PARTITION BY：可选，用于将数据分组，窗口函数在每个分组内独立计算。
• ORDER BY：指定排序规则，必须包含至少一个列。

行为：

• 每一行都被分配一个唯一的序号。
• 即使有并列的行，序号也不会重复。

2. RANK()

RANK() 为每一行分配一个排名，如果有并列的行，则会分配相同的排名，但后续的排名会跳过一些数字。

语法

RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
)

• PARTITION BY：可选，用于将数据分组，窗口函数在每个分组内独立计算。
• ORDER BY：指定排序规则，必须包含至少一个列。

行为：

• 如果有并列的行，它们会被分配相同的排名。
• 后续的排名会跳过一些数字，以保持总排名的连续性。

3. DENSE_RANK()

DENSE_RANK() 为每一行分配一个排名，即使有并列的行，也不会跳过任何排名，始终保持连续。

语法

DENSE_RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
)

• PARTITION BY：可选，用于将数据分组，窗口函数在每个分组内独立计算。
• ORDER BY：指定排序规则，必须包含至少一个列。

行为：

• 如果有并列的行，它们会被分配相同的排名。
• 后续的排名不会跳过任何数字，始终保持连续。

示例：

假设有一个表 scores，数据如下：

使用 ROW_NUMBER()：

SELECT id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM scores;

结果：

解释：

• 每一行都被分配了一个唯一的序号。
• 即使有两个 90 的行，它们也被分配了不同的序号 1 和 2。

使用 RANK()：

SELECT id, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

结果：

解释：

• 两个 90 的行都被分配了排名 1。
• 排名 2 被跳过，因为有两个并列的 1。
• 排名 3 是下一个可用的排名。

使用 DENSE_RANK()：

SELECT id, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

结果：

解释：

• 两个 90 的行都被分配了排名 1。
• 排名 2 是下一个可用的排名，即使有两个并列的 1。
• 排名 3 是下一个可用的排名。

4. 总结

• ROW_NUMBER()：

  • 为每一行分配一个唯一的序号。
  • 即使有并列的行，序号也不会重复。
  • 适用于需要唯一标识每一行的场景。

• RANK()：

  • 为每一行分配一个排名。
  • 如果有并列的行，会分配相同的排名。
  • 后续的排名会跳过一些数字，以保持总排名的连续性。
  • 适用于需要反映并列情况的场景。

• DENSE_RANK()：

  • 为每一行分配一个排名。
  • 即使有并列的行，也不会跳过任何排名，始终保持连续。
  • 适用于需要保持排名连续的场景。

选择建议

• 如果你需要为每一行分配一个唯一的序号，使用 ROW_NUMBER()。
• 如果你需要反映并列情况，并且可以接受跳过排名，使用 RANK()。
• 如果你需要保持排名连续，即使有并列，使用 DENSE_RANK()。