LEFT JOIN关联后SUM计算结果异常问题排查求助

问题根源 #

你遇到的是关联查询中常见的重复聚合问题：

• Query1里你通过SUM和GROUP BY把stock_tracker_max表中WK06 GABONA的多行数据合并成了一行，但原表中这个Stock_Tracker实际对应18条原始记录（78084 ÷ 4338 = 18）。
• 当你用未做聚合的a表直接关联已经聚合的b表时，b表中WK06 GABONA的那一行会被重复匹配a表的18条记录，最终主查询的SUM(b.Qty_Shipped)相当于把4338重复加了18次，得到异常值78084。

解决方案 #

有两种可靠的修复方式：

方案1：先聚合两张表，再关联

先分别对两张表按Stock_Tracker完成聚合，再执行关联操作，这样关联结果都是单行，不会出现重复计算：

SELECT 
    a.Stock_Tracker,
    b.Stock_Tracker,
    a.Qty_Invoiced,
    a.Qty_Received,
    b.Qty_Shipped
FROM (
    SELECT 
        Stock_Tracker,
        SUM(Qty_Invoiced) AS Qty_Invoiced,
        SUM(Qty_Received) AS Qty_Received
    FROM `strange-bird-366417.financial_data.stock_tracker_max`
    WHERE Stock_Tracker = 'WK06 GABONA'
    GROUP BY 1
) a 
LEFT JOIN (
    SELECT 
        Stock_Tracker,
        SUM(Qty_Shipped) AS Qty_Shipped
    FROM `strange-bird-366417.financial_data.tbl_shipment_data_final`
    WHERE Stock_Tracker = 'WK06 GABONA'
    GROUP BY 1
) b 
ON a.Stock_Tracker = b.Stock_Tracker

方案2：主查询中用MAX替代SUM计算b表字段

因为关联后b.Qty_Shipped的值是重复的，用MAX可以直接取到唯一的正确值，避免重复求和：

SELECT 
    a.Stock_Tracker,
    b.Stock_Tracker,
    SUM(a.Qty_Invoiced) AS Qty_Invoiced,
    SUM(a.Qty_Received) AS Qty_Received,
    MAX(b.Qty_Shipped) AS Qty_Shipped
FROM `strange-bird-366417.financial_data.stock_tracker_max` a 
LEFT JOIN (
    SELECT Stock_Tracker, SUM(Qty_Shipped) AS Qty_Shipped 
    FROM `strange-bird-366417.financial_data.tbl_shipment_data_final` 
    GROUP BY 1
) b 
ON a.Stock_Tracker = b.Stock_Tracker 
WHERE a.stock_tracker = 'WK06 GABONA'
GROUP BY 1,2

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