Snowflake单列日期比对：判断用户LOCATION2/3到访是否在LOCATION1到访4周内

实现逻辑 #

• 第一步：统一将字符串格式的到访日期转换为Snowflake日期类型，避免日期间隔计算错误
• 第二步：通过窗口函数为每个Person Id关联其唯一的LOCATION1到访日期
• 第三步：计算LOCATION2/LOCATION3到访日期与LOCATION1到访日期的间隔，判断是否落在±4周（28天）范围内
• 第四步：按Person Id聚合，只要存在1条符合要求的记录即标记为Y，否则标记为N

完整代码 #

WITH visit_with_date AS (
    -- 转换日期格式，适配源数据DD/MM/YYYY的格式
    SELECT 
        "Person Id" AS person_id,
        LOCATION,
        TO_DATE(VISIT_DATE, 'DD/MM/YYYY') AS visit_date
    FROM VISIT_RECORDS -- 替换为你的实际表名
),
person_loc1_date AS (
    -- 为每个用户挂载对应的LOCATION1到访日期
    SELECT 
        *,
        MAX(CASE WHEN LOCATION = 'LOCATION1' THEN visit_date END) OVER (PARTITION BY person_id) AS loc1_visit_date
    FROM visit_with_date
),
match_check AS (
    SELECT 
        person_id,
        -- 存在至少1条符合条件的LOCATION2/3记录则标记Y，否则标记N
        CASE WHEN MAX(CASE 
            WHEN LOCATION IN ('LOCATION2','LOCATION3') 
                AND ABS(DATEDIFF('day', loc1_visit_date, visit_date)) <= 28 
            THEN 1 ELSE 0 END) = 1 THEN 'Y' ELSE 'N' END AS match_flag
    FROM person_loc1_date
    GROUP BY person_id
)
SELECT * FROM match_check;

代码说明 #

• 日期转换逻辑可根据你实际的日期存储格式调整TO_DATE的第二个参数
• 间隔计算选择按天判断±28天，比按周计算精度更高，可避免周边界划分导致的误差
• 若需要保留原表明细同时标记用户，可去掉最后的聚合步骤，直接在person_loc1_date层新增标志字段即可

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