MySQL ROW_NUMBER() 详解（MySQL 8.0）：排名、Top-N 查询与去重

1. 介绍

MySQL 8.0 引入了许多新特性，其中最显著的之一就是对窗口函数的支持。本文将重点介绍使用最广泛的函数之一：ROW_NUMBER()。

ROW_NUMBER() 函数为数据分析和报表提供了强大的能力，能够轻松地根据特定条件对数据进行排序和排名。本文将从基础用法、实用示例到旧版 MySQL 的替代方案，全面讲解该函数的使用方法。

目标读者

• 具备基本 SQL 知识的初学者到中级用户
• 使用 MySQL 进行数据处理和分析的工程师与数据分析师
• 正在考虑迁移到最新 MySQL 版本的任何人

ROW_NUMBER() 的优势

该函数可以根据特定条件为每一行分配唯一的编号。例如，你可以简洁地编写 “按销售额降序排名” 或 “提取并整理重复数据” 等查询。

在旧版本中，往往需要使用用户自定义变量编写复杂查询。使用 ROW_NUMBER()，SQL 语句变得更简洁、更易读。

本文将通过具体的查询示例，以适合初学者的方式进行讲解。接下来的章节，我们将深入探讨该函数的基本语法和行为细节。

2. 什么是 ROW_NUMBER() 函数？

ROW_NUMBER() 函数是 MySQL 8.0 新增的一种窗口函数，用于为行分配顺序编号。它可以按特定顺序和/或在每个分组内部进行编号，在数据分析和报表中极为有用。下面我们将结合实用示例，详细说明其基本语法。

ROW_NUMBER() 的基本语法

首先，ROW_NUMBER() 的基本格式如下。

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

各元素含义

• ROW_NUMBER() ：为每一行分配一个顺序编号。
• OVER ：用于定义窗口函数作用范围的关键字。
• PARTITION BY ：按指定列对数据进行分组，可选。若省略，则在所有行上统一编号。
• ORDER BY ：定义用于分配编号的排序规则，即排序依据。

基本示例

例如，假设有一个名为 “sales” 的表，数据如下。

要在每个部门内部按销售额降序分配顺序编号，可使用以下查询。

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

结果

从该结果可以看到，每个部门内部按销售额的排名已显示。

如何使用 PARTITION BY

在上面的示例中，数据是按 “department” 列分组的，这为每个部门分别生成了独立的序列。

如果省略 PARTITION BY，则对所有行作为一个整体进行编号。

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

结果

ROW_NUMBER() 的特性与注意事项

• 唯一编号：即使值相同，分配的编号也会唯一。
• NULL 处理：如果 ORDER BY 包含 NULL，升序时 NULL 排在最前，降序时排在最后。
• 性能影响：对于大数据集，ORDER BY 可能代价较高，需合理建立索引以提升性能。

3. 实际使用案例

以下是使用 MySQL ROW_NUMBER() 函数的实际场景。该函数在许多真实业务中非常有用，例如对数据进行排名和处理重复记录。

3-1. 每组内部排名

例如，考虑使用销售数据来“按部门对员工进行销售排名”的情况。使用以下数据集作为示例。

查询示例：按部门销售排名

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

结果：

通过这种方式，每个部门都会根据销售金额降序获得自己的序列，从而轻松生成排名。

3-2. 提取 Top N 行

接下来，让我们看一个“在每个部门中提取销售前 3 名员工”的情况。

查询示例：提取 Top N 行

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

结果：

此示例仅检索每个部门中销售前 3 行的数据。正如您所见，ROW_NUMBER() 不仅适用于排名，还适用于过滤顶级结果。

3-3. 查找和删除重复数据

数据库有时会包含重复记录。在这种情况下，您也可以使用 ROW_NUMBER() 轻松处理它们。

查询示例：检测重复项

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

此查询在同一员工名称存在多个记录时检测重复项。

查询示例：删除重复项

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

总结

ROW_NUMBER() 在各种场景中都很有用，例如：

1. 每个组内的排名
2. 提取 Top N 行
3. 检测和删除重复项

这使得复杂的数据处理和分析更加简单和高效。

4. 与其他窗口函数的比较

在 MySQL 8.0 中，除了 ROW_NUMBER() 之外，还有像 RANK() 和 DENSE_RANK() 这样的窗口函数，可用于排名和位置计算。虽然它们有相似的角色，但行为和结果不同。这里我们将比较每个函数并解释何时使用它们。

4-1. RANK() 函数

RANK() 函数分配排名，为相等的值赋予相同的排名，并跳过下一个排名号。

基本语法

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

示例

使用以下数据，计算销售排名。

查询示例：使用 RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

结果：

关键点：

• 销售金额相同的 A 和 B 都被视为排名“1”。
• 下一个排名“2”被跳过，因此 C 成为排名“3”。

4-2. DENSE_RANK() 函数

DENSE_RANK() 函数也为相等的值分配相同的排名，但它不会跳过下一个排名号。

基本语法

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

示例

使用上述相同的数据，尝试 DENSE_RANK() 函数。

查询示例：使用 DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

结果：

关键点：

• A 和 B 的销售金额相同（800），均被视为排名“1”。
• 与 RANK() 不同，下一个排名从“2”开始，因此保持了排名的连续性。

4-3. ROW_NUMBER() 的区别

ROW_NUMBER() 函数与其他两个函数的区别在于，它即使值相同，也会分配唯一的编号。

示例

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

结果：

要点：

• 即使值相同，每行也会获得唯一的编号，因此没有重复的排名。
• 这在需要严格的排序控制或每行唯一性时非常有用。

4-4. 快速用例总结

总结

ROW_NUMBER()、RANK() 和 DENSE_RANK() 应根据场景适当使用。

1. ROW_NUMBER() 最适合需要每行唯一编号的情况。
2. RANK() 适用于希望并列排名共享排名并强调排名间隙的情况。
3. DENSE_RANK() 适用于希望连续排名而无间隙的情况。

5. MySQL 8.0 以下版本的替代方案

在早于 MySQL 8.0 的版本中，不支持 ROW_NUMBER() 和其他窗口函数。但是，您可以使用用户定义变量来实现类似行为。本节解释了 MySQL 8.0 以下版本的实用替代方案。

5-1. 使用用户定义变量进行顺序编号

在 MySQL 5.7 及更早版本中，您可以使用用户定义变量为每行分配顺序编号。让我们看下面的示例。

示例：按部门销售排名

示例数据：

查询：

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

结果：

5-2. 提取前 N 行

要检索前 N 行，您可以类似地使用用户定义变量。

查询：

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

结果：

此查询按部门分配排名，然后仅提取前 3 名内的行。

5-3. 检测和删除重复项

您也可以使用用户定义变量处理重复数据。

查询示例：检测重复项

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

查询示例：删除重复项

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. 使用用户定义变量时的注意事项

1. 会话依赖

• 用户定义变量仅在当前会话中有效。它们无法在不同的查询或会话之间重用。

1. 处理顺序依赖

• 用户定义变量依赖于执行顺序，因此正确设置 ORDER BY 至关重要。

1. SQL 可读性和可维护性

• 查询可能会变得复杂，因此在 MySQL 8.0 及更高版本中，推荐使用窗口函数。

总结

在 MySQL 8.0 以下的版本中，您可以使用用户自定义变量来实现顺序编号和排名，以替代窗口函数。然而，由于查询往往会变得更复杂，最好在可能的情况下考虑迁移到更新的版本。

6. 注意事项与最佳实践

MySQL 的 ROW_NUMBER() 函数以及基于变量的替代方案非常便利，但在准确高效地运行它们时有一些重要注意点。本节阐述了性能优化的实际注意事项和最佳实践。

6-1. 性能考虑

1. ORDER BY 成本

ROW_NUMBER() 总是与 ORDER BY 一起使用。由于它需要排序，对大型数据集来说处理时间可能会显著增加。

缓解措施：

• 使用索引： 为 ORDER BY 中使用的列添加索引，以加快排序速度。
• 使用 LIMIT： 只检索实际需要的行数，以减少处理的数据量。

示例：

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. 增加的内存使用和磁盘 I/O

窗口函数是通过临时表和内存进行处理的。随着数据量的增长，内存消耗和磁盘 I/O 可能会增加。

缓解措施：

• 拆分查询： 将处理拆分为更小的查询，逐步提取数据以降低负载。
• 使用临时表： 将提取的数据存入临时表，然后从该表进行聚合，以分散工作负载。

6-2. 查询调优技巧

1. 检查执行计划

在 MySQL 中，您可以使用 EXPLAIN 来检查查询执行计划。这有助于您验证索引是否被正确使用。

示例：

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

示例输出：

如果看到 Using index，则表明索引被适当地使用。

2. 优化索引

务必为 ORDER BY 和 WHERE 中使用的列添加索引。特别关注以下内容。

• 单列索引： 适用于简单的排序条件
• 复合索引： 当条件涉及多个列时效果更佳

示例：

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. 使用批处理

与其一次性处理庞大的数据集，不如通过批量处理数据来降低负载。

示例：

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. 维护数据一致性

1. 更新与重新计算

当插入或删除行时，编号可能会变化。构建一个机制，根据需要重新计算编号。

示例：

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

使用视图可以帮助您基于最新数据保持排名的实时性。

6-4. 最佳实践查询示例

下面是一个兼顾性能和可维护性的最佳实践示例。

示例：提取前 N 行

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

此结构使用公共表表达式（CTE）来提升可读性和可复用性。

小结

使用 ROW_NUMBER() 或其替代方案时，请牢记以下要点：

1. 通过 索引优化 提高速度。
2. 检查 执行计划 以识别瓶颈。
3. 为 数据更新 做规划并保持一致性。
4. 使用 批处理 和 CTE 将负载分散。

应用这些最佳实践将使大规模数据分析和报告的处理更加高效。

7. 结论

在本文中，我们重点介绍了 MySQL 的 ROW_NUMBER() 函数，阐述了从基本用法和实际示例到旧版本替代方案的全部内容，并提供了注意事项和最佳实践。在本节中，我们将回顾要点并总结实用收获。

7-1. 为什么 ROW_NUMBER() 有用

ROW_NUMBER() 函数在以下方面对数据分析和报告特别方便：

1. 组内顺序编号： 轻松创建部门或类别的销售排名。
2. 提取前 N 行： 高效地根据特定条件过滤并提取数据。
3. 检测并删除重复项： 对数据清理和组织非常有帮助。

由于它简化了复杂查询，显著提升了 SQL 的可读性和可维护性。

7-2. 与其他窗口函数的比较

与 RANK()、DENSE_RANK() 等窗口函数相比，ROW_NUMBER() 的区别在于即使值相同也会分配唯一的序号。

选择合适的函数：
为你的需求挑选最佳函数，可实现高效的数据处理。

7-3. 处理旧版 MySQL

对于 MySQL 8.0 以下的环境，我们同样介绍了使用用户自定义变量的方法。但需注意以下事项：

• 由于 SQL 更复杂，可读性下降
• 某些情况下查询优化更困难
• 可能需要额外处理以保持数据一致性

如果可能，强烈建议迁移到 MySQL 8.0 或更高版本并使用窗口函数。

7-4. 性能优化要点

1. 使用索引： 为 ORDER BY 中使用的列添加索引，以提升速度。
2. 检查执行计划： 使用 EXPLAIN 预先验证性能。
3. 采用批处理： 将大数据集分成更小的块处理，以分散负载。
4. 使用视图和 CTE： 提高可复用性并简化复杂查询。

通过运用这些技术，你可以实现高效且稳定的数据处理。

7-5. 最后说明

ROW_NUMBER() 是一个强大的工具，能够显著提升数据分析的效率。
在本文中，我们从基本语法和实际示例到注意事项和替代方案，全部覆盖。

我们鼓励你在阅读本文的同时自行运行示例查询。提升 SQL 技能将帮助你自信地应对更复杂的数据分析和报告任务。

附录：参考资源

• 官方文档： MySQL Window Functions
• 在线 SQL 环境： SQL Fiddle（一个可以在线运行和测试 SQL 的工具）