MySQL 高级函数之 SUM()：窗口函数在滚动求和中的应用
大家好，今天我们来深入探讨 MySQL 中 SUM() 函数作为窗口函数在滚动求和中的应用。SUM() 函数大家都很熟悉，用于计算总和。但当它作为窗口函数使用时，功能会得到极大的扩展，尤其是在处理时间序列数据、财务数据等需要滚动计算的场景中。
1. 窗口函数基础回顾
在深入滚动求和之前，我们先简单回顾一下窗口函数的基础概念。窗口函数（Window Function）允许我们对查询结果的“窗口”内的数据进行计算，而不需要使用 GROUP BY 子句。这与聚合函数类似，但窗口函数不会将多行数据聚合成一行，而是为每一行都返回一个计算结果，这个结果是基于当前行所在的“窗口”计算得到的。
窗口函数的基本语法如下：
function_name() OVER (
[PARTITION BY column_list]
[ORDER BY column_list [ASC | DESC]]
[frame_clause]
)function_name(): 要使用的窗口函数，例如SUM(),AVG(),RANK(),ROW_NUMBER()等。OVER(): 指示这是一个窗口函数。PARTITION BY column_list: 将结果集划分为多个分区，窗口函数将分别在每个分区内计算。类似于GROUP BY，但不会将结果集聚合。ORDER BY column_list [ASC | DESC]: 定义每个分区内数据的排序方式，这对于滚动计算至关重要。frame_clause: 定义窗口的范围，也就是窗口函数计算时所考虑的行。这是滚动计算的核心。
2. SUM() 作为窗口函数
SUM() 函数作为窗口函数，可以计算指定窗口内某一列的总和。结合 ORDER BY 和 frame_clause，可以实现滚动求和的功能。
3. 滚动求和的实现方式
滚动求和，顾名思义，就是对数据集中的数据进行连续的、滚动的加总。例如，计算过去 7 天的销售额总和，或者计算过去 3 个月的平均收益。
实现滚动求和的关键在于 frame_clause，它定义了窗口的范围。常见的 frame_clause 形式有：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区的第一行到当前行。ROWS BETWEEN n PRECEDING AND CURRENT ROW: 从当前行之前的 n 行到当前行。ROWS BETWEEN n PRECEDING AND n FOLLOWING: 从当前行之前的 n 行到当前行之后的 n 行。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 从当前行到分区的最后一行。ROWS BETWEEN CURRENT ROW AND n FOLLOWING: 从当前行到当前行之后的 n 行。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 整个分区。
对于滚动求和，最常用的 frame_clause 是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 和 ROWS BETWEEN n PRECEDING AND CURRENT ROW。
4. 示例：每日销售额滚动求和
假设我们有一个 sales 表，包含每日的销售额数据：
CREATE TABLE sales (
sale_date DATE,
sale_amount DECIMAL(10, 2)
);
INSERT INTO sales (sale_date, sale_amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 200.00),
('2023-01-04', 120.00),
('2023-01-05', 180.00),
('2023-01-06', 250.00),
('2023-01-07', 130.00),
('2023-01-08', 160.00),
('2023-01-09', 220.00),
('2023-01-10', 190.00);现在，我们要计算每日的累计销售额（从第一天开始到当前天的销售额总和）。可以使用以下 SQL 查询：
SELECT
sale_date,
sale_amount,
SUM(sale_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sale_amount
FROM
sales;这个查询的结果如下：
| sale_date | sale_amount | cumulative_sale_amount |
|---|---|---|
| 2023-01-01 | 100.00 | 100.00 |
| 2023-01-02 | 150.00 | 250.00 |
| 2023-01-03 | 200.00 | 450.00 |
| 2023-01-04 | 120.00 | 570.00 |
| 2023-01-05 | 180.00 | 750.00 |
| 2023-01-06 | 250.00 | 1000.00 |
| 2023-01-07 | 130.00 | 1130.00 |
| 2023-01-08 | 160.00 | 1290.00 |
| 2023-01-09 | 220.00 | 1510.00 |
| 2023-01-10 | 190.00 | 1700.00 |
SUM(sale_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 这部分就是窗口函数，它按照 sale_date 升序排列，窗口范围是从第一行到当前行，因此计算的是累计销售额。
5. 示例：过去 3 天的销售额滚动求和
如果我们想计算过去 3 天的销售额总和（包括当天），可以使用以下 SQL 查询：
SELECT
sale_date,
sale_amount,
SUM(sale_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_day_sale_amount
FROM
sales;这个查询的结果如下：
| sale_date | sale_amount | rolling_3_day_sale_amount |
|---|---|---|
| 2023-01-01 | 100.00 | 100.00 |
| 2023-01-02 | 150.00 | 250.00 |
| 2023-01-03 | 200.00 | 450.00 |
| 2023-01-04 | 120.00 | 470.00 |
| 2023-01-05 | 180.00 | 500.00 |
| 2023-01-06 | 250.00 | 550.00 |
| 2023-01-07 | 130.00 | 560.00 |
| 2023-01-08 | 160.00 | 540.00 |
| 2023-01-09 | 220.00 | 510.00 |
| 2023-01-10 | 190.00 | 570.00 |
SUM(sale_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 这部分窗口函数，按照 sale_date 升序排列，窗口范围是当前行之前的 2 行到当前行，因此计算的是过去 3 天的销售额总和。 对于前两行，因为前面不足两行，所以直接从第一行开始计算。
6. PARTITION BY 的应用
如果我们的数据包含多个类别，例如不同地区的销售额，可以使用 PARTITION BY 将数据分成多个分区，分别计算每个地区的滚动求和。
假设我们有一个 sales 表，包含地区和销售额数据：
CREATE TABLE sales (
region VARCHAR(50),
sale_date DATE,
sale_amount DECIMAL(10, 2)
);
INSERT INTO sales (region, sale_date, sale_amount) VALUES
('North', '2023-01-01', 100.00),
('North', '2023-01-02', 150.00),
('North', '2023-01-03', 200.00),
('South', '2023-01-01', 80.00),
('South', '2023-01-02', 120.00),
('South', '2023-01-03', 160.00),
('North', '2023-01-04', 120.00),
('North', '2023-01-05', 180.00),
('South', '2023-01-04', 100.00),
('South', '2023-01-05', 140.00);现在，我们要计算每个地区的累计销售额。可以使用以下 SQL 查询：
SELECT
region,
sale_date,
sale_amount,
SUM(sale_amount) OVER (PARTITION BY region ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sale_amount
FROM
sales;这个查询的结果如下：
| region | sale_date | sale_amount | cumulative_sale_amount |
|---|---|---|---|
| North | 2023-01-01 | 100.00 | 100.00 |
| North | 2023-01-02 | 150.00 | 250.00 |
| North | 2023-01-03 | 200.00 | 450.00 |
| North | 2023-01-04 | 120.00 | 570.00 |
| North | 2023-01-05 | 180.00 | 750.00 |
| South | 2023-01-01 | 80.00 | 80.00 |
| South | 2023-01-02 | 120.00 | 200.00 |
| South | 2023-01-03 | 160.00 | 360.00 |
| South | 2023-01-04 | 100.00 | 460.00 |
| South | 2023-01-05 | 140.00 | 600.00 |
SUM(sale_amount) OVER (PARTITION BY region ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 这部分窗口函数，首先按照 region 进行分区，然后在每个分区内按照 sale_date 升序排列，窗口范围是从第一行到当前行，因此计算的是每个地区的累计销售额。
7. 时间范围的窗口定义
在某些情况下，我们需要根据时间范围来定义窗口，例如计算过去 7 天的销售额。 MySQL 8.0 引入了 RANGE 框架，可以更方便地处理时间范围的窗口。
但是，需要注意的是，RANGE 框架要求 ORDER BY 子句必须使用日期类型或数值类型。
假设我们仍然使用前面的 sales 表，要计算过去 7 天的销售额总和，可以使用以下 SQL 查询：
SELECT
sale_date,
sale_amount,
SUM(sale_amount) OVER (ORDER BY sale_date ASC RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS rolling_7_day_sale_amount
FROM
sales;这个查询的结果如下：
| sale_date | sale_amount | rolling_7_day_sale_amount |
|---|---|---|
| 2023-01-01 | 100.00 | 100.00 |
| 2023-01-02 | 150.00 | 250.00 |
| 2023-01-03 | 200.00 | 450.00 |
| 2023-01-04 | 120.00 | 570.00 |
| 2023-01-05 | 180.00 | 750.00 |
| 2023-01-06 | 250.00 | 1000.00 |
| 2023-01-07 | 130.00 | 1130.00 |
| 2023-01-08 | 160.00 | 990.00 |
| 2023-01-09 | 220.00 | 1060.00 |
| 2023-01-10 | 190.00 | 1130.00 |
SUM(sale_amount) OVER (ORDER BY sale_date ASC RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) 这部分窗口函数，按照 sale_date 升序排列，窗口范围是当前日期之前的 6 天到当前日期（包括当前日期）。 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW 表示时间范围的窗口，这比使用 ROWS 框架更加直观和易于理解。
8. 窗口函数与性能
虽然窗口函数功能强大，但在处理大数据集时，可能会影响查询性能。 这是因为窗口函数需要在排序后的数据集上进行计算，这会增加额外的开销。
以下是一些优化窗口函数性能的建议：
- 索引优化: 确保
PARTITION BY和ORDER BY子句中使用的列都有合适的索引。 - 避免不必要的窗口函数: 只在必要时使用窗口函数。如果可以使用
GROUP BY子句来实现相同的功能，优先使用GROUP BY。 - 优化窗口范围: 尽量缩小窗口范围，避免不必要的计算。
- MySQL 版本: 使用较新的 MySQL 版本，因为新版本通常会对窗口函数进行性能优化。
9. 其他注意事项
- 窗口函数只能在
SELECT列表中和ORDER BY子句中使用。不能在WHERE子句、GROUP BY子句或HAVING子句中使用。 - 可以在一个查询中使用多个窗口函数。
- 窗口函数可以与其他函数组合使用，例如
AVG(),MAX(),MIN(),RANK(),ROW_NUMBER()等。
10. 总结：灵活运用窗口函数，掌握滚动求和技巧
SUM() 函数作为窗口函数，在滚动求和中具有强大的应用。通过结合 ORDER BY 和 frame_clause，可以轻松实现各种复杂的滚动计算需求。 掌握了 PARTITION BY 可以对数据进行分区，分别计算每个分区的滚动求和。 掌握了 RANGE 框架，能够更加直观地处理时间范围的窗口。