sql - 连接表会膨胀数值

Question

这是我的查询，我有不同的帐户值（accValByProd表），我想按他们的产品类型添加到我的表中。如果我只是单独选择accValByProd表格，则数字是准确的，但是当我将其与计费表连接时，数字会变得比应有的大得多。

正如您所看到的，因为我需要在计费表中获取 MAX 值，然后在最终计算中对它们求和，所以我不能只在最终的 SELECT 中这样做（除非有办法做到这一点？）。感谢任何帮助我不太确定我做错了什么。

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 
                -- LESS than same day following year includes any 
                -- possible time portion if so part of.
                bh.period_start_date >= '2020-07-01'
            and bh.period_end_date < '2021-07-01'
            and bh.a_fee != 0
), 
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 
            -- which table alias, should never leave blind
            -- assumption here is "Account" table (alias a)
            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 DISTINCT 
        ad.ad_id, 
        ad.fname + ' ' + ad.middle + ' ' + ad.lname 'full name',
        accValByProd.prod_type AS 'product type', 
        COUNT(DISTINCT billing.debit_account_id) AS 'number of accounts', 
        SUM(accValByProd.acc_val) AS 'product aum',
        SUM(CASE WHEN DATEDIFF(DAY, billing.period_start_date, billing.period_end_date) > 31 
                THEN billing.platform_fee ELSE 0 END) AS 'fees'
    FROM 
        advisor ad
            JOIN billing 
                ON ad.a_id = billing.a_id
                JOIN accounts 
                    ON billing.debit_account_id = accounts.account_id
                JOIN accValByProd 
                    ON billing.debit_account_id = accValByProd.deb_id
    WHERE 
            1=1 
        and (   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

这是 a_id 6835 的预期结果（加上其他信息）

a_id  product aum   product type
----  ------------  -------------
6835  7861895.23    APM
6835  47059722.64   FSP
6835  9816992.32    UMA
6835  528930.47     Unknown

以下是实际结果：

a_id  product aum   product type
----  ------------  -------------
6835  45447953.20   APM 
6835  203942000.07  FSP
6835  77678383.30   UMA
6835  1706276.86    Unknown

标签： sql