MySQL的`窗口函数`：如何利用`ROW_NUMBER`、`RANK`、`DENSE_RANK`处理数据排名与去重？

MySQL 窗口函数：ROW_NUMBER、RANK、DENSE_RANK 的实战应用

各位朋友，大家好！今天我们来聊聊 MySQL 窗口函数中三个非常重要的成员：ROW_NUMBER、RANK 和 DENSE_RANK。这三个函数在处理数据排名和去重问题时，能发挥巨大的作用。我们将通过实际案例，深入理解它们的用法和区别，并探讨如何巧妙地结合它们解决复杂的数据分析需求。

1. 窗口函数基础回顾

在深入讨论这三个函数之前，我们先简单回顾一下窗口函数的基本概念。窗口函数允许我们对查询结果集中的每一行进行计算，但又不像聚合函数那样会改变结果集的行数。它基于一个“窗口”（一组与当前行相关的行）进行计算，并将结果添加到当前行的结果中。

窗口函数的基本语法如下：

函数名() OVER (
    [PARTITION BY 列名1, 列名2, ...]
    [ORDER BY 列名3 [ASC | DESC], ...]
    [ROWS | RANGE BETWEEN 窗口起始位置 AND 窗口结束位置]
)

• 函数名(): 你想使用的窗口函数，比如 ROW_NUMBER、RANK、DENSE_RANK 等。
• OVER(): 定义窗口的子句。
• PARTITION BY: 将结果集分成多个分区。窗口函数将在每个分区内独立计算。如果没有 PARTITION BY，则整个结果集被视为一个分区。
• ORDER BY: 定义每个分区内行的排序方式。排序会影响排名类窗口函数的结果。
• ROWS/RANGE BETWEEN: 定义窗口的大小，即窗口中包含哪些行。 ROWS 基于物理行数， RANGE 基于 ORDER BY 列的值。 通常情况下，对于排名函数，我们不需要显式指定窗口大小，因为默认窗口是当前分区的所有行。

2. ROW_NUMBER：生成唯一行号

ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的序号，从 1 开始，依次递增。 即使两行数据在 ORDER BY 子句中具有相同的值，它们也会被分配不同的序号。

案例 1：对学生成绩进行排名（无并列）

假设我们有一个 students 表，包含学生的姓名和分数：

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

INSERT INTO students (name, score) VALUES
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 92),
('Eve', 95);

现在，我们要对学生按照分数进行排名，使用 ROW_NUMBER() 可以轻松实现：

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

查询结果如下：

id	name	score	row_num
5	Eve	95	1
2	Bob	92	2
4	David	92	3
1	Alice	85	4
3	Charlie	78	5

可以看到，即使 Bob 和 David 的分数相同，他们也被分配了不同的排名（2 和 3）。 ROW_NUMBER() 总是生成唯一的序号。

案例 2：按班级对学生成绩进行排名

如果我们想按班级对学生进行排名，只需添加 PARTITION BY 子句：

ALTER TABLE students ADD COLUMN class VARCHAR(255);

UPDATE students SET class = 'Class A' WHERE id IN (1, 2, 3);
UPDATE students SET class = 'Class B' WHERE id IN (4, 5);

SELECT
    id,
    name,
    class,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS row_num
FROM
    students;

查询结果如下：

id	name	class	score	row_num
2	Bob	Class A	92	1
1	Alice	Class A	85	2
3	Charlie	Class A	78	3
5	Eve	Class B	95	1
4	David	Class B	92	2

可以看到，每个班级内部都生成了独立的排名。

3. RANK：生成带并列的排名

RANK() 函数也会为结果集中的每一行分配一个排名，但与 ROW_NUMBER() 不同的是，如果两行或多行在 ORDER BY 子句中具有相同的值，它们将被分配相同的排名。 排名会跳跃，例如： 1, 2, 2, 4, 5…

案例 3：对学生成绩进行排名（有并列）

继续使用上面的 students 表，我们使用 RANK() 函数进行排名：

SELECT
    id,
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank_num
FROM
    students;

查询结果如下：

id	name	score	rank_num
5	Eve	95	1
2	Bob	92	2
4	David	92	2
1	Alice	85	4
3	Charlie	78	5

可以看到，Bob 和 David 的分数相同，都被分配了排名 2。 下一个排名是 4，跳过了 3。

案例 4：按班级对学生成绩进行排名（有并列）

SELECT
    id,
    name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_num
FROM
    students;

查询结果如下：

id	name	class	score	rank_num
2	Bob	Class A	92	1
1	Alice	Class A	85	2
3	Charlie	Class A	78	3
5	Eve	Class B	95	1
4	David	Class B	92	2

每个班级内部的排名都考虑了并列的情况。

4. DENSE_RANK：生成连续的带并列的排名

DENSE_RANK() 函数与 RANK() 类似，也会为相同值的行分配相同的排名。 但是，DENSE_RANK() 生成的排名是连续的，不会跳跃。 例如： 1, 2, 2, 3, 4…

案例 5：对学生成绩进行排名（连续排名）

SELECT
    id,
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM
    students;

查询结果如下：

id	name	score	dense_rank_num
5	Eve	95	1
2	Bob	92	2
4	David	92	2
1	Alice	85	3
3	Charlie	78	4

可以看到，Bob 和 David 的分数相同，都被分配了排名 2。 下一个排名是 3，而不是像 RANK() 那样跳到 4。

案例 6：按班级对学生成绩进行排名（连续排名）

SELECT
    id,
    name,
    class,
    score,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_rank_num
FROM
    students;

查询结果如下：

id	name	class	score	dense_rank_num
2	Bob	Class A	92	1
1	Alice	Class A	85	2
3	Charlie	Class A	78	3
5	Eve	Class B	95	1
4	David	Class B	92	2

5. 结合窗口函数进行去重

窗口函数不仅可以用于排名，还可以用于去重。 一种常见的去重策略是使用 ROW_NUMBER() 为每个重复组分配一个唯一的序号，然后只保留序号为 1 的行。

案例 7：去除重复的客户记录

假设我们有一个 customers 表，包含客户的姓名、地址和电话号码。 由于数据录入错误，可能存在重复的客户记录：

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    address VARCHAR(255),
    phone VARCHAR(20)
);

INSERT INTO customers (name, address, phone) VALUES
('John Doe', '123 Main St', '555-1234'),
('Jane Smith', '456 Oak Ave', '555-5678'),
('John Doe', '123 Main St', '555-1234'),
('Peter Jones', '789 Pine Ln', '555-9012'),
('Jane Smith', '456 Oak Ave', '555-5678');

我们可以使用以下 SQL 语句去除重复的客户记录：

SELECT
    id,
    name,
    address,
    phone
FROM (
    SELECT
        id,
        name,
        address,
        phone,
        ROW_NUMBER() OVER (PARTITION BY name, address, phone ORDER BY id) AS row_num
    FROM
        customers
) AS subquery
WHERE
    row_num = 1;

查询结果如下：

id	name	address	phone
1	John Doe	123 Main St	555-1234
2	Jane Smith	456 Oak Ave	555-5678
4	Peter Jones	789 Pine Ln	555-9012

这个查询首先使用 ROW_NUMBER() 函数，按照姓名、地址和电话号码进行分组，并为每个分组内的记录分配一个唯一的序号。 ORDER BY id 确保保留每个重复组中 id 最小的记录。 然后，外层查询只选择 row_num 为 1 的记录，即每个重复组的第一条记录，从而实现了去重。

案例 8：删除重复的客户记录

上面的查询只是显示了去重后的结果，并没有实际删除重复的记录。 如果需要真正删除重复的记录，可以使用以下 SQL 语句：

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY name, address, phone ORDER BY id) AS row_num
        FROM
            customers
    ) AS subquery
    WHERE row_num > 1
);

这个 SQL 语句首先使用一个子查询，找到所有 row_num 大于 1 的记录，即重复的记录。 然后，外层 DELETE 语句删除这些记录。 需要注意的是，在 MySQL 5.7 及更早版本中，不能在同一个查询中同时进行 SELECT 和 DELETE 操作，因此需要使用一个中间表或者临时表来解决这个问题。 MySQL 8.0 已经解决了这个问题，允许在同一个查询中同时进行 SELECT 和 DELETE 操作。

6. 实际应用场景扩展

除了上面介绍的案例，ROW_NUMBER、RANK 和 DENSE_RANK 还可以应用于更广泛的场景：

• 分页查询： 使用 ROW_NUMBER() 可以轻松实现分页查询，只需要在外层查询中过滤出指定范围的 row_num。
• 查找Top N： 可以使用 RANK() 或 DENSE_RANK() 找到每个分组中的 Top N 条记录。 例如，找到每个部门薪水最高的 3 名员工。
• 计算百分比排名： 可以结合其他窗口函数（如 COUNT()）计算每个值在整个数据集中的百分比排名。
• 数据清洗： 可以使用 ROW_NUMBER() 识别并处理数据中的异常值或错误记录。
• 报表生成： 在生成各种报表时，可以使用排名函数对数据进行排序和分组，使报表更易于理解和分析。

7. 注意事项

• 窗口函数必须在 SELECT 语句中使用。
• OVER() 子句是窗口函数的核心，它定义了窗口的范围和排序方式。
• PARTITION BY 子句用于将结果集分成多个分区，窗口函数将在每个分区内独立计算。
• ORDER BY 子句用于定义每个分区内行的排序方式，排序会影响排名类窗口函数的结果。
• 在复杂的查询中，可以使用多个窗口函数，每个窗口函数可以有不同的 OVER() 子句。
• 性能方面，窗口函数通常比使用子查询或临时表更高效，但对于大数据集，仍然需要进行优化。

8. 总结与思考

今天，我们深入探讨了 MySQL 窗口函数中 ROW_NUMBER、RANK 和 DENSE_RANK 的用法和区别。 ROW_NUMBER 生成唯一序号， RANK 生成带跳跃的排名， DENSE_RANK 生成连续排名。 掌握它们，能更高效地解决数据排名和去重等问题。 灵活运用这些工具，提升数据处理能力，在实际工作中创造更大的价值。