lag()和lead()可向前/向后取最近非空值，需明确order by；coalesce()串联多层窗口函数实现就近填充；first_value()配合ignore nulls支持真正前向填充，但兼容性差；聚合函数如avg()填充易失真，应优先遵循业务规则。
用 LAG() 和 LEAD() 向前/向后取最近非空值
直接用 LAG() 或 LEAD() 获取相邻行的非空值，是最常用也最可控的填充方式。它不依赖全局统计，适合时间序列或有序业务数据（比如按日期排列的销售记录）。
关键点在于：必须明确 ORDER BY 子句，否则窗口无序，结果不可靠；同时要嵌套判断——先用 LAG() 尝试取上一行，若仍为空再继续向上找，但 SQL 不支持无限递归，所以通常只取 1–3 层。
-
LAG(value, 1) OVER (ORDER BY date)取前 1 行的value，但若那行也是NULL，结果仍是NULL - 可组合
CASE WHEN实现“优先用前一行，不行就用前两行”：CASE WHEN value IS NOT NULL THEN value WHEN LAG(value, 1) OVER (ORDER BY date) IS NOT NULL THEN LAG(value, 1) OVER (ORDER BY date) ELSE LAG(value, 2) OVER (ORDER BY date) END
- 注意性能：多层
LAG()会重复计算窗口，大数据量时建议用 CTE 预先算出各偏移值
COALESCE() 配合多个窗口函数一次性兜底
COALESCE() 本身不是窗口函数，但它能优雅串联多个 LAG()/LEAD() 结果，形成“就近填充链”。比嵌套 CASE 更简洁，语义也更直白。
典型场景是传感器数据断点修复：某设备每小时上报一次，偶尔漏报，需用前后最近的有效值补上。
- 写法示例：
COALESCE( value, LAG(value, 1) OVER (ORDER BY ts), LAG(value, 2) OVER (ORDER BY ts), LEAD(value, 1) OVER (ORDER BY ts), LEAD(value, 2) OVER (ORDER BY ts) )
- 顺序很重要：
COALESCE()从左到右求值，把最可能非空的放前面（比如当前行 > 前 1 行 > 后 1 行） - 避免无意义扩展：
LAG(value, 100)在稀疏数据里可能跨天，导致用错业务上下文
用 FIRST_VALUE() + IGNORE NULLS 简化向前填充逻辑
PostgreSQL 14+、Oracle、BigQuery 和 Snowflake 支持 IGNORE NULLS 修饰符，配合 FIRST_VALUE() 可直接拿到“往前看第一个非空值”，不用手动写多层 LAG()。
这是真正意义上的“前向填充（forward fill）”，语义清晰且代码紧凑，但兼容性差——MySQL 和旧版 PostgreSQL 不支持。
- 正确写法：
FIRST_VALUE(value) OVER ( ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS )
- 必须加
ROWS BETWEEN ...，否则默认是RANGE，在时间戳重复时行为异常 - 如果数据库不支持
IGNORE NULLS，硬凑ROW_NUMBER()+ 自连接反而更慢，不如退回LAG()链
为什么不用 AVG() 或 MAX() 窗口函数直接填充？
用聚合类窗口函数（如 AVG(value) OVER (...)）填充缺失值，表面上看能“平滑”数据，实际多数情况是错的。
问题核心在于：聚合函数会混入其他行的有效值，破坏单点观测意义。比如某天销售额缺失，用前后 7 天均值填充，等于假设该天表现完全服从局部均值——这在促销日、故障日等场景下严重失真。
- 仅当业务明确要求“用区间均值代表缺失期”时才考虑，例如月度能耗估算
-
AVG()对NULL自动过滤，但结果类型可能变成DECIMAL，和原字段类型不一致，插入时需显式CAST - 更大的坑是：如果窗口内全为
NULL，AVG()返回NULL，不会报错，容易掩盖数据质量问题
真正难的不是语法，而是判断“这个空该不该填”以及“用谁来填”——业务规则永远比窗口函数优先级高。
