SQL窗口函数：为什么子查询里不能直接计算移动平均值？
开门见山地说，想用子查询来计算真正的移动平均值，这条路基本是走不通的。核心原因在于，窗口函数必须直接写在顶层的SELECT语句里，一旦嵌套进子查询，等待你的多半是语法错误，或者更隐蔽的逻辑混乱。
为什么子查询里套 A VG() OVER() 会报错？
很多开发者习惯用子查询来分步处理逻辑，于是可能会写出下面这样的代码：
SELECT date, amount, (SELECT A VG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM sales s2 WHERE s2.date <= s1.date) AS ma FROM sales s1;
遗憾的是，无论是在MySQL、PostgreSQL还是SQL Server中，执行这类语句几乎都会立刻碰壁。原因非常直接：OVER()子句被设计为只能在最外层的SELECT列表或ORDER BY子句中使用。数据库引擎的解析器一看到子查询里出现了A VG(...) OVER(...)这种结构，就会直接拒绝执行。
- SQL Server会明确告诉你：
Windowed functions can only appear in the SELECT or ORDER BY clauses。 - MySQL 8.0+ 会抛出错误：
This function is not allowed in this context。 - PostgreSQL 的报错信息也很清晰：
window function calls cannot appear in subqueries。
这背后的逻辑与SQL语句的执行顺序有关。窗口函数的计算发生在结果集几乎已经确定的阶段，而子查询的求值时机则早得多。把窗口函数塞进子查询，相当于打乱了引擎固有的执行计划，它自然就“不干了”。
想“分步计算”移动平均？用CTE才是正道
如果业务逻辑确实复杂，需要先对数据进行清洗、排序或补全，然后再计算移动平均，正确的做法是使用WITH子句（即公共表表达式，CTE）来拆解步骤，而不是求助于子查询。
WITH clean_data AS (
SELECT date, amount,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date) AS rn
FROM sales
WHERE amount IS NOT NULL
),
ordered_by_product AS (
SELECT *,
A VG(amount) OVER (
PARTITION BY product_id
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma_3d
FROM clean_data
)
SELECT date, product_id, amount, ma_3d
FROM ordered_by_product
WHERE rn >= 3; -- 可选：过滤掉前两行（因为前两行无法构成完整的3期平均）
CTE的关键在于，它进行的是逻辑分层，而非物理嵌套。每一个CTE块内的SELECT语句，对于数据库引擎来说，仍然处于“顶层”的上下文环境中，因此在其中使用OVER()窗口函数是完全合法的。
这里有几点需要特别注意：
- 切忌在
WHERE或HA VING子句中直接引用窗口函数的结果。因为这些子句的执行时机早于窗口计算，数据还没准备好。 - 如果确实需要根据移动平均值进行过滤（例如
ma_3d > 100），必须将整个包含窗口计算的查询作为子查询或CTE，然后在外层SELECT之后再应用WHERE条件。 - 另外，在CTE内部使用的
ORDER BY通常只服务于窗口函数本身，并不保证最终结果集的顺序。为了结果的可预测性，最外层的SELECT最好还是显式地加上ORDER BY。
如果非要嵌套怎么办？JOIN模拟是下策
在某些极端场景下，比如使用的数据库版本较老（MySQL 5.7），根本不支持窗口函数，这时才需要考虑用自连接（Self-JOIN）来模拟移动平均的计算。但必须清醒认识到，这是一种性能代价高且容易出错的替代方案。
SELECT s1.date, s1.amount,
A VG(s2.amount) AS ma_3d
FROM sales s1
JOIN sales s2 ON s2.date BETWEEN DATE_SUB(s1.date, INTERVAL 2 DAY) AND s1.date
GROUP BY s1.date, s1.amount
ORDER BY s1.date;
这种写法的问题相当明显：
- 它依赖日期连续性。如果日期有重复或缺失，用
INTERVAL进行日期范围匹配，与窗口函数中ROWS BETWEEN基于物理行数的控制逻辑是两回事，结果可能不一致。 - 它缺乏
PARTITION BY的便捷性。如果想按产品、用户分组计算，逻辑会变得非常复杂，容易导致跨组数据混淆。 - 性能是硬伤。自连接会产生N²级别的数据膨胀，一旦数据量上万，查询速度就会显著下降。
- 在MySQL 5.7等版本中，日期运算和
BETWEEN的边界行为可能并不如预期那样严格，导致计算结果不可靠。
说到底，当我们需要计算移动平均值时，最直接、最高效、最可靠的方法就是使用标准的A VG() OVER()窗口函数。一个容易被忽略的核心要点是：窗口函数并非可以随意放置的普通函数，它与SELECT语句的执行阶段深度绑定。一旦放错了位置，问题就不是结果准不准了，而是查询根本跑不起来。
