Snowflake克隆表与原表全外连接计数对比结果不符问题

你的SQL存在两个核心问题，导致无法得到2024-04-01对应PROD_COUNT为NULL的预期结果：

1. 分组字段逻辑错误：你用原表的to_date(prd.date)作为分组字段，但Full Outer Join后，当克隆表有2024-04-01的数据时，原表无对应日期数据，prd.date为NULL，分组后这行的TRANSACTION_DATE会显示为NULL，而非2024-04-01。
2. WHERE条件过滤逻辑错误：to_date(prd.date) >= '2024-04-02'直接过滤掉了原表中2024-04-01的数据，且JOIN后再聚合的方式，无法正确关联克隆表的日期与原表的NULL计数。

修正后的SQL #

WITH prod_agg AS (
    SELECT
        TO_DATE(date) AS TRANSACTION_DATE,
        COUNT(date) AS PROD_COUNT
    FROM invoice_latest
    WHERE TO_DATE(date) >= '2024-04-01'
    GROUP BY TO_DATE(date)
),
clone_agg AS (
    SELECT
        TO_DATE(date) AS TRANSACTION_DATE,
        COUNT(1) AS CLONE_COUNT
    FROM invoice_latest_clone
    WHERE TO_DATE(date) >= '2024-04-01'
    GROUP BY TO_DATE(date)
)
SELECT
    'invoice' AS TABLE_NAME,
    COALESCE(p.TRANSACTION_DATE, c.TRANSACTION_DATE) AS TRANSACTION_DATE,
    p.PROD_COUNT,
    c.CLONE_COUNT
FROM prod_agg p
FULL OUTER JOIN clone_agg c
    ON p.TRANSACTION_DATE = c.TRANSACTION_DATE
ORDER BY TRANSACTION_DATE;

关键说明 #

• 先分别对原表和克隆表按日期聚合，避免JOIN后数据膨胀，同时确保每个日期的计数准确。
• 用COALESCE(p.TRANSACTION_DATE, c.TRANSACTION_DATE)统一日期字段，无论哪张表有该日期数据，都能正确显示日期值。
• 原表中不存在的日期（比如2024-04-01），PROD_COUNT会自然显示为NULL，完全符合预期。

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