SQL计算如何消除冗余值？多表关联统计营收重复计数问题处理

问题根源 #

你遇到的是SQL多表关联中非常典型的一对多关联导致主表字段重复聚合问题：发票表（invoices）和发票明细表（invoice_items）是1对N的关系，直接JOIN后，1张发票会生成N行数据，对应N条明细，此时直接对发票的subtotal/credit/tax字段求和，就会把单张发票的数值重复计算N次，最终结果虚高。

解决方案 #

核心思路是先按发票维度聚合明细数据，算出每张发票的总退款额，再关联发票表、客户表做聚合，保证每张发票仅参与一次计算：

WITH invoice_refund_summary AS (
    -- 第一步：先统计每张发票的总退款金额，单张发票仅返回一条结果
    SELECT 
        invoices_id,
        SUM(refunded_amount) AS total_refund
    FROM invoice_items
    GROUP BY invoices_id
)
SELECT
    c.state AS "State",
    ROUND(SUM(i.subtotal - i.credit)::NUMERIC, 2) AS "Sales",
    ROUND(SUM(i.tax)::NUMERIC, 2) AS "Tax",
    ROUND(SUM(i.subtotal - i.credit + i.tax - irs.total_refund)::NUMERIC, 2) AS "Gross"
FROM invoices i
JOIN customer c ON i.customer_id = c.id
-- 关联聚合后的退款表，单张发票仅匹配一条，不会出现重复行
JOIN invoice_refund_summary irs ON i.id = irs.invoices_id
WHERE
    i.status = 'Paid'
    AND i.datepaid BETWEEN '2016-01-01' AND '2016-02-01'
    AND c.billing_day <> 0
    AND c.register_date < '2016-02-01'
    AND c.account_exempt = 'f'
    AND c.country = 'US'
    AND c.state = 'AK'
GROUP BY c.state;

结果验证 #

按照你的示例数据计算：

• 发票27111851：Sales贡献100、Tax贡献20、净营收贡献100+20-15=105
• 发票27111852：Sales贡献-1、Tax贡献0、净营收贡献-1
• 发票27111853：Sales贡献200、Tax贡献40、净营收贡献240
  合计结果为Sales=299、Tax=60、Gross=344，和预期完全一致。

如果使用的数据库不支持CTE语法，把invoice_refund_summary的逻辑改成子查询放在JOIN子句中即可，逻辑完全相同。

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