SQL 使用窗口函数实现 gap 填充

sql中用窗口函数前向填充空缺值的核心思路是：先用条件累计和为连续非空段打唯一group_id，再按组取首个非空值填充；若开头全为空，需结合LAST_VALUE IGNORE NULLS或MAX+UNBOUNDED PRECEDING实现真正前向填充。

SQL 中用窗口函数填充 gap（空缺值），核心思路是：先标记连续非空段，再对每段内空值用该段首个非空值向前填充（或用上一个非空值）。不依赖循环或自连接，纯靠 LAG、COALESCE 和分组标识（如累计计数）即可实现。

识别非空值的“段落”起点

关键在于给每个连续的非空值块打上唯一 ID。常用方法是用条件累计和：
对每一行判断 value IS NOT NULL，然后对这个布尔结果做 SUM() OVER (ORDER BY ...)。每次遇到非空值，累计和加 1；空值则继承前一个累计和。这样，同一段连续非空值（及其后续紧邻空值）就拥有相同 group_id。

示例：

SELECT *,        SUM(CASE WHEN value IS NOT NULL THEN 1 ELSE 0 END)           OVER (ORDER BY id) AS grp FROM your_table;

对每段取首个非空值（前向填充）

有了 grp 后，在该分组内用 FIRST_VALUE(value) OVER (PARTITION BY grp ORDER BY id) 即可拿到该段第一个非空值。注意：需确保 ORDER BY id 是严格递增且无重复，否则可能取错行。

更稳妥写法（兼容多数场景）：

• 用 MAX(value) OVER (PARTITION BY grp) —— 因为每段最多只有一个非空值“源头”，其余为空，MAX 自然返回那个非空值
• 或用 FIRST_VALUE(value) IGNORE NULLS OVER (PARTITION BY grp ORDER BY id)（postgresql 14+/oracle/SQL Server 支持；mysql 8.0+ 不支持 IGNORE NULLS）

完整可运行示例（MySQL 8.0+ / PostgreSQL）

假设表 t 有列 id（顺序）、val（含 null）：

SELECT id, val,        FIRST_VALUE(val) OVER (          PARTITION BY grp           ORDER BY id           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOwinG        ) AS filled_val FROM (   SELECT *,          SUM(val IS NOT NULL) OVER (ORDER BY id) AS grp   FROM t ) t1;

说明：
• val IS NOT NULL 在 MySQL 中返回 1/0，可直接参与 SUM
• ROWS BETWEEN ... 确保 FIRST_VALUE 扫描整组（避免默认仅看当前行及之前）
• 若某段全为 NULL（即开头就是空），FIRST_VALUE 返回 NULL，符合预期

处理开头全是 NULL 的情况

如果数据开头连续多个 NULL，上述 grp=0 段里没有非空值，FIRST_VALUE 或 MAX 都会返回 NULL，无法“向后借”。此时需额外逻辑：
• 先用 LAG 找上一个非空值（但开头没上一个）
• 更通用做法：用 MAX(val) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) 做“到目前为止最大非空值”，再配合 COALESCE 实现简单前向填充（不区分段，只填到首次出现非空为止）

例如仅需“把每个 NULL 替换为它前面最近的非 NULL 值”（标准前向填充）：

SELECT id, val,        COALESCE(          val,          LAST_VALUE(val) IGNORE NULLS OVER (ORDER BY id ROWS UNBOUNDED PRECEDING)        ) AS filled_val FROM t;

注：LAST_VALUE(... IGNORE NULLS) 在支持该语法的数据库中最简洁；不支持时可用子查询 + ROW_NUMBER() 模拟。