Snowflake数据库如何使用前序非空值填充字段NULL值

结论 #

单次调用固定偏移的lag()无法直接实现向前非空值填充（即LOCF，末次观测值结转）需求——lag()只能取固定行数偏移位置的值，无法自动跳过中间的NULL值匹配最近的非空行。但通过窗口函数组合完全可以实现该需求，不需要递归或者自连接。

实现注意前提 #

SQL中表本身是无序集合，你需要先确定一个能明确标识行先后顺序的字段（比如实际业务里的事件发生时间、自增ID等），下文示例中ORDER BY 行排序字段的位置替换成该字段即可，不指定明确排序字段的话填充结果会随机出错。

通用兼容写法（支持所有支持窗口函数的数据库：MySQL8.0+、Hive、Spark SQL、旧版PostgreSQL、SQL Server等） #

实现逻辑分两步：

1. 对需要填充的价格列，通过累计计数给行打分组标记：每遇到一次非空价格值，分组ID+1，同个分组内的所有行都共享该分组开头的非空价格
2. 按分组ID分区，取每个分组内的价格值填充同组所有行，分组ID为0（即首次出现非空值之前的行）保留NULL即可

示例代码（假设你原有CTE名为your_cte）：

WITH group_mark AS (
    SELECT
        *,
        COUNT(US_PRICE) OVER(ORDER BY 行排序字段 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS us_gid,
        COUNT(NON_US_PRICE) OVER(ORDER BY 行排序字段 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS non_us_gid
    FROM your_cte
)
SELECT
    US_DATE_TIME,
    IF(us_gid=0, NULL, MAX(US_PRICE) OVER(PARTITION BY us_gid)) AS US_PRICE,
    NON_US_DATE_TIME,
    IF(non_us_gid=0, NULL, MAX(NON_US_PRICE) OVER(PARTITION BY non_us_gid)) AS NON_US_PRICE
FROM group_mark;

运行该代码得到的结果和你给出的预期结果完全一致。

简化写法（适用于支持IGNORE NULLS特性的数据库：PostgreSQL11+、SQL Server2022+、BigQuery、Databricks、Snowflake等） #

这类数据库直接用last_value窗口函数加IGNORE NULLS参数一步就能实现，不需要打分组标记：

SELECT
    US_DATE_TIME,
    LAST_VALUE(US_PRICE) IGNORE NULLS OVER(ORDER BY 行排序字段 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS US_PRICE,
    NON_US_DATE_TIME,
    LAST_VALUE(NON_US_PRICE) IGNORE NULLS OVER(ORDER BY 行排序字段 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NON_US_PRICE
FROM your_cte;

为什么不推荐嵌套lag()实现 #

如果用多层lag()嵌套判断，你需要提前预知字段最多连续出现多少个NULL值，比如连续2个NULL就要嵌套判断IFNULL(US_PRICE, LAG(US_PRICE,1), LAG(US_PRICE,2))，连续NULL行数不确定的场景下这种写法完全不可用，可维护性极差。

内容的提问来源于stack exchange，提问作者user18466310