使用LEFT JOIN和CASE WHEN实现类UPDATE逻辑时的重复记录问题

需要用minmax表的数据更新sorted表的Callsign字段，原本可以用UPDATE语句，但DBT不支持直接UPDATE，尝试用LEFT JOIN+CASE WHEN实现时遇到左连接导致记录数增多的问题。

源表数据 #

sorted表 #

minmax表 #

期望结果 #

核心逻辑 #

• sorted表与minmax表左连接，匹配条件：两表filename相同，且sorted的time_stamp处于minmax的min_timestamp与max_timestamp之间
• 匹配成功时用minmax的Callsign覆盖sorted的Callsign，sorted原有非空值保留
• 存在多匹配时取第一个匹配项（按min_timestamp排序）

当前SQL（存在记录数膨胀问题） #

SELECT
    s.time_stamp,
    CASE
        WHEN m.callsign IS NOT NULL THEN m.callsign
        ELSE s.callsign
    END AS Callsign,
    s.filename
FROM
    Sorted s
LEFT JOIN 
    (SELECT
         s.filename,
         s.time_stamp,
         m.callsign
     FROM
         Sorted s
     LEFT JOIN 
         Minmax m ON s.filename = m.filename
                  AND s.time_stamp BETWEEN m.min_timestamp AND m.max_timestamp
     QUALIFY ROW_NUMBER() OVER (PARTITION BY s.filename, s.time_stamp ORDER BY m.min_timestamp) = 1
    ) m ON s.filename = m.filename AND s.time_stamp = m.time_stamp;

优化方案 #

你当前的SQL嵌套了不必要的sorted表关联，导致逻辑冗余且容易引发记录数膨胀。可以简化为直接在主查询中用窗口函数筛选唯一匹配项，具体如下：

SELECT
    s.time_stamp,
    -- 优先用匹配到的minmax Callsign，无匹配则保留原字段值
    COALESCE(m.callsign, s.callsign) AS Callsign,
    s.filename
FROM
    Sorted s
LEFT JOIN 
    Minmax m 
    ON s.filename = m.filename
    AND s.time_stamp BETWEEN m.min_timestamp AND m.max_timestamp
-- 按sorted的唯一标识分区，取第一个匹配的minmax记录
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY s.time_stamp, s.filename 
    ORDER BY m.min_timestamp ASC
) = 1;

逻辑说明 #

1. 直接左连接：主查询中sorted和minmax直接按条件关联，避免嵌套查询的冗余关联
2. 窗口函数去重：用QUALIFY+ROW_NUMBER()按sorted的唯一键（time_stamp+filename）分区，按min_timestamp排序取第一条匹配记录，确保每条sorted记录只保留一个匹配项
3. COALESCE简化逻辑：替代CASE WHEN，直接取第一个非空值，代码更简洁

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