子查询实现滑动窗口聚合的关键限制是sql标准不支持按行偏移的动态窗口，where中子查询无法引用当前行位置，静态范围易因数据缺失或重复导致窗口漂移；相关子查询需依赖排序字段（如event_time）约束邻域范围。
子查询实现滑动窗口聚合的关键限制
SQL 标准本身不支持直接用子查询定义“按行偏移的动态窗口”，WHERE 子句里的子查询无法引用外部查询的当前行位置（比如 ROW_NUMBER() 值），所以单纯靠嵌套 SELECT + WHERE id BETWEEN ... 很难真正实现“每行对应自己前后 N 行”的滑动窗口。常见错误是写成静态范围（如固定 id > t1.id - 3），结果在数据有缺失、排序非单调或存在重复时间戳时，窗口实际覆盖的物理行数会漂移。
用相关子查询模拟滑动窗口（适用于小数据量）
当必须用子查询且数据量可控（比如单表万行以内），可借助相关子查询 + 排序字段约束范围。核心是：先确定排序依据（如 event_time 或带序号的 seq），再让子查询对每一行计算其“时间/序号邻域内”的聚合值。
示例：计算每条记录过去 3 秒内（含自身）的平均 value
SELECT
t1.event_time,
t1.value,
(SELECT AVG(t2.value)
FROM metrics t2
WHERE t2.event_time >= t1.event_time - INTERVAL '3' SECOND
AND t2.event_time
- 必须确保
event_time有索引，否则性能急剧下降 -
INTERVAL '3' SECOND是 PostgreSQL 语法；MySQL 用DATE_SUB(t1.event_time, INTERVAL 3 SECOND)；SQL Server 用DATEADD(second, -3, t1.event_time) - 若存在多条同时间戳记录，该写法会把它们全纳入窗口——这可能是预期行为，也可能是 bug 源头
窗口函数才是滑动窗口的正确解法
所有主流 SQL 引擎（PostgreSQL 8.4+、SQL Server 2012+、MySQL 8.0+、BigQuery、Trino）都支持标准窗口函数，AVG() OVER (ORDER BY ... ROWS BETWEEN ... AND ...) 才是语义清晰、性能可控的滑动窗口方案。子查询只是兼容旧版本的权宜之计。
等价于上例的写法：
SELECT
event_time,
value,
AVG(value) OVER (
ORDER BY event_time
RANGE BETWEEN INTERVAL '3' SECOND PRECEDING AND CURRENT ROW
) AS avg_last_3s
FROM metrics;
-
RANGE按值范围（适合时间/数值型排序键）；ROWS按物理行数（需配合ROW_NUMBER()） - MySQL 不支持
RANGE配合INTERVAL，只能退化为ROWS BETWEEN 3 PRECEDING AND CURRENT ROW，此时要求数据严格等间隔且无缺失 - 使用
PARTITION BY device_id可做分组内独立窗口，子查询极难干净实现这点
子查询强行模拟 ROWS 窗口的隐患
有人用 (SELECT COUNT(*) FROM t2 WHERE t2.sort_col 先算序号，再嵌套一层查 <code>rn BETWEEN t1.rn-2 AND t1.rn。这种写法问题明显：
- 两次全表扫描，复杂度 O(n²)，10 万行就明显卡顿
- 若
sort_col有重复值，COUNT(*)生成的rn不稳定（相同值可能被任意排序），窗口边界错位 - 无法利用索引加速内层
WHERE，优化器大概率放弃索引走全表 - 在分布式 SQL 引擎（如 Trino、Spark SQL）中，相关子查询可能触发广播或 shuffle，比窗口函数慢一到两个数量级
真正需要子查询介入的场景，通常是窗口函数无法表达的逻辑——比如窗口内取满足某条件的最新一条记录（FIRST_VALUE 不够用），这时才考虑用 LATERAL 或带 ORDER BY ... LIMIT 1 的子查询，而不是硬套滑动聚合。
