MySQL 窗口帧 EXCLUDE 规则如何影响移动聚合结果

先给结论：MySQL 目前不能执行窗口帧的 EXCLUDE 子句。MySQL 8.4 与当前 9.7 官方限制文档都明确说明，这个构造会被解析器识别，但执行时产生错误。因此，它不会悄悄改变移动聚合结果，也不能在生产 SQL 中直接使用。

MySQL 8.4 官方限制：https://dev.mysql.com/doc/refman/8.4/en/window-function-restrictions.html

真正需要回答的问题分成两层：第一，SQL 标准里的 EXCLUDE CURRENT ROW、GROUP、TIES 会怎样改变参与聚合的行集合；第二，在 MySQL 中怎样用受支持的窗口函数和显式条件实现相同业务目标。

先保护什么：移动聚合的集合语义

把移动聚合当成一个数据正确性资产，可以更容易发现风险。窗口帧先根据 ROWS 或 RANGE 确定候选行，EXCLUDE 再从候选行里移除一部分。它不改变 frame 的起止边界，而是改变最终交给 SUM、AVG、COUNT 的集合。

MySQL 原生支持的 frame units 只有 ROWS 和 RANGE。官方文档还提醒：有 ORDER BY 而没有显式 frame 时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，并包含当前行的所有 peers。仅仅加一个 ORDER BY，就可能让 SUM 的结果与无 ORDER BY 时不同。因此，迁移 EXCLUDE 查询时应始终写出明确窗口帧。

EXCLUDE 在标准 SQL 中改变了什么

标准语义通常分为四类：

• EXCLUDE NO OTHERS：不排除任何行，也是默认语义。
• EXCLUDE CURRENT ROW：只排除当前输出行，其他 peer 行仍参与聚合。
• EXCLUDE GROUP：排除当前行及与它在窗口 ORDER BY 上相等的全部 peer 行。
• EXCLUDE TIES：保留当前行，但排除与当前行同值的其他 peer 行。

下面是标准 SQL 的表达方式，用来说明语义；它不能在 MySQL 中执行。

-- 标准 SQL 示例：五行移动平均中排除当前输出行
SELECT
    sensor_id,
    observed_at,
    value,
    AVG(value) OVER (
        PARTITION BY sensor_id
        ORDER BY observed_at
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS neighbor_avg
FROM sensor_reading;

如果当前窗口值为 10、20、30、40、50，当前行是 30，那么普通 AVG 使用五行，EXCLUDE CURRENT ROW 只使用 10、20、40、50。GROUP 和 TIES 则还要看 ORDER BY 值是否重复，不能仅凭物理行位置判断。

最高风险入口：语法被识别不代表功能可用

迁移脚本最容易误判的一点，是解析器能够识别关键字。MySQL 对 GROUPS frame、EXCLUDE、IGNORE NULLS 和 FROM LAST 都采用“可解析但不支持”的策略。静态 SQL 格式化器可能不报错，真正发到服务器时才失败。

因此不要用“预编译工具接受了语法”作为兼容性证据。数据库版本升级也不能凭猜测放行：截至当前 9.7 官方限制文档，EXCLUDE 仍然是解析后报错。跨 PostgreSQL、SQLite 等数据库迁移时，应把 EXCLUDE 列为方言能力清单中的显式项。

MySQL 改写：安全排除当前行

对 SUM 和 AVG，最实用的替代方式是先算完整 frame 的 SUM 与 COUNT，再减去当前行贡献。重点是不能只写 SUM(frame)-value：当前值可能为 NULL，而且排除后可能没有任何非 NULL 行，此时标准 SUM 和 AVG 应返回 NULL，而不是 0。

-- 第一步：只使用 MySQL 支持的 ROWS 窗口，计算完整 frame 的和与非空计数
WITH framed AS (
    SELECT
        sensor_id,
        observed_at,
        reading_id,
        value,
        SUM(value) OVER w AS frame_sum,
        COUNT(value) OVER w AS frame_count
    FROM sensor_reading
    WINDOW w AS (
        PARTITION BY sensor_id
        ORDER BY observed_at, reading_id
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    )
)
SELECT
    sensor_id,
    observed_at,
    reading_id,
    value,
    -- 排除当前非空值后若计数为 0，SUM 应保持空帧的 NULL 语义
    CASE
        WHEN frame_count - (value IS NOT NULL) = 0 THEN NULL
        ELSE frame_sum - COALESCE(value, 0)
    END AS moving_sum_excluding_current,
    -- AVG 使用修正后的和除以修正后的计数，NULLIF 防止除零
    (frame_sum - COALESCE(value, 0))
        / NULLIF(frame_count - (value IS NOT NULL), 0)
        AS moving_avg_excluding_current
FROM framed
ORDER BY sensor_id, observed_at, reading_id;

这里在 ORDER BY 中加入 reading_id，目的是让 ROWS 的物理顺序稳定。如果只按可能重复的时间戳排序，数据库可以在 peers 之间选择不同的先后顺序，移动 frame 的成员就可能不稳定。

COUNT、MIN、MAX 应怎样处理

COUNT(value) EXCLUDE CURRENT ROW 最简单：完整 frame 的 COUNT(value) 减去 value IS NOT NULL。而 COUNT(*) 应直接减 1，因为当前行一定存在。

-- COUNT(value) 只统计非 NULL，因此按当前值是否非空减去 0 或 1
COUNT(value) OVER w - (value IS NOT NULL) AS count_value_excluding_current,

-- COUNT(*) 统计物理行，排除当前行时固定减 1
COUNT(*) OVER w - 1 AS count_rows_excluding_current

MIN 和 MAX 不能用减法改写。当前行如果恰好是最小值或最大值，需要寻找 frame 内的第二候选；如果极值重复，还要判断排除一行后是否仍有相同极值。对于这类非可逆聚合，优先使用显式 self join 或相关子查询，按稳定行号限定 frame，再排除当前主键。

GROUP 与 TIES：先定义 peer，再谈替代

GROUP 和 TIES 的风险高于 CURRENT ROW，因为它们依赖“哪些行是 peers”。标准定义以窗口 ORDER BY 表达式相等为准。若 ORDER BY 只有 observed_at，同一时间戳的行属于同一 peer group；若再加入唯一的 reading_id，peer group 就通常只剩当前行。

在 MySQL 中没有一条通用的减法公式能覆盖所有有界 ROWS/RANGE frame 与 peers 组合。较安全的做法是把业务 peer key 和稳定位置显式建模，然后在相关子查询中同时限定 frame 位置与排除条件：

-- 示例约定 seq_no 是每个 sensor 内稳定连续的位置，observed_at 是业务 peer key
SELECT
    cur.sensor_id,
    cur.seq_no,
    cur.observed_at,
    cur.value,
    (
        SELECT SUM(n.value)
        FROM sensor_reading AS n
        WHERE n.sensor_id = cur.sensor_id
          -- 先限定与 ROWS 2 PRECEDING / 2 FOLLOWING 等价的业务窗口
          AND n.seq_no BETWEEN cur.seq_no - 2 AND cur.seq_no + 2
          -- 再排除当前时间戳对应的整个 peer group
          AND NOT (n.observed_at  cur.observed_at)
    ) AS moving_sum_excluding_peer_group
FROM sensor_reading AS cur;

是 MySQL 的 NULL-safe equality。用 NOT (a b) 可以让两个 NULL 被视为同一 peer key；是否符合业务要求必须明确。这个查询表达的是“按 seq_no 定位移动范围，再排除同时间戳组”的业务规则，而不是声称 MySQL 已实现标准 EXCLUDE GROUP。

EXCLUDE TIES 的替代也类似：排除同 peer key 的其他行，但保留当前主键。可把条件改为“peer key 不同，或者主键等于当前主键”。由于相关子查询可能放大扫描成本，应为 (sensor_id, seq_no) 和 peer key 设计合适索引，并用真实数据评估执行计划。

风险分级：哪些改写可以直接用

审计记录：让聚合差异可解释

上线前不要只比较最终 AVG。建议为同一批固定输入同时输出或离线记录：frame 起止位置、frame_count、当前值是否 NULL、排除后的计数、peer key、最终聚合值。只要其中一项不一致，就能快速区分是排序不稳定、NULL 语义、peer 定义还是聚合公式的问题。

对于迁移任务，还应记录源数据库类型、源 SQL 的 EXCLUDE 模式、MySQL 目标版本、改写策略和已验证样例。这样未来 MySQL 若正式支持 EXCLUDE，也能判断是保留兼容改写还是切回原生语法。

发布前验证清单

• 确认目标 MySQL 版本的官方限制文档，而不是只检查 SQL 是否能被格式化。
• 显式写出 ROWS 或 RANGE frame，避免默认 RANGE 把 peers 纳入结果。
• 为 ROWS 排序增加稳定的唯一键，并单独定义业务 peer key。
• 覆盖当前值为 NULL、frame 仅当前行、frame 边缘不足、peer 重复和全部值为 NULL。
• SUM 与 AVG 同时修正计数；MIN/MAX 使用显式候选集合。
• 对相关子查询检查索引与执行计划，避免正确性修复引入不可接受的扫描成本。

所以，MySQL 中 EXCLUDE 对移动聚合的直接影响是：查询无法执行。理解四种标准语义仍然重要，因为它决定了改写时究竟要排除当前物理行、整个同值组，还是仅排除其他同值行。把 frame、peer、NULL 和空帧四个边界写清楚，才能得到与源语义一致、可审计的 MySQL 结果。

官方资料

• MySQL 8.4 Window Function Restrictions：https://dev.mysql.com/doc/refman/8.4/en/window-function-restrictions.html
• MySQL 8.4 Window Function Frame Specification：https://dev.mysql.com/doc/refman/8.4/en/window-functions-frames.html
• MySQL 9.7 Window Function Restrictions：https://dev.mysql.com/doc/refman/9.7/en/window-function-restrictions.html
• MySQL 8.4 Window Function Concepts：https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html