在SQL开发中，处理前后行数据差值是个很常见的需求，比如计算传感器数据的连续变化量。很多人第一反应就是用LAG()函数，但把它塞进子查询里，往往就会撞上“Window function not allowed in subquery”的错误。这事儿其实挺容易踩坑，今天就来拆解一下。
为什么不能直接在子查询里用LAG()？
先说结论：LAG()这类窗口函数需要依赖整个分组排序的上下文，而相关子查询是“对每一行独立执行”的机制，两者执行模型天然冲突。所以，像 (SELECT LAG(value) FROM t WHERE id = t1.id) 这种写法，在MySQL 8.0+、PostgreSQL等数据库里都会直接报错。窗口函数必须出现在最外层的SELECT或者CTE中，不能嵌套在相关子查询里。
自连接法：兼容旧版数据库的备选方案
如果你用的是MySQL 5.7、SQL Server这类不支持窗口函数的版本，或者就是想绕过子查询的限制，自连接通常是最靠谱的替代方案。核心思路就是：给数据按时间顺序排好序，然后让当前行匹配上“序号刚好小1”的那一行。
具体操作分三步：
- 先用ROW_NUMBER()或变量生成序号。MySQL 5.7用变量，8.0+直接用ROW_NUMBER() OVER (ORDER BY ts)。
- 然后做LEFT JOIN，条件是
t1.rn = t2.rn + 1，这样t2就是t1的前一行。 - 最后在主查询SELECT里计算差值：
t1.value - COALESCE(t2.value, 0)，避免NULL值报错。
示例（MySQL 8.0+）：
SELECT t1.id, t1.value, t1.value - COALESCE(t2.value, 0) AS diff FROM ( SELECT id, value, ts, ROW_NUMBER() OVER (ORDER BY ts) AS rn FROM sensor_data ) t1 LEFT JOIN ( SELECT id, value, ts, ROW_NUMBER() OVER (ORDER BY ts) AS rn FROM sensor_data ) t2 ON t1.rn = t2.rn + 1;
直接窗口函数法：更简洁，但别塞进子查询
如果用的是MySQL 8.0+或PostgreSQL，直接上窗口函数是最省心的。但有个关键点：LAG()必须出现在最外层SELECT或FROM子句的派生表中，不能强行塞进相关子查询——否则只会触发错误，或者性能爆炸。
正确写法其实很简单，一行搞定：
SELECT id, value, value - LAG(value, 1, 0) OVER (ORDER BY ts) AS diff FROM sensor_data;
这里LAG()会返回同一扫描中前一个行的值，不需要多次执行，效率高得多。
PostgreSQL的默认值陷阱
使用PostgreSQL时要特别留意：LAG()函数有三个参数，第三个参数是当取不到前一行时返回的默认值。如果不显式指定，默认返回NULL，导致第一行以及后续所有行的差值都变成NULL——这在计算累计变化时很容易出问题。
推荐写法是 LAG(value, 1, 0)，比用 COALESCE(LAG(value), 0) 更安全。因为后者是在LAG()返回NULL时才兜底，而LAG()本身的行为已经定义好了。另外，ORDER BY必须明确指定，否则LAG()的行为未定义，不同执行计划结果可能不一致。
实际跑起来就会发现，最麻烦的反而不是语法本身，而是时间字段重复、排序键不唯一导致的“前一行”错位。这个问题子查询解决不了，只能先清洗数据，确保排序键唯一可靠。
