Snowflake左连接未按预期工作：日期对账问题排查

需求 #

对两张表进行日期对账，找出日历表存在但业务表中无对应记录的日期（即左连接后业务表日期列为NULL的记录）。

现有查询 #

1. 生成2017-2022年日期序列表（日历表） #

select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date

该表示例：

2. 业务表查询（提取日期及相关字段） #

select distinct date
from table 
where i.id = id

该表示例：

3. 错误的左连接查询 #

WITH calendar_table as (
select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date)

select distinct t.generated_date, i.date
from calendar_table t 
left join table i on t.date = i.date
where i.id = 'id'
order by t.generated_date desc

预期结果与实际结果 #

预期结果 #

实际结果 #

问题原因 #

• 连接字段不匹配：日历表的日期字段是generated_date，但查询中错误使用t.date进行连接，导致连接逻辑失效。
• Where条件过滤NULL记录：左连接后添加where i.id = 'id'，会直接过滤掉所有业务表无匹配（即i.id为NULL）的记录，违背了左连接保留左表所有记录的设计目的。

修复后的查询 #

方案1：将业务表过滤条件移至ON子句，修正连接字段 #

WITH calendar_table as (
select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date)

select t.generated_date, i.date
from calendar_table t 
left join table i on t.generated_date = i.date 
                 and i.id = 'id'  -- 将业务表过滤条件移至ON子句，避免过滤NULL记录
order by t.generated_date desc

方案2：先过滤业务表再执行左连接 #

WITH calendar_table as (
select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2017-01-01'::date start_date, '2022-12-31'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date),
filtered_business as (
select distinct date
from table 
where id = 'id'  -- 提前过滤业务表，避免连接后再过滤
)

select t.generated_date, fb.date
from calendar_table t 
left join filtered_business fb on t.generated_date = fb.date
order by t.generated_date desc

额外说明 #

如果只需筛选出业务表缺失的日期（即date为NULL的记录），可在修复后的查询末尾添加where fb.date is null（对应方案2）或where i.date is null（对应方案1）。

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