MySQL函数：`ROW_NUMBER()`为结果集的每一行分配一个唯一的序号。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL窗口函数之ROW_NUMBER() 详解

大家好，今天我们来深入探讨MySQL中一个非常重要的窗口函数：ROW_NUMBER()。这个函数的功能很简单，就是为结果集中的每一行分配一个唯一的序号。虽然功能简单，但它在很多场景下都非常有用，例如分页、排名、查找重复数据等。

1. 什么是窗口函数？

在深入ROW_NUMBER()之前，我们先简单了解一下窗口函数。窗口函数是一种特殊的SQL函数，它可以在与当前行相关的行的集合（称为窗口）上执行计算。与聚合函数（如SUM(), AVG()）不同，窗口函数不会将多行数据聚合为一行，而是为结果集中的每一行都返回一个值。

窗口函数的语法通常如下：

function_name(arguments) OVER (partition_clause order_clause frame_clause)

function_name(arguments): 这是窗口函数本身，例如ROW_NUMBER()。
OVER(...): OVER 子句定义了窗口的范围和计算方式。
- partition_clause: PARTITION BY 子句将结果集划分为多个分区。窗口函数将在每个分区内独立计算。
- order_clause: ORDER BY 子句定义了每个分区内行的排序方式。这对于某些窗口函数（如ROW_NUMBER()）来说至关重要，因为它决定了序号的分配顺序。
- frame_clause: frame_clause (窗口框架) 定义了当前行的窗口范围。它允许你指定窗口中包含的行，例如“当前行及其之前的两行”。 ROW_NUMBER() 本身并不直接依赖 frame_clause，但其他窗口函数，例如 SUM() OVER (ORDER BY ... ROWS BETWEEN ...) 会使用它。

2. ROW_NUMBER() 函数的基本用法

ROW_NUMBER() 函数的基本语法如下：

ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3, column4, ... )

PARTITION BY: 可选。将结果集划分为多个分区。如果在 OVER() 子句中省略 PARTITION BY，则整个结果集被视为一个分区。
ORDER BY: 必须。定义每个分区内行的排序方式。ROW_NUMBER() 根据指定的排序规则为每一行分配一个唯一的序号。

下面我们通过一个例子来说明：

假设我们有一个名为 employees 的表，包含以下数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Sales', 50000.00),
(2, 'Bob', 'Sales', 60000.00),
(3, 'Charlie', 'Marketing', 55000.00),
(4, 'David', 'Marketing', 48000.00),
(5, 'Eve', 'IT', 70000.00),
(6, 'Frank', 'IT', 65000.00),
(7, 'Grace', 'Sales', 55000.00);

现在，我们想要为 employees 表中的每一行分配一个序号，按照 salary 从高到低排序。可以使用以下SQL语句：

SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
    employees;

查询结果如下：

id	name	department	salary	row_num
5	Eve	IT	70000.00	1
2	Bob	Sales	60000.00	2
3	Charlie	Marketing	55000.00	3
7	Grace	Sales	55000.00	4
6	Frank	IT	65000.00	5
1	Alice	Sales	50000.00	6
4	David	Marketing	48000.00	7

可以看到，ROW_NUMBER() 函数根据 salary 降序排列，为每一行分配了一个唯一的序号。

3. PARTITION BY 子句的应用

PARTITION BY 子句可以将结果集划分为多个分区，ROW_NUMBER() 函数将在每个分区内独立计算序号。

例如，我们想要为每个部门内的员工按照 salary 从高到低分配序号。可以使用以下SQL语句：

SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM
    employees;

查询结果如下：

id	name	department	salary	row_num
5	Eve	IT	70000.00	1
6	Frank	IT	65000.00	2
3	Charlie	Marketing	55000.00	1
4	David	Marketing	48000.00	2
2	Bob	Sales	60000.00	1
7	Grace	Sales	55000.00	2
1	Alice	Sales	50000.00	3

可以看到，ROW_NUMBER() 函数在每个部门内都从 1 开始重新分配序号。

4. ROW_NUMBER() 在分页中的应用

ROW_NUMBER() 函数可以用于实现分页功能。假设我们需要查询 employees 表中的第 3 到第 5 行数据，按照 salary 降序排列。可以使用以下SQL语句：

SELECT
    id,
    name,
    department,
    salary
FROM (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM
        employees
) AS subquery
WHERE
    row_num BETWEEN 3 AND 5;

查询结果如下：

id	name	department	salary
3	Charlie	Marketing	55000.00
7	Grace	Sales	55000.00
6	Frank	IT	65000.00

这个查询首先使用子查询为 employees 表中的每一行分配一个序号，然后在外层查询中筛选出序号在 3 到 5 之间的行。

5. ROW_NUMBER() 在查找重复数据中的应用

ROW_NUMBER() 函数可以用于查找重复数据。假设我们想要找出 employees 表中 department 和 salary 都相同的记录。可以使用以下SQL语句：

SELECT
    id,
    name,
    department,
    salary
FROM (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department, salary ORDER BY id) AS row_num
    FROM
        employees
) AS subquery
WHERE
    row_num > 1;

在这个例子中， PARTITION BY department, salary 将具有相同 department 和 salary 的记录划分到同一个分区中。 ORDER BY id 用于确保在每个分区内，序号是按照 id 排序的。外层查询筛选出 row_num 大于 1 的记录，这些记录就是重复的记录。由于我们的示例数据中没有完全重复的 department 和 salary 组合，所以这个查询不会返回任何结果。但是，如果我们插入以下数据：

INSERT INTO employees (id, name, department, salary) VALUES
(8, 'Grace2', 'Sales', 55000.00);

再次执行上面的查询，将会返回：

id	name	department	salary
8	Grace2	Sales	55000.00

6. ROW_NUMBER() 与其他窗口函数的比较

MySQL还提供了其他一些窗口函数，例如 RANK(), DENSE_RANK(), 和 NTILE()，它们都用于对结果集进行排名。 ROW_NUMBER() 与这些函数的区别在于：

ROW_NUMBER(): 为每一行分配一个唯一的序号，即使多行具有相同的排序值。
RANK(): 为具有相同排序值的行分配相同的排名，并跳过后续的排名。
DENSE_RANK(): 为具有相同排序值的行分配相同的排名，但不跳过后续的排名。
NTILE(N): 将结果集划分为 N 个大致相等的部分，并为每一行分配一个组号。

为了更好地理解这些函数的区别，我们来看一个例子。假设我们有一个名为 scores 的表，包含以下数据：

CREATE TABLE scores (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    score INT
);

INSERT INTO scores (id, name, score) VALUES
(1, 'Alice', 80),
(2, 'Bob', 90),
(3, 'Charlie', 85),
(4, 'David', 90),
(5, 'Eve', 75),
(6, 'Frank', 85);

现在，我们使用不同的窗口函数对 scores 表进行排名：

SELECT
    id,
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num,
    NTILE(3) OVER (ORDER BY score DESC) AS ntile_num
FROM
    scores;

查询结果如下：

id	name	score	row_num	rank_num	dense_rank_num	ntile_num
2	Bob	90	1	1	1	1
4	David	90	2	1	1	1
3	Charlie	85	3	3	2	1
6	Frank	85	4	3	2	2
1	Alice	80	5	5	3	2
5	Eve	75	6	6	4	3

可以看到：

ROW_NUMBER() 为每一行分配了一个唯一的序号，即使 Bob 和 David 的分数相同。
RANK() 为 Bob 和 David 分配了相同的排名 1，并跳过了排名 2。
DENSE_RANK() 为 Bob 和 David 分配了相同的排名 1，但没有跳过排名 2。
NTILE(3) 将结果集划分为 3 个部分，并为每一行分配了一个组号。

7. ROW_NUMBER() 的性能考虑

虽然 ROW_NUMBER() 功能强大，但在处理大型数据集时，需要考虑其性能。

索引: ORDER BY 子句中使用的列应该有索引，以提高排序的效率。如果有 PARTITION BY 子句，那么 PARTITION BY 子句中使用的列也应该有索引，以提高分区的效率。
避免不必要的排序: 尽量避免在不需要排序的情况下使用 ROW_NUMBER()。如果只需要为每一行分配一个唯一的序号，而不需要特定的排序，可以考虑使用自增长列或者 UUID。
窗口大小: ROW_NUMBER() 不受窗口框架 frame_clause 的影响，因为它总是为每一行分配一个序号，与窗口框架无关。但是，如果与其他窗口函数（例如 SUM() OVER (ORDER BY ... ROWS BETWEEN ...)）一起使用，窗口框架的大小会影响整体的性能。
复杂查询: 在复杂的查询中，ROW_NUMBER() 可能会增加查询的复杂度，从而影响性能。需要仔细评估查询的执行计划，并进行必要的优化。

8. 一些高级用法示例

查找每个分组的前N个记录: 假设我们要找到每个部门工资最高的前两名员工。

SELECT
    id,
    name,
    department,
    salary
FROM (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
    FROM
        employees
) AS subquery
WHERE
    row_num <= 2;

删除重复记录，只保留一个: 假设 employees 表存在完全重复的记录（所有列都相同），我们可以使用 ROW_NUMBER() 删除重复记录，只保留一个。

DELETE FROM employees
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY name, department, salary ORDER BY id) AS row_num
        FROM
            employees
    ) AS subquery
    WHERE row_num > 1
);

请注意: 在执行 DELETE 操作之前，务必备份数据。

计算累计百分比: 假设我们要计算每个员工的工资占总工资的累计百分比。
```
SELECT
    id,
    name,
    department,
    salary,
    SUM(salary) OVER (ORDER BY salary) AS cumulative_salary,
    (SUM(salary) OVER (ORDER BY salary)) / (SELECT SUM(salary) FROM employees) * 100 AS cumulative_percentage
FROM
    employees;
```
这个例子结合了ROW_NUMBER()的思想，展示了窗口函数在复杂的统计计算中的应用。虽然这里没有直接使用ROW_NUMBER()，但是SUM() OVER (ORDER BY salary) 实际上依赖于排序，与 ROW_NUMBER() 的 ORDER BY 子句有相似之处。

ROW_NUMBER() 的核心总结

ROW_NUMBER() 是一个强大的窗口函数，能够为结果集中的每一行分配唯一的序号。通过 PARTITION BY 和 ORDER BY 子句，可以灵活地控制序号的分配方式。掌握 ROW_NUMBER() 能够简化很多SQL查询，提高开发效率。在实际应用中，要根据具体的场景选择合适的窗口函数，并注意性能优化。

MySQL窗口函数之ROW_NUMBER() 详解

发表回复 取消回复

发表回复取消回复