Snowflake中使用FIRST_VALUE与FULL OUTER JOIN填充缺失行空值失效问题

根因分析 #

你的代码在测试环境生效属于巧合，生产环境失效存在4个核心疏漏：

• 窗口排序字段错误：窗口函数执行优先级高于SELECT别名定义，你在窗口中ORDER BY的是原表的ORDER_CREATED字段，缺失日期对应的行该字段为NULL，Snowflake默认NULL排序优先级高于有效值，导致窗口内排序逻辑完全错乱，FIRST_VALUE无法定位到前序有效行。
• 窗口函数选择错误：你使用的FIRST_VALUE会取整个窗口的首行值，无法满足“取紧邻当前缺失行的上一个有效非空值”的需求，测试数据只有一个起始值所以刚好符合预期，生产数据存在多次值变更时就会填充错误。
• 缺少分区逻辑：生产数据存在多个独立的SITE_ID + SUBSCRIPTION_ID订阅组合，未加PARTITION BY会导致不同订阅的数据混入同一个窗口，填充逻辑完全混乱。
• 关联逻辑不完善：直接用原表和日期表FULL JOIN，无法针对每个订阅生成独立的连续日期序列，多订阅场景下会出现大量无效空行。

修复方案 #

Snowflake支持窗口函数的IGNORE NULLS参数，可以直接跳过空值取最近的有效记录，修复后代码如下：

WITH sub_date_range AS (
    -- 生成每个订阅对应的完整连续日期序列
    SELECT 
        s.SITE_ID,
        s.SUBSCRIPTION_ID,
        dd.date_key AS ORDER_CREATED
    FROM (
        -- 提取所有独立订阅组合的时间范围
        SELECT 
            DISTINCT SITE_ID, SUBSCRIPTION_ID,
            MIN(ORDER_CREATED) OVER (PARTITION BY SITE_ID, SUBSCRIPTION_ID) AS min_dt,
            MAX(ORDER_CREATED) OVER (PARTITION BY SITE_ID, SUBSCRIPTION_ID) AS max_dt
        FROM prev_test
    ) s
    JOIN dim_date dd 
        ON dd.date_key BETWEEN s.min_dt AND s.max_dt
)
SELECT 
    sd.SITE_ID,
    sd.SUBSCRIPTION_ID,
    sd.ORDER_CREATED,
    LAST_VALUE(pt.ORDER_TYPE IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ORDER_TYPE,
    LAST_VALUE(pt.SUBSCRIPTION_STATUS IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS SUBSCRIPTION_STATUS,
    LAST_VALUE(pt.PERIOD_NORMALIZER IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS PERIOD_NORMALIZER,
    LAST_VALUE(pt.CHANGE_MRR_EVENT_TYPE IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CHANGE_MRR_EVENT_TYPE,
    LAST_VALUE(pt.TOTAL IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS TOTAL,
    LAST_VALUE(pt.DAILY_MRR IGNORE NULLS) OVER (
        PARTITION BY sd.SITE_ID, sd.SUBSCRIPTION_ID 
        ORDER BY sd.ORDER_CREATED 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS DAILY_MRR
FROM sub_date_range sd
LEFT JOIN prev_test pt 
    ON sd.SITE_ID = pt.SITE_ID 
    AND sd.SUBSCRIPTION_ID = pt.SUBSCRIPTION_ID 
    AND sd.ORDER_CREATED = pt.ORDER_CREATED
ORDER BY sd.SITE_ID, sd.SUBSCRIPTION_ID, sd.ORDER_CREATED

优化点说明 #

1. 先针对每个订阅生成专属的连续日期序列，避免不同订阅数据混淆，且排序字段不会出现NULL
2. 用LAST_VALUE + IGNORE NULLS自动跳过空值，取最近的上一条有效记录数据，完全符合填充需求
3. 新增PARTITION BY分区逻辑，每个订阅的填充逻辑独立，适配多订阅生产场景
4. 用LEFT JOIN代替FULL JOIN，过滤无效空行，结果更干净

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