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

不能直接在子查询中使用LAG()，因为窗口函数需整组排序上下文，而相关子查询对每行独立执行，二者执行模型冲突；必须将LAG()置于最外层SELECT或CTE中。

为什么不能直接用 LAG() 就写子查询？

很多人想在子查询里直接调用窗口函数，比如写成 (SELECT LAG(value) FROM t WHERE id = t1.id) —— 这会报错：Window function not allowed in subquery。MySQL 8.0+ 和 PostgreSQL 虽支持窗口函数，但它们不能出现在相关子查询的 SELECT 列表或 WHERE 条件中。相关子查询本质是“对每行独立执行一次”，而 LAG() 需要整组排序上下文，二者执行模型冲突。

用自连接模拟前一行（兼容 MySQL 5.7 / PostgreSQL / SQL Server）

核心思路：把表和自己按顺序号做左连接，让当前行匹配上「序号刚好小 1」的那行。前提是必须有可排序的字段（如时间戳、自增 ID）。

• 先用 ROW_NUMBER() 或变量生成序号（MySQL 5.7 用变量，8.0+ 用 ROW_NUMBER() OVER (ORDER BY ts)）
• 再 LEFT JOIN 自身，条件是 t1.rn = t2.rn + 1
• 差值计算放在主查询 SELECT 中：t1.value - COALESCE(t2.value, 0)，避免 NULL

示例（MySQL 8.0+）：

SELECT   t1.id,  t1.value,  t1.value - COALESCE(t2.value, 0) AS diffFROM (  SELECT id, value, ts, ROW_NUMBER() OVER (ORDER BY ts) AS rn  FROM sensor_data) t1LEFT 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+ 直接用窗口函数更稳，但别塞进子查询

如果你用的是 MySQL 8.0+ 或 PostgreSQL，优先放弃相关子查询，改用带窗口函数的派生表或 CTE。强行塞进子查询只会触发错误或性能灾难。

• 窗口函数必须出现在最外层 SELECT 或 FROM 子句的派生表中
• LAG(value) OVER (ORDER BY ts) 返回的是同一次扫描中的前一行值，无需多次执行
• 如果业务逻辑强制要求「每行触发一次子查询」（比如权限校验），那窗口函数根本不可用，得换应用层处理

正确写法：

SELECT   id,  value,  value - LAG(value, 1, 0) OVER (ORDER BY ts) AS diffFROM sensor_data;

PostgreSQL 中 LAG() 的默认值陷阱

PostgreSQL 的 LAG(value, offset, default) 第三个参数是当取不到前一行时返回的默认值。如果不显式指定，默认返回 NULL，导致整个差值变 NULL。尤其在计算累计变化时，漏掉这个参数会让第一行之后全崩。

• 写成 LAG(value, 1, 0) 比 COALESCE(LAG(value), 0) 更安全——后者在 LAG 返回 NULL 时才兜底，但 LAG 本身已定义行为
• 注意 offset 是整数，不能是列名或表达式；default 类型需和 value 兼容
• ORDER BY 必须明确，否则 LAG() 行为未定义，不同执行计划可能结果不一致

实际跑起来才发现，最麻烦的不是语法，而是时间字段重复、排序键不唯一导致的「前一行」错位——这没法靠子查询修复，得先清洗数据。