Snowflake中如何关联两个表排查日期缺失问题？

我需要解决Snowflake中的日期缺失问题：已基于特定ID创建了2017-2022年的工作日日历表（排除周末，包含所有预期日期），现在要通过关联这张日历表，找出另一张存在日期缺失的表中对应的缺失日期（即关联后显示NULL的行）。

示例数据 #

日历表（包含所有预期日期）

存在日期缺失的目标表

原查询问题分析 #

原查询的问题在于左连接后在WHERE子句中添加了c.id = 'id'和c.date between '2017-01-01' and '2022-12-31'的过滤条件，这会直接过滤掉左连接中日历表有数据但目标表无匹配的行（也就是我们要找的缺失日期行）——因为这些行的c.id和c.date都是NULL，无法满足WHERE子句的非空条件。

正确查询语句 #

SELECT 
    a.id, 
    a.date AS 预期工作日, 
    c.date AS 实际存在日期
FROM (
    SELECT DISTINCT id, date 
    FROM 日历表
    WHERE date BETWEEN '2017-01-01' AND '2022-12-31'
      AND id = 'ID'
) AS a
LEFT JOIN 目标表 c
    ON c.id = a.id 
    AND c.date = a.date 
WHERE c.date IS NULL
ORDER BY a.date;

关键调整说明 #

• 将原WHERE子句中针对目标表的过滤条件移到JOIN的ON子句中，确保左连接保留日历表的所有行，不会提前过滤掉无匹配的记录
• 通过c.date IS NULL精准筛选出日历表有记录但目标表无对应日期的行，这些就是缺失的日期
• 保留日历表的id和date字段，清晰展示预期的工作日和实际缺失的情况

内容的提问来源于stack exchange，提问作者H.Hernandez