Snowflake SQL比较克隆x原始计数

发布于04月10日

我试图比较2个表(克隆表x原始表)使用完全外部连接snowflake，但没有得到我期望的结果:

select 
    'invoice' as TABLE_NAME, 
    to_date(prd.date) as TRANSACTION_DATE,
    count(prd.date) as PROD_COUNT,
  from invoice_latest prd
full outer join
(
    select 
    'invoice_clone' as TABLE_NAME, 
    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)
    order by to_date(date)
) cln
on to_date(prd.date) = cln.TRANSACTION_DATE
where to_date(prd.date) >= '2024-04-02' OR cln.TRANSACTION_DATE is not null
group by to_date(prd.date)
order by to_date(prd.date)

我希望4月1日与原始表相关的计数为NULL，但得到以下结果:

推荐答案

问题是，您正在进行连接，然后过滤连接的结果，然后计数.

你应该先过滤，然后再执行连接.

一个类似于你的查询，给出了类似的坏结果:

select 'invoice' as table_name
    , o_orderdate as transaction_date
    , count(prd.o_orderdate) as prod_count
    , max(clone_count)
from snowflake_sample_data.tpch_sf1.orders prd
full outer join (
    select 'invoice_clone' as table_name
        , o_orderdate as transaction_date
        , count(1) as clone_count
        from snowflake_sample_data.tpch_sf1.orders 
        where o_orderdate >= '1998-07-29'
        group by o_orderdate
        order by o_orderdate        
) cln
on prd.o_orderdate = cln.transaction_date
where o_orderdate >= '1998-07-30' or cln.transaction_date is not null
group by prd.o_orderdate
order by 2

查询已修复:

select 'invoice' as table_name
    , transaction_date
    , count(prd.o_orderdate) as prod_count
    , max(clone_count)
from (
    select *
    from snowflake_sample_data.tpch_sf1.orders 
    where o_orderdate >= '1998-07-30'
) prd
full outer join (
    select 'invoice_clone' as table_name
        , o_orderdate as transaction_date
        , count(1) as clone_count
        from snowflake_sample_data.tpch_sf1.orders 
        where o_orderdate >= '1998-07-29'
        group by o_orderdate
        order by o_orderdate        
) cln
on prd.o_orderdate = cln.transaction_date
where cln.transaction_date is not null
group by transaction_date
order by 2