Snowflake中基于表B条件的表A列Case When实现及变体问题

问题1：基于固定日期阈值关联两表并赋值House_ID #

示例数据 #

表A

表B

预期输出

解决方案SQL #

WITH b_valid_mark AS (
    SELECT 
        Market_ID,
        House_ID,
        -- 标记当前House_ID是否存在大于阈值的日期
        MAX(CASE WHEN Date > '2022-01-15' THEN 1 ELSE 0 END) AS has_valid_date
    FROM TableB
    GROUP BY Market_ID, House_ID
)
SELECT 
    a.Market_ID,
    a.House_ID,
    a.Revenue,
    CASE 
        -- 存在符合条件的日期则保留House_ID，否则设为NULL（对应需求中的NaN）
        WHEN bvm.has_valid_date = 1 THEN a.House_ID
        ELSE NULL
    END AS Result_House_ID
FROM TableA a
LEFT JOIN b_valid_mark bvm 
    ON a.Market_ID = bvm.Market_ID 
    AND a.House_ID = bvm.House_ID;

说明 #

1. 先通过CTE对表B按(Market_ID, House_ID)分组，标记每组是否存在大于阈值的日期
2. 用左连接关联表A和CTE结果，确保表A所有记录都被保留
3. 通过CASE WHEN判断标记值，完成House_ID的赋值逻辑

问题2：基于表A的Date_from与表B的Date关联赋值 #

示例数据 #

表A（新增Date_from列）

表B（同问题1）

预期输出

解决方案SQL #

方案1：使用EXISTS子查询（简洁高效）

SELECT 
    a.Market_ID,
    a.House_ID,
    a.Revenue,
    a.Date_from,
    CASE 
        -- 检查当前Market_ID下是否存在表B的Date大于表A的Date_from
        WHEN EXISTS (
            SELECT 1 
            FROM TableB b 
            WHERE b.Market_ID = a.Market_ID 
              AND b.Date > a.Date_from
        ) THEN a.House_ID
        ELSE NULL
    END AS Result_House_ID
FROM TableA a;

方案2：使用左连接+窗口函数

SELECT 
    DISTINCT a.Market_ID,
    a.House_ID,
    a.Revenue,
    a.Date_from,
    CASE 
        WHEN MAX(CASE WHEN b.Date > a.Date_from THEN 1 ELSE 0 END) 
             OVER (PARTITION BY a.Market_ID, a.House_ID) = 1 
        THEN a.House_ID
        ELSE NULL
    END AS Result_House_ID
FROM TableA a
LEFT JOIN TableB b 
    ON a.Market_ID = b.Market_ID;

说明 #

• 方案1通过EXISTS子查询直接判断当前表A记录对应的Market_ID下是否存在符合条件的日期，逻辑直观，性能较好
• 方案2通过左连接关联表B后，用窗口函数聚合判断是否存在符合条件的记录，适合需要同时获取表B其他字段的场景

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