Snowflake SQL多表关联前过滤数据并保留全量匹配结果

需求说明 #

在Snowflake环境中生成202301月度费用报表，无数据写入权限。要求：

• 以ID为关联键，整合TABLE1、TABLE3、TABLE4中billing_month='202301'的费用数据
• TABLE2包含所有需统计的ID，需保留其中Rate_Program不属于('SFNF', 'SPSF', 'SF')的记录（即ID=1/2/3/4）
• 需包含ID=4的完整记录，即使该ID在部分费用表中无匹配数据

各表结构 #

TABLE1 #

TABLE2 #

TABLE3 #

TABLE4（唯一费用表） #

用户尝试的SQL #

SELECT * FROM TABLE1 t1 
LEFT JOIN TABLE2 t2
    ON t1.ID = t2.acct and t1.billing_month = '202301'
FULL JOIN TABLE3 t3
    ON t3.ID = t2.acct and t3.billing_month = '202301'
FULL JOIN TABLE4 t4
    ON t4.ID = t2.acct and t4.billing_month = '202301'
    where t1.MCC not in ('4444','5555')
        and t2.rate_program not in ('SFNF', 'SPSF', 'SF')         

当前实际输出 #

期望输出 #

问题分析与解决方案 #

问题根源 #

1. 原查询以TABLE1为起点，ID=4不在TABLE1中，导致左连TABLE2时无法匹配到该ID
2. WHERE条件中的t1.MCC not in ('4444','5555')会过滤掉所有t1为NULL的记录（即ID=4的情况），因为NULL值无法参与IN/NOT IN的逻辑判断

解决思路 #

以TABLE2作为主表（因为它包含所有需要的ID），先筛选出符合Rate_Program条件的记录，再分别左连TABLE1、TABLE3、TABLE4中已过滤出202301月份数据的子集，确保所有目标ID都被保留。

最终SQL #

SELECT
    t2.ACCT AS ID,
    COALESCE(t1.Billing_month, t3.Billing_month, t4.Billing_month) AS Billing_month,
    t1.A_FEE,
    t3.B_FEE,
    t4.C_FEE,
    COALESCE(t1.MCC, t3.MCC, t4.MCC) AS MCC,
    t2.Rate_Program
FROM TABLE2 t2
LEFT JOIN (SELECT * FROM TABLE1 WHERE Billing_month = '202301') t1
    ON t2.ACCT = t1.ID
LEFT JOIN (SELECT * FROM TABLE3 WHERE Billing_month = '202301') t3
    ON t2.ACCT = t3.ID
LEFT JOIN (SELECT * FROM TABLE4 WHERE Billing_month = '202301') t4
    ON t2.ACCT = t4.ID
WHERE
    t2.Rate_Program NOT IN ('SFNF', 'SPSF', 'SF')
    AND (t1.ID IS NOT NULL OR t3.ID IS NOT NULL OR t4.ID IS NOT NULL) -- 可选，过滤无任何费用数据的ID

代码说明 #

1. 先对每个费用表做子查询，过滤出Billing_month='202301'的数据，避免在JOIN条件中重复筛选
2. 以TABLE2为基础，左连三个费用表的过滤后子集，确保所有符合Rate_Program条件的ID都被保留
3. 使用COALESCE函数统一获取Billing_month和MCC的值，优先取存在数据的表中的值
4. 最后WHERE条件仅过滤TABLE2的Rate_Program，避免过滤掉无费用数据的目标ID（如ID=4）

执行该SQL后，将得到符合期望的输出结果。

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