如何在Snowflake SQL中用10天回溯窗口内的最近记录填充空值

问题描述 #

需要填充activities表中offer_id的空值，规则是取同一member_id下，当前记录日期10天回溯窗口内的最近非空offer_id；如果窗口内没有非空值，则保留NULL。现有查询会生成重复结果，无法精准获取符合条件的最近记录。

示例表结构与数据 #

CREATE TABLE activities
(
    activity_id       NUMBER,
    activity_datetime DATE,
    offer_id          VARCHAR,
    member_id         NUMBER
);

INSERT INTO activities (activity_id, activity_datetime, offer_id, member_id)
VALUES (1, '2022-10-01', '1111', 10001)
     , (2, '2022-10-05', '5555', 10001)
     , (3, '2022-10-09', NULL, 10001)
     , (4, '2022-10-09', NULL, 10001)
     , (5, '2022-10-13', NULL, 10001)
     , (6, '2022-10-13', NULL, 10001)
     , (7, '2022-10-17', '18887', 10001)
     , (8, '2022-10-21', '23331', 10001)
     , (9, '2022-10-25', '27775', 10001)
     , (10, '2022-10-29', '32219', 10001)
     , (11, '2022-10-01', '1111', 20001)
     , (12, '2022-10-05', '5555', 20001)
     , (13, '2022-10-09', NULL, 20001)
     , (14, '2022-10-09', NULL, 20001)
     , (15, '2022-10-13', NULL, 20001)
     , (16, '2022-10-13', NULL, 20001)
     , (17, '2022-10-17', '18887', 20001)
     , (18, '2022-10-21', '23331', 20001)
     , (19, '2022-10-25', '27775', 20001)
     , (20, '2022-10-29', '32219', 20001);

当前数据 #

期望结果 #

现有查询问题 #

原查询通过拆分空值和非空表再关联，会返回多条匹配结果，无法筛选出10天窗口内的最近记录：

WITH activity_nulls AS (SELECT *
                   FROM activities
                   WHERE offer_id IS NULL)
   , activity_non_null AS (SELECT *
                  FROM activities
                  WHERE offer_id IS NOT NULL)
SELECT activity_nulls.activity_id       actvity_id_nulls
     , activity_nulls.activity_datetime dt_nulls
     , activity_non_null.offer_id
     , activity_non_null.activity_datetime
FROM activity_nulls
         INNER JOIN activity_non_null
                    ON activity_non_null.member_id = activity_nulls.member_id
WHERE activity_non_null.activity_datetime BETWEEN DATEADD(DAY, -14, activity_non_null.activity_datetime)
          AND activity_non_null.activity_datetime;

解决方案 #

使用Snowflake的LAST_VALUE窗口函数，结合日期范围限制实现需求：

SELECT 
    activity_id,
    activity_datetime,
    -- 仅当offer_id为空时，取10天窗口内最近的非空值；否则保留原值
    CASE 
        WHEN offer_id IS NULL THEN
            LAST_VALUE(offer_id IGNORE NULLS) OVER (
                PARTITION BY member_id
                ORDER BY activity_datetime
                RANGE BETWEEN INTERVAL '10 days' PRECEDING AND CURRENT ROW
            )
        ELSE offer_id
    END AS offer_id,
    member_id
FROM activities
ORDER BY member_id, activity_id;

方案说明 #

1. LAST_VALUE(offer_id IGNORE NULLS)：忽略窗口内的NULL值，提取最后一个非空的offer_id，即当前记录之前的最近非空值。
2. PARTITION BY member_id：确保仅在同一会员的记录范围内查找匹配值。
3. RANGE BETWEEN INTERVAL '10 days' PRECEDING AND CURRENT ROW：精准限制窗口范围为当前记录日期前10天到当前记录的所有行，符合回溯窗口要求。
4. CASE语句：仅对原offer_id为空的行进行填充，非空记录保持原值不变。

该方案不会产生重复结果，完全匹配预期输出，同时高效处理数据。

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