MySQL Window Function：Frame 对 Rank 函数的影响
大家好，今天我们来深入探讨 MySQL Window Function 中 Frame 子句对 Rank 函数的影响。Window Function 是 MySQL 8.0 引入的一项强大的功能，它允许我们在查询结果集的“窗口”上执行计算，而无需使用复杂的子查询或自连接。其中，Rank 函数用于计算窗口内每行的排名。而 Frame 子句则定义了窗口的大小和范围，直接影响 Rank 函数的计算结果。
1. Window Function 基础回顾
在深入 Frame 子句之前，我们先简要回顾一下 Window Function 的基本语法和使用方法。
一个典型的 Window Function 语法如下：
function_name(arguments) OVER (
[PARTITION BY column_list]
[ORDER BY column_list [ASC | DESC]]
[frame_clause]
)- function_name(arguments): 这是要执行的 Window Function，例如
RANK(),ROW_NUMBER(),SUM(),AVG()等。 - OVER(…):
OVER子句标志着这是一个 Window Function。 - PARTITION BY column_list: 将结果集分成多个分区。Window Function 会在每个分区内独立计算。如果没有
PARTITION BY，则整个结果集被视为一个分区。 - ORDER BY column_list [ASC | DESC]: 定义每个分区内行的排序方式。
ORDER BY子句对于 Rank 函数至关重要，因为它决定了排名的依据。 - frame_clause: 定义窗口的范围，也就是当前行计算时所考虑的行的集合。这是我们今天要重点讨论的内容。
2. Rank 函数简介
RANK() 函数为每个分区内的每一行分配一个排名。排名是根据 ORDER BY 子句中指定的列的值来确定的。如果两行或多行具有相同的值，则它们将被分配相同的排名，并且下一个排名将被跳过。
例如，如果三行具有相同的排名 2，则下一行的排名将是 5，而不是 3。
3. Frame 子句的重要性
Frame 子句定义了窗口的范围，它决定了当前行计算 Rank 时，哪些行会被纳入计算范围。如果没有指定 Frame 子句，则默认的 Frame 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着窗口从分区的第一行开始，一直延伸到当前行，并且所有与当前行具有相同 ORDER BY 值的行都包含在窗口中。
4. Frame 子句的语法
Frame 子句的完整语法如下：
{ROWS | RANGE} BETWEEN
{UNBOUNDED PRECEDING | value PRECEDING | CURRENT ROW | value FOLLOWING | UNBOUNDED FOLLOWING}
AND
{UNBOUNDED PRECEDING | value PRECEDING | CURRENT ROW | value FOLLOWING | UNBOUNDED FOLLOWING}- ROWS | RANGE: 指定 Frame 的单位。
ROWS: 基于行数定义 Frame。RANGE: 基于ORDER BY列的值定义 Frame。
- BETWEEN … AND …: 定义 Frame 的起始点和结束点。
- UNBOUNDED PRECEDING: 表示分区的第一行。
- value PRECEDING: 表示当前行之前的
value行（ROWS）或value值的行（RANGE）。 - CURRENT ROW: 表示当前行。
- value FOLLOWING: 表示当前行之后的
value行（ROWS）或value值的行（RANGE）。 - UNBOUNDED FOLLOWING: 表示分区的最后一行。
5. Frame 子句对 Rank 函数的影响案例分析
为了更好地理解 Frame 子句对 Rank 函数的影响，我们通过一些具体的案例进行分析。
5.1. 默认 Frame：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
假设我们有一个 employees 表，包含员工姓名和薪水：
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(255),
salary DECIMAL(10, 2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 50000.00),
(2, 'Bob', 60000.00),
(3, 'Charlie', 60000.00),
(4, 'David', 70000.00),
(5, 'Eve', 70000.00),
(6, 'Frank', 80000.00);我们使用 RANK() 函数计算每个员工的薪水排名，不指定 Frame 子句：
SELECT
id,
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;结果如下：
| id | name | salary | salary_rank |
|---|---|---|---|
| 6 | Frank | 80000.00 | 1 |
| 4 | David | 70000.00 | 2 |
| 5 | Eve | 70000.00 | 2 |
| 2 | Bob | 60000.00 | 4 |
| 3 | Charlie | 60000.00 | 4 |
| 1 | Alice | 50000.00 | 6 |
在这个例子中，由于没有指定 Frame 子句，使用的是默认的 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。对于薪水为 70000 的 David 和 Eve，他们的排名都是 2，因为他们具有相同的薪水值。
5.2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
现在，我们使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 作为 Frame 子句：
SELECT
id,
name,
salary,
RANK() OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_rank
FROM
employees;结果如下：
| id | name | salary | salary_rank |
|---|---|---|---|
| 6 | Frank | 80000.00 | 1 |
| 4 | David | 70000.00 | 2 |
| 5 | Eve | 70000.00 | 2 |
| 2 | Bob | 60000.00 | 4 |
| 3 | Charlie | 60000.00 | 4 |
| 1 | Alice | 50000.00 | 6 |
在这个例子中，结果与不指定 Frame 子句的结果相同。这是因为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 和 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 在这种情况下行为一致。当 ORDER BY 的列有重复值时，RANGE 会包含所有与当前行具有相同值的行，而 ROWS 也会包含这些行（因为它们是连续的）。
5.3. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
接下来，我们使用 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 作为 Frame 子句：
SELECT
id,
name,
salary,
RANK() OVER (ORDER BY salary DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS salary_rank
FROM
employees;结果如下：
| id | name | salary | salary_rank |
|---|---|---|---|
| 6 | Frank | 80000.00 | 1 |
| 4 | David | 70000.00 | 1 |
| 5 | Eve | 70000.00 | 1 |
| 2 | Bob | 60000.00 | 1 |
| 3 | Charlie | 60000.00 | 1 |
| 1 | Alice | 50000.00 | 1 |
这个结果可能令人惊讶。对于每一行，RANK() 函数计算的是当前行及其前一行和后一行中所有行中最高的排名。由于每一行都至少与另一行具有相同的薪水（除了薪水最高的 Frank），因此所有行的排名都变成了 1。
5.4. RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING
让我们使用 RANGE 来定义 frame， 并设定一个数值范围。
SELECT
id,
name,
salary,
RANK() OVER (ORDER BY salary DESC RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS salary_rank
FROM
employees;结果如下：
| id | name | salary | salary_rank |
|---|---|---|---|
| 6 | Frank | 80000.00 | 1 |
| 4 | David | 70000.00 | 1 |
| 5 | Eve | 70000.00 | 1 |
| 2 | Bob | 60000.00 | 1 |
| 3 | Charlie | 60000.00 | 1 |
| 1 | Alice | 50000.00 | 1 |
这个结果类似于上一个例子。因为 RANGE 是基于 ORDER BY 列的值来定义 Frame，所以 对于薪水为 70000 的 David 来说， Frame 包含了薪水在 60000 到 80000 之间的所有行，而薪水为 60000 的 Bob 来说，Frame 包含了薪水在50000到70000之间的所有行，所以所有排名都变成了1。
5.5. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
SELECT
id,
name,
salary,
RANK() OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS salary_rank
FROM
employees;结果如下：
| id | name | salary | salary_rank |
|---|---|---|---|
| 6 | Frank | 80000.00 | 1 |
| 4 | David | 70000.00 | 1 |
| 5 | Eve | 70000.00 | 1 |
| 2 | Bob | 60000.00 | 1 |
| 3 | Charlie | 60000.00 | 1 |
| 1 | Alice | 50000.00 | 1 |
在这个例子中，Frame 包含了分区中的所有行。因此，对于每一行，RANK() 函数计算的是整个分区中最高的排名。由于最高薪水是 80000，所以所有行的排名都是 1。
5.6. 使用 PARTITION BY 和 Frame
现在，我们添加一个 department 列到 employees 表：
ALTER TABLE employees ADD COLUMN department VARCHAR(255);
UPDATE employees SET department = 'Sales' WHERE id IN (1, 2, 3);
UPDATE employees SET department = 'Marketing' WHERE id IN (4, 5, 6);我们使用 PARTITION BY 和 Frame 子句来计算每个部门内的薪水排名：
SELECT
id,
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_rank
FROM
employees;结果如下：
| id | name | department | salary | salary_rank |
|---|---|---|---|---|
| 2 | Bob | Sales | 60000.00 | 1 |
| 3 | Charlie | Sales | 60000.00 | 1 |
| 1 | Alice | Sales | 50000.00 | 3 |
| 6 | Frank | Marketing | 80000.00 | 1 |
| 4 | David | Marketing | 70000.00 | 2 |
| 5 | Eve | Marketing | 70000.00 | 2 |
在这个例子中，PARTITION BY department 将 employees 表分成两个分区：Sales 和 Marketing。RANK() 函数在每个分区内独立计算排名。Frame 子句 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口从分区的第一行开始，一直延伸到当前行。
6. 总结：Frame 子句如何影响 Rank 函数
通过以上案例，我们可以总结出 Frame 子句对 Rank 函数的影响如下：
- 决定了参与排名的行的范围: Frame 子句决定了在计算 Rank 时，哪些行会被纳入考虑范围。不同的 Frame 定义会产生不同的排名结果。
- 影响了相同值的排名: 当
ORDER BY列有重复值时，Frame 子句会影响这些相同值的排名如何处理。RANGE和ROWS的行为在处理相同值时可能有所不同。 - 与 PARTITION BY 结合使用: Frame 子句可以与
PARTITION BY子句结合使用，在每个分区内定义不同的窗口范围，从而实现更精细的排名计算。
7. 选择合适的 Frame 子句
选择合适的 Frame 子句取决于具体的业务需求。
- 默认 Frame: 如果你需要计算从分区开始到当前行的排名，并且希望相同值的行具有相同的排名，可以使用默认的
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。 - 基于行数的 Frame: 如果你需要基于行数来定义窗口范围，可以使用
ROWS BETWEEN ... AND ...。 - 基于值的 Frame: 如果你需要基于
ORDER BY列的值来定义窗口范围，可以使用RANGE BETWEEN ... AND ...。 - 全分区 Frame: 如果你需要计算整个分区的排名，可以使用
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
理解 Frame 子句的语法和行为，并根据实际需求选择合适的 Frame 定义，是使用 MySQL Window Function 进行数据分析的关键。
8. 避免常见错误
在使用 Frame 子句时，需要注意以下几点：
- 理解
ROWS和RANGE的区别: 确保你理解ROWS和RANGE的区别，并根据实际需求选择合适的单位。 - 注意 Frame 的边界: 注意 Frame 的起始点和结束点，确保它们符合你的预期。
- 测试不同的 Frame 定义: 在实际应用中，建议测试不同的 Frame 定义，以确保你得到正确的结果。
9. 结束语：灵活运用 Frame，精准分析数据
Frame 子句是 MySQL Window Function 中一个强大而灵活的工具。通过合理使用 Frame 子句，我们可以实现各种复杂的排名和聚合计算，从而更好地理解和分析数据。 掌握Frame的定义和应用能够让我们在数据分析时更加游刃有余。