左外连接和得到膨胀的结果

Question

以下查询为我的客户提供了每月经常性收入( MRR )：

with dims as ( 
select distinct subscription_id, country_name, product_name from revenue 
where site_id = '18XLsHIVSJg' and subscription_id is not null 
)
select to_date('2022-07-01') as occurred_date, 
count(distinct srm.subscription_id) as subscriptions, 
count(distinct srm.receiver_contact) as subscribers, 
sum(srm.baseline_mrr) as mrr_srm
from subscription_revenue_mart srm 
join dims d on d.subscription_id = srm.subscription_id 
where srm.site_id = '18XLsHIVSJg' 
-- MRR as of the day before ie June 30th
and to_date(srm.creation_date) < '2022-07-01' 
-- Counting the subscriptions active after July 1st
and ((srm.subscription_status = 'SUBL.A') or  
-- Counting the subscriptions canceled/deactivated after July 1st
(srm.subscription_status = 'SUBL.C' and (srm.deactivation_date >= '2022-07-01') or (srm.canceled_date >= '2022-07-01')) ) group by 1; 

我总共获得了5922.15美元的，但我需要从另一个表中添加数据，以获取客户在产品订阅中进行的升级/降级。使用与上面相同的方法，我可以这样查询我的“更改”表：

select subscription_id, sum(mrr_change_amount) mrr_change_amount,max(subscription_event_date) subscription_event_date from subscription_revenue_mart_change srmc
where site_id = '18XLsHIVSJg'
and to_date(srmc.creation_date) < '2022-07-01' 
and ((srmc.subscription_status = 'SUBL.A') 
or  (srmc.subscription_status = 'SUBL.C' and (srmc.deactivation_date >= '2022-07-01') or (srmc.canceled_date >= '2022-07-01')))
group by 1;

我总共得到了3635.47美元的

当我将这两个查询合并为一个时，就会得到一个膨胀的结果：

   with dims as ( 
    select distinct subscription_id, country_name, product_name from revenue 
    where site_id = '18XLsHIVSJg' and subscription_id is not null 
    ),
    change as (
    select subscription_id, sum(mrr_change_amount) mrr_change_amount,
-- there can be multiple changes per subscription
max(subscription_event_date) subscription_event_date from subscription_revenue_mart_change srmc
    where site_id = '18XLsHIVSJg'
    and to_date(srmc.creation_date) < '2022-07-01' 
    and ((srmc.subscription_status = 'SUBL.A') 
    or  (srmc.subscription_status = 'SUBL.C' and (srmc.deactivation_date >= '2022-07-01') or (srmc.canceled_date >= '2022-07-01')))
    group by 1
    )
    select to_date('2022-07-01') as occurred_date, 
    count(distinct srm.subscription_id) as subscriptions, 
    count(distinct srm.receiver_contact) as subscribers, 
    -- See comment RE:  LEFT OUTER join
    sum(coalesce(c.mrr_change_amount,srm.baseline_mrr)) as mrr 
    from subscription_revenue_mart srm 
    join dims d 
    on d.subscription_id = srm.subscription_id 
    -- LEFT OUTER join required for customers that never made a change
    left outer join change c 
    on srm.subscription_id = c.subscription_id
    where srm.site_id = '18XLsHIVSJg' 
    and to_date(srm.creation_date) < '2022-07-01' 
    and ((srm.subscription_status = 'SUBL.A') 
    or  (srm.subscription_status = 'SUBL.C' and (srm.deactivation_date >= '2022-07-01') or (srm.canceled_date >= '2022-07-01'))) group by 1;

应该是$9557.62 ie (5922.15 +3635.47美元)，但是查询输出的是$16116.91，这是错误的。

我想爆炸-内爆综合症可能会导致这种情况。

我设计了我的“更改”CTE，通过聚合所有相关的字段来防止这种情况，但是它没有工作。

有人能就解决这个问题的最佳方法提供建议吗？

Answer 1

如果你也给我们样本数据也会有帮助，但我在这里看到了一个问题：

    sum(coalesce(c.mrr_change_amount,srm.baseline_mrr)) as mrr 

为什么是COALESCE？这将给你两个数字中的一个，但我想你想要的是：

    sum(ifnull(c.mrr_change_amount, 0) + srm.baseline_mrr) as mrr 

你给我们的东西我只能提供这么多。