SQL相关子查询如何对比当前与前一记录差值

在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()的行为未定义，不同执行计划结果可能不一致。

实际跑起来就会发现，最麻烦的反而不是语法本身，而是时间字段重复、排序键不唯一导致的“前一行”错位。这个问题子查询解决不了，只能先清洗数据，确保排序键唯一可靠。