SQL关联billing表后accValByProd账户价值数值异常膨胀求助

数值偏高是关联时一对多关系导致的重复聚合：

• billing CTE 取的是2020年7月1日到2021年6月30日一整年的账单明细，同一个账户（debit_account_id）在这段时间内会有多条账单记录
• accValByProd CTE 是按账户+产品类型聚合的结果，同一个账户+产品类型只有1条记录
• 两个表通过debit_account_id关联时，1条accValByProd记录会匹配到N条billing明细，acc_val就会被重复计算N次，最终SUM(acc_val)的结果等于预期值乘以重复次数，所以远高于预期。

另外你最终查询中同时写了DISTINCT和GROUP BY属于冗余逻辑，GROUP BY执行后本身就不会返回重复行，可以直接删掉DISTINCT。

推荐先把billing表的费用指标提前聚合到账户维度，再和accValByProd关联，避免重复放大账户价值，修改后的SQL如下：

WITH billing AS
(   SELECT 
            bh.debit_account_id,
            bh.period_start_date,
            bh.period_end_date,
            bh.a_id,
            bh.account_value,
            bh.platform_fee
        FROM 
            billing_history bh
        WHERE 
                bh.period_start_date >= '2020-07-01'
            and bh.period_end_date < '2021-07-01'
            and bh.a_fee != 0
),
-- 新增：提前聚合billing到账户+advisor维度，计算好费用
billing_agg AS (
    SELECT
        debit_account_id,
        a_id,
        SUM(CASE WHEN DATEDIFF(DAY, period_start_date, period_end_date) > 31 
                THEN platform_fee ELSE 0 END) AS fees
    FROM billing
    GROUP BY debit_account_id, a_id
),
acc_prods AS
(
SELECT 
        a.account_id,
        a.product_id,
        CASE WHEN p.product_type = 3 THEN 'FSP'
             WHEN p.product_type = 6 THEN 'APM'
             WHEN p.product_type = 13 THEN 'UMA'                                                                         
             ELSE 'Unknown' END product_type,
        a.a_id
    FROM 
        account a
            LEFT JOIN product p
                ON a.product_id = p.product_id
    WHERE 
            a.close_date IS NULL 
        OR  a.close_date >= GETDATE()
), 
accounts AS (                                                    
SELECT 
        a.account_id,
        a.a_id,
        a.customer_id,
        a.close_date
    FROM 
        account a
), 
accValByProd as 
(
SELECT 
        bh.debit_account_id AS deb_id,
        bh.period_end_date,
        MAX(bh.account_value) AS acc_val,
        bh.a_id,
        ISNULL(ap.product_type, 'Unknown') AS prod_type                                     
    FROM 
        billing_history bh
            LEFT JOIN acc_prods ap
                ON bh.debit_account_id = ap.account_id
    WHERE
        bh.period_end_date = '2021-06-30'
    GROUP BY 
        bh.debit_account_id, 
        bh.period_end_date, 
        bh.a_id, 
        ap.product_type
)
                                                    
SELECT 
        ad.ad_id, 
        ad.fname + ' ' + ad.middle + ' ' + ad.lname 'full name',
        accValByProd.prod_type AS 'product type', 
        COUNT(DISTINCT accValByProd.deb_id) AS 'number of accounts', 
        SUM(accValByProd.acc_val) AS 'product aum',
        SUM(billing_agg.fees) AS 'fees'
    FROM 
        advisor ad
            JOIN billing_agg 
                ON ad.a_id = billing_agg.a_id
                JOIN accounts 
                    ON billing_agg.debit_account_id = accounts.account_id
                JOIN accValByProd 
                    ON billing_agg.debit_account_id = accValByProd.deb_id
    WHERE 
            (ad.termination_date IS NULL OR ad.termination_date >= GETDATE()) 
        AND (accounts.close_date IS NULL OR accounts.close_date >= '2021-06-30')
    GROUP BY 
        ad.a_id, 
        ad.fname,
        ad.middle,
        ad.lname, 
        accValByProd.prod_type

临时验证方案 #

如果只是快速验证问题，可以把原查询中SUM(accValByProd.acc_val)替换为SUM(DISTINCT accValByProd.acc_val)，但该方案存在局限性：如果两个不同账户的账户价值刚好相等，会被去重导致结果偏低，仅适合临时排查使用。

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