Snowflake左关联两个CTE时REVENUE字段求和异常求解

直接修复当前问题 #

核心问题是左关联无匹配时，bh.REVENUE为NULL，导致s.REVENUE + bh.REVENUE结果为NULL。解决方法是用COALESCE函数将NULL值替换为0，确保即使右侧无匹配，也能保留左侧的REVENUE值：

with smartconnect_transactions as (
    select distinct
        dd.MONTHYEAR,
        date_trunc(month, tr.RECHARGEDATE) as MONTH,
        s.CREATE_DATE::date as ACTIVATION_DATE,
        s.TERMINATION_DATE::date as TERMINATION_DATE,
        s.ACCOUNT_NUMBER,
        sum(tr.COST) over (partition by s.ACCOUNT_NUMBER, date_trunc(month, tr.RECHARGEDATE)) as REVENUE,
        tr.MSISDN
    from UCONNECT_DW.ANALYTICS.VW_SC_TRANSACTION_REPORT as tr
    join UCONNECT_DW.ANALYTICS.DIM_SUBSCRIBERS as s
        on tr.MSISDN = s.PHONE_NUMBER
        and tr.RECHARGEDATE >= s.CREATE_DATE and tr.RECHARGEDATE < ifnull(s.TERMINATION_DATE, '2999-12-31')
    join UCONNECT_DW.ANALYTICS.DIM_DATE as dd
        on tr.RECHARGEDATE::date = dd.THEDATE
    where tr.RECHARGEDATE::date >= '2022-10-01'
    group by
        dd.MONTHYEAR,
        s.ACCOUNT_NUMBER,
        s.CREATE_DATE,
        s.TERMINATION_DATE,
        tr.RECHARGEDATE,
        tr.MSISDN,
        tr.COST
    order by MONTHYEAR, ACCOUNT_NUMBER),
smartconnect_billinghistory as (
    select distinct
        dd.MONTHYEAR,
        date_trunc(month, bh.ISSUEDDATE) as MONTH,
        s.CREATE_DATE::date as ACTIVATION_DATE,
        s.TERMINATION_DATE::date as TERMINATION_DATE,
        s.ACCOUNT_NUMBER,
        sum(bh.COLLECTIONAMOUNT) over (partition by s.ACCOUNT_NUMBER, date_trunc(month, bh.ISSUEDDATE)) as REVENUE,
        bh.MSISDN
    from UCONNECT_DW.ANALYTICS.VW_SC_SUBSCRIBER_BILLING_HISTORY as bh
    join UCONNECT_DW.ANALYTICS.DIM_SUBSCRIBERS as s
        on bh.MSISDN = s.PHONE_NUMBER
        and bh.ISSUEDDATE >= s.CREATE_DATE and bh.ISSUEDDATE < ifnull(s.TERMINATION_DATE, '2999-12-31')
    join UCONNECT_DW.ANALYTICS.DIM_DATE as dd
        on bh.ISSUEDDATE::date = dd.THEDATE
    where bh.ISSUEDDATE::date >= '2022-10-01'
    group by
        dd.MONTHYEAR,
        s.ACCOUNT_NUMBER,
        s.CREATE_DATE,
        s.TERMINATION_DATE,
        bh.ISSUEDDATE,
        bh.MSISDN,
        bh.COLLECTIONAMOUNT
    order by MONTHYEAR, ACCOUNT_NUMBER)
select distinct
    s.MONTHYEAR,
    s.ACTIVATION_DATE,
    s.TERMINATION_DATE,
    s.ACCOUNT_NUMBER,
    s.REVENUE + COALESCE(bh.REVENUE, 0) as REVENUE -- 用COALESCE将NULL替换为0
from smartconnect_transactions as s
left join smartconnect_billinghistory as bh
    on s.ACCOUNT_NUMBER = bh.ACCOUNT_NUMBER
    and s.MSISDN = bh.MSISDN
where s.REVENUE is not null;

优化CTE提升性能（可选） #

当前两个CTE存在冗余逻辑：使用窗口函数sum() over()计算分组总和后，又通过group by+distinct去重，会生成大量重复行，导致关联性能低下。建议直接用group by聚合数据，避免重复：

with smartconnect_transactions as (
    select
        dd.MONTHYEAR,
        date_trunc(month, tr.RECHARGEDATE) as MONTH,
        s.CREATE_DATE::date as ACTIVATION_DATE,
        s.TERMINATION_DATE::date as TERMINATION_DATE,
        s.ACCOUNT_NUMBER,
        sum(tr.COST) as REVENUE, -- 直接group by聚合，替代窗口函数
        tr.MSISDN
    from UCONNECT_DW.ANALYTICS.VW_SC_TRANSACTION_REPORT as tr
    join UCONNECT_DW.ANALYTICS.DIM_SUBSCRIBERS as s
        on tr.MSISDN = s.PHONE_NUMBER
        and tr.RECHARGEDATE >= s.CREATE_DATE and tr.RECHARGEDATE < ifnull(s.TERMINATION_DATE, '2999-12-31')
    join UCONNECT_DW.ANALYTICS.DIM_DATE as dd
        on tr.RECHARGEDATE::date = dd.THEDATE
    where tr.RECHARGEDATE::date >= '2022-10-01'
    group by
        dd.MONTHYEAR,
        date_trunc(month, tr.RECHARGEDATE),
        s.CREATE_DATE::date,
        s.TERMINATION_DATE::date,
        s.ACCOUNT_NUMBER,
        tr.MSISDN -- 调整分组维度，去掉冗余字段避免重复行
    order by MONTHYEAR, ACCOUNT_NUMBER),
smartconnect_billinghistory as (
    select
        dd.MONTHYEAR,
        date_trunc(month, bh.ISSUEDDATE) as MONTH,
        s.CREATE_DATE::date as ACTIVATION_DATE,
        s.TERMINATION_DATE::date as TERMINATION_DATE,
        s.ACCOUNT_NUMBER,
        sum(bh.COLLECTIONAMOUNT) as REVENUE, -- 直接group by聚合
        bh.MSISDN
    from UCONNECT_DW.ANALYTICS.VW_SC_SUBSCRIBER_BILLING_HISTORY as bh
    join UCONNECT_DW.ANALYTICS.DIM_SUBSCRIBERS as s
        on bh.MSISDN = s.PHONE_NUMBER
        and bh.ISSUEDDATE >= s.CREATE_DATE and bh.ISSUEDDATE < ifnull(s.TERMINATION_DATE, '2999-12-31')
    join UCONNECT_DW.ANALYTICS.DIM_DATE as dd
        on bh.ISSUEDDATE::date = dd.THEDATE
    where bh.ISSUEDDATE::date >= '2022-10-01'
    group by
        dd.MONTHYEAR,
        date_trunc(month, bh.ISSUEDDATE),
        s.CREATE_DATE::date,
        s.TERMINATION_DATE::date,
        s.ACCOUNT_NUMBER,
        bh.MSISDN -- 调整分组维度，去掉冗余字段
    order by MONTHYEAR, ACCOUNT_NUMBER)
select
    s.MONTHYEAR,
    s.ACTIVATION_DATE,
    s.TERMINATION_DATE,
    s.ACCOUNT_NUMBER,
    s.REVENUE + COALESCE(bh.REVENUE, 0) as REVENUE
from smartconnect_transactions as s
left join smartconnect_billinghistory as bh
    on s.ACCOUNT_NUMBER = bh.ACCOUNT_NUMBER
    and s.MSISDN = bh.MSISDN
    and s.MONTH = bh.MONTH -- 新增月份关联，避免跨月错误匹配
where s.REVENUE is not null;

优化点说明 #

1. 用group by直接聚合替代窗口函数+去重逻辑，减少重复行，大幅提升关联性能；
2. 调整分组维度，仅保留需要聚合的核心字段，去掉具体交易/账单日期等冗余字段；
3. 关联条件新增s.MONTH = bh.MONTH，确保同一账户、号码的当月交易与账单匹配，避免跨月错误关联。

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