如何利用SQL窗口函数进行数据插值填充_使用LAG与COALESCE组合-SQL-PHP中文网

LAG()与COALESCE()组合仅支持单步机械位移，无法处理时间连续性、整行缺失或动态空值长度；可靠前向填充需用COUNT非空值分组+FIRST_VALUE广播，插值则须先重建时间轴再计算。

如何利用sql窗口函数进行数据插值填充_使用lag与coalesce组合

LAG() 和 COALESCE() 组合只能做“单步前向填充”，无法真正解决时间序列缺失值问题——它只取上一行，不跳过 NULL，也不感知时间连续性。

LAG(col, 1) OVER (ORDER BY ts) 返回 NULL 的真实原因

当上一行的 col 是 NULL 时，LAG() 照样返回 NULL；它不“往前找”，只是机械位移。常见错误是以为 COALESCE(LAG(col), col) 能补空，但若当前行 col 本就是 NULL，而 LAG(col) 又是 NULL，结果还是 NULL。

LAG(col, 1) 不保证非空，只保证“位置上一行”
时间戳重复或乱序时，ORDER BY ts 可能让 LAG 跳到错误行（比如同一天多条记录，未加二级排序）
原始数据缺整行（如 2024-05-01 完全没记录），LAG() 根本接触不到那天的前后值

COALESCE(LAG(), LAG(,2), LAG(,3)) 的硬编码陷阱

有人用 COALESCE(LAG(col,1), LAG(col,2), LAG(col,3)) 模拟“往前找”，但这依赖缺失长度可预估——实际中可能连续缺 5 天、12 天，甚至跨月。一旦深度不够，空值照旧。

SQL 标准不支持动态层数，必须写死参数，维护成本高
性能随层数线性下降：每多一层 LAG() 就多一次窗口扫描
遇到开头连续 NULL（如视频第一天就没数据），所有 LAG() 全为 NULL，COALESCE 无计可施

为什么 FIRST_VALUE + 分组才是可靠方案

真正向前填充的本质，是把每个非空值当作“锚点”，让它覆盖后面所有直到下一个非空值之间的空行。这需要构造逻辑分组 grp，而非依赖物理行偏移。

用 COUNT(col) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING) 生成 grp：每遇到一个非空 col，计数+1；NULL 行沿用前一个计数
再用 FIRST_VALUE(col) OVER (PARTITION BY grp ORDER BY ts ROWS UNBOUNDED PRECEDING) 广播每组首个非空值
ROWS UNBOUNDED PRECEDING 必须显式写出，否则默认 RANGE 在时间戳相同时会聚合错误行

插值类填充（如线性）不能只靠 LAG/LEAD

如果要算“相邻非空值之间的等距插值”，比如 ID=2 值为 10、ID=5 值为 22，中间 ID=3/4 缺失，需填 14 和 18——这时 LAG() 和 LEAD() 只能分别拿到 10 和 22，但无法定位“下个非空在哪儿”。必须先用子查询或 CTE 找出每个空行的 prev_val 和 next_val 对应的 prev_ts、next_ts，再计算斜率。