MySQL新版本特性之：`MySQL 8.0`的“`Window Frame`：`ROWS`、`RANGE`和`GROUPS`的深入用法。

MySQL 8.0 Window Frame：ROWS、RANGE 和 GROUPS 的深入用法

大家好，今天我们来深入探讨 MySQL 8.0 中 Window Frame 的 ROWS、RANGE 和 GROUPS 这三个关键概念。Window Frame 是 Window Function (窗口函数) 的重要组成部分，它定义了窗口函数操作的数据行的集合。理解并熟练运用这三个概念，对于编写高效、强大的 SQL 查询至关重要。

1. Window Function 和 Window Frame 的基本概念

在深入 ROWS、RANGE 和 GROUPS 之前，我们先快速回顾一下 Window Function 和 Window Frame 的基本概念。

Window Function (窗口函数) 允许我们对一组与当前行相关的行进行计算，而无需使用 GROUP BY 子句。窗口函数可以执行聚合、排序、排名等操作。常见的窗口函数包括 ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、MAX()、MIN() 等。

Window Frame (窗口框架) 定义了窗口函数应用的数据行的集合。它指定了相对于当前行的窗口的起始和结束位置。如果没有明确指定 Window Frame，则默认的 Frame 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

Window Frame 的语法如下：

{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end

frame_start 和 frame_end 可以是以下值：

• UNBOUNDED PRECEDING: 窗口从分区的第一行开始。
• UNBOUNDED FOLLOWING: 窗口延伸到分区的最后一行。
• CURRENT ROW: 窗口包含当前行。
• expr PRECEDING: 窗口从当前行之前的 expr 行开始。
• expr FOLLOWING: 窗口从当前行之后的 expr 行开始。

现在，让我们深入了解 ROWS、RANGE 和 GROUPS 的具体用法。

2. ROWS 模式：基于物理行数

ROWS 模式基于物理行数定义窗口框架。它指定了相对于当前行的确切行数。

语法：

ROWS BETWEEN frame_start AND frame_end

示例：

假设我们有一个 sales 表，包含以下数据：

order_date	product	quantity	price
2023-01-01	A	2	10
2023-01-01	B	1	20
2023-01-02	A	3	10
2023-01-02	C	2	30
2023-01-03	B	2	20
2023-01-03	A	1	10
2023-01-04	C	3	30
2023-01-04	B	1	20

我们想要计算每个订单日期对应的产品销售数量的移动平均值，窗口大小为前一行、当前行和后一行。

SELECT
    order_date,
    product,
    quantity,
    AVG(quantity) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average
FROM
    sales;

结果：

order_date	product	quantity	moving_average
2023-01-01	A	2	2
2023-01-01	B	1	2
2023-01-02	A	3	2
2023-01-02	C	2	2
2023-01-03	B	2	1.6667
2023-01-03	A	1	2
2023-01-04	C	3	1.3333
2023-01-04	B	1	2

在这个例子中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定了窗口包含当前行之前的 1 行和当前行之后的 1 行。 因此，对于 2023-01-01 的第一行数据，它的窗口包含 2023-01-01 的 A 和 B 这两行，平均值为 (2+1)/2=1.5。

另一个例子： 计算累计销售额。

SELECT
    order_date,
    product,
    quantity,
    SUM(quantity) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM
    sales;

结果：

order_date	product	quantity	cumulative_sales
2023-01-01	A	2	2
2023-01-01	B	1	3
2023-01-02	A	3	6
2023-01-02	C	2	8
2023-01-03	B	2	10
2023-01-03	A	1	11
2023-01-04	C	3	14
2023-01-04	B	1	15

在这个例子中，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定了窗口包含从分区的第一行到当前行的所有行。因此，对于 2023-01-02 的 C 这行数据，它的窗口包含 2023-01-01 的 A 和 B、2023-01-02 的 A 和 C 这四行，总和为 2+1+3+2 = 8。

3. RANGE 模式：基于排序键的值

RANGE 模式基于排序键的值定义窗口框架。它指定了与当前行具有相同排序键值的行。

语法：

RANGE BETWEEN frame_start AND frame_end

重要提示：

• 使用 RANGE 模式时，ORDER BY 子句必须包含单个表达式。
• RANGE 模式通常与数值或日期类型的排序键一起使用。

示例：

假设我们有一个 employee 表，包含以下数据：

employee_id	salary	department
1	50000	IT
2	50000	HR
3	60000	IT
4	70000	HR
5	70000	IT
6	80000	HR

我们想要计算每个员工的工资在其所在部门内的排名，但是希望工资相同的员工排名相同。

SELECT
    employee_id,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS salary_rank
FROM
    employee;

结果：

employee_id	salary	department	salary_rank
1	50000	IT	1
3	60000	IT	2
5	70000	IT	3
2	50000	HR	1
4	70000	HR	2
6	80000	HR	3

在这个例子中，RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定了窗口包含所有工资小于等于当前员工工资的员工。对于 IT 部门中工资为 70000 的员工，它的窗口包含所有 IT 部门中工资小于等于 70000 的员工，因此排名为 3。

RANGE 的数值范围：

RANGE 可以基于数值范围进行窗口定义。例如，我们想计算薪资在当前员工薪资 ± 10000 范围内的平均薪资。

SELECT
    employee_id,
    salary,
    department,
    AVG(salary) OVER (ORDER BY salary RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS average_salary_range
FROM
    employee;

结果：

employee_id	salary	department	average_salary_range
1	50000	IT	50000
2	50000	HR	50000
3	60000	IT	60000
4	70000	HR	70000
5	70000	IT	70000
6	80000	HR	80000

注意：由于示例数据中薪资的间隔都大于 10000，因此每个员工的平均薪资范围等于他们自身的薪资。

RANGE 的日期范围：

RANGE 也可以基于日期范围进行窗口定义。例如，假设 sales 表中还有一个 shipping_date 字段，我们想计算每个订单日期前后3天内的订单总额。

SELECT
    order_date,
    product,
    quantity,
    price,
    SUM(quantity * price) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '3' DAY FOLLOWING) AS total_sales_range
FROM
    sales;

在这个例子中，RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '3' DAY FOLLOWING 指定了窗口包含所有订单日期在当前订单日期前后3天内的订单。

4. GROUPS 模式：基于排序后的组

GROUPS 模式基于排序后的组定义窗口框架。它指定了相对于当前行所在组的组数。

语法：

GROUPS BETWEEN frame_start AND frame_end

重要提示：

• GROUPS 模式需要 ORDER BY 子句，并且 ORDER BY 子句中可以包含多个表达式。
• GROUPS 模式将具有相同 ORDER BY 值的行视为一个组。

示例：

假设我们有一个 student_scores 表，包含以下数据：

student_id	score	subject
1	80	Math
2	80	Math
3	70	Math
4	90	Math
5	90	Math
6	80	English
7	70	English
8	90	English
9	90	English

我们想要计算每个学生的分数在其所在科目内的排名，并且希望将分数相同的学生视为同一组。

SELECT
    student_id,
    score,
    subject,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS score_rank
FROM
    student_scores;

结果：

student_id	score	subject	score_rank
4	90	Math	1
5	90	Math	1
1	80	Math	3
2	80	Math	3
3	70	Math	5
8	90	English	1
9	90	English	1
6	80	English	3
7	70	English	4

在这个例子中，GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定了窗口包含所有分数大于等于当前学生分数的组。对于 Math 科目中分数为 80 的学生，他们的窗口包含所有 Math 科目中分数大于等于 80 的组，因此排名为 3。

GROUPS 的数值范围：

GROUPS 还可以基于组数进行窗口定义。例如，我们想计算当前组和前一个组的平均分。

SELECT
    student_id,
    score,
    subject,
    AVG(score) OVER (PARTITION BY subject ORDER BY score GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS average_score_groups
FROM
    student_scores;

结果：

student_id	score	subject	average_score_groups
3	70	Math	70
1	80	Math	75
2	80	Math	75
4	90	Math	85
5	90	Math	90
7	70	English	70
6	80	English	75
8	90	English	85
9	90	English	90

在这个例子中，对于 Math 科目中分数为 90 的学生（student_id 4 和 5），由于他们是同一组，GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW 会包含 80 分的组和 90 分的组，平均分则为 (80 + 90 + 90) / 3 = 85。 对于 English 科目，70 分的组前面没有组，则只计算自身的平均值，得到 70。

5. 总结：选择合适的模式

ROWS、RANGE 和 GROUPS 这三个 Window Frame 模式各有用途。

• ROWS 模式基于物理行数，适用于需要精确控制窗口大小的场景。
• RANGE 模式基于排序键的值，适用于需要处理具有相同排序键值的行的场景。
• GROUPS 模式基于排序后的组，适用于需要将具有相同排序键值的行视为同一组的场景。

选择哪种模式取决于具体的业务需求和数据特点。理解它们的区别和用法，可以帮助我们编写更高效、更灵活的 SQL 查询。

6. 一些使用建议

以下是一些在使用 ROWS, RANGE, 和 GROUPS 时的建议:

• 明确需求: 在编写包含窗口函数的 SQL 语句之前，务必清楚地了解需要计算什么，以及窗口应该如何定义。
• 考虑数据类型: RANGE 模式对排序键的数据类型有要求，通常是数值或日期类型。
• 性能考量: 复杂的窗口函数可能会影响查询性能。在处理大量数据时，需要仔细评估性能，并考虑是否可以优化查询。
• 测试验证: 编写完成后，务必进行充分的测试，确保窗口函数的结果符合预期。

7. 总结和重点强调

掌握ROWS、RANGE和GROUPS 的区别和用法，可以更好地利用窗口函数进行复杂的数据分析和计算。在实际应用中，需要根据具体的需求选择合适的窗口框架模式，并进行充分的测试验证，以确保查询的正确性和性能。

8. 思考和进一步学习

希望今天的讲解能够帮助大家更好地理解 MySQL 8.0 中 Window Frame 的 ROWS、RANGE 和 GROUPS 的用法。 建议大家在实际项目中多加练习，并深入研究 MySQL 官方文档，以便更好地掌握这些强大的功能。