问题：MYSQL中如何使用preceding实现滑动窗口计算？

Question

在MySQL中，如何使用`PRECEDING`关键字配合窗口函数实现滑动窗口计算？例如，在计算某列的移动平均或累计求和时，如何定义动态的窗口范围（如当前行往前5行）？为何在使用`ROWS BETWEEN`与`PRECEDING`组合时，结果未按预期滑动？是否存在性能优化的考量或语法限制？通过具体示例说明如何正确构建包含`PRECEDING`的滑动窗口查询，以实现高效、准确的数据分析。

Answer 1

一、MySQL中窗口函数与滑动窗口计算简介

在MySQL 8.0及以上版本中，窗口函数（Window Functions）为数据分析提供了强大的支持。其中，PRECEDING关键字常用于定义窗口的起始边界，尤其适用于构建滑动窗口（Sliding Window）场景，如移动平均、累计求和等。

二、基本语法与使用方式

MySQL中使用窗口函数的基本语法如下：

SELECT
    column_name,
    window_function(column_name) OVER (
        ORDER BY order_column
        ROWS BETWEEN X PRECEDING AND Y FOLLOWING
    ) AS result_column
FROM table_name;

• ORDER BY：用于定义窗口的排序方式，是滑动窗口的必要条件。
• ROWS BETWEEN：定义窗口行范围。
• X PRECEDING：表示当前行往前X行。
• Y FOLLOWING：表示当前行往后Y行。

三、滑动窗口示例：移动平均与累计求和

假设我们有一个销售记录表sales_data，结构如下：

id	sale_date	amount
1	2023-01-01	100
2	2023-01-02	120
3	2023-01-03	130
4	2023-01-04	110
5	2023-01-05	140
6	2023-01-06	150
7	2023-01-07	160
8	2023-01-08	170
9	2023-01-09	180
10	2023-01-10	190

现在我们希望计算amount列的5日移动平均值（包括当前行），可以使用如下SQL：

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales_data;

同样地，计算累计求和：

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM sales_data;

四、为何结果未按预期滑动？常见问题分析

在使用ROWS BETWEEN与PRECEDING组合时，结果未按预期滑动的原因通常有以下几种：

1. 缺少明确的ORDER BY子句：窗口函数必须依赖有序的行，否则无法确定行之间的相对位置。
2. 未正确理解PRECEDING/FOLLOWING的含义：例如，4 PRECEDING表示当前行往前4行，共5行数据。
3. 数据中存在重复的排序值：若ORDER BY字段有重复值，MySQL将视为同一行，导致窗口范围不准确。

五、性能优化与语法限制

MySQL窗口函数的性能优化主要集中在以下方面：

• 索引优化：在ORDER BY字段上建立索引，可显著提升性能。
• 避免大窗口范围：如使用UNBOUNDED PRECEDING时，若数据量大，会导致性能下降。
• 分区窗口（PARTITION BY）：若需按类别分组计算窗口函数，应使用PARTITION BY来限制窗口范围。

语法限制方面：

• MySQL 8.0才开始支持窗口函数，旧版本不兼容。
• 不支持RANGE类型的窗口定义（如RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND CURRENT ROW）。

六、完整示例：正确构建滑动窗口查询

以下是一个完整的SQL示例，展示如何正确使用PRECEDING关键字构建滑动窗口查询：

SELECT
    sale_date,
    amount,
    AVG(amount) OVER w AS moving_avg_5days,
    SUM(amount) OVER w AS rolling_sum_5days
FROM sales_data
WINDOW w AS (
    ORDER BY sale_date
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
);

输出结果示例：

sale_date	amount	moving_avg_5days	rolling_sum_5days
2023-01-01	100	100.00	100
2023-01-02	120	110.00	220
2023-01-03	130	116.67	350
2023-01-04	110	115.00	460
2023-01-05	140	120.00	600
2023-01-06	150	130.00	750
2023-01-07	160	140.00	900
2023-01-08	170	150.00	1050
2023-01-09	180	160.00	1200
2023-01-10	190	170.00	1350

七、进阶：使用PARTITION BY进行分组滑动计算

若数据表中包含多个产品类别，我们需要对每个类别单独计算滑动窗口。此时应使用PARTITION BY：

SELECT
    category,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY category
        ORDER BY sale_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales_data_by_category;

八、性能分析与执行计划

使用EXPLAIN命令可以查看窗口函数的执行计划：

EXPLAIN SELECT
    sale_date,
    amount,
    AVG(amount) OVER w
FROM sales_data
WINDOW w AS (
    ORDER BY sale_date
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
);

关注字段：Extra中应包含Using filesort（若未使用索引）或Using index（若已优化）。

graph TD A[开始] --> B[选择目标列] B --> C{是否需要滑动窗口计算?} C -->|是| D[使用窗口函数] D --> E[定义ORDER BY] E --> F[设置ROWS BETWEEN] F --> G[使用PRECEDING/FOLLOWING] G --> H[执行查询] C -->|否| I[普通聚合或查询] H --> J[结束] I --> J