关联两表产生笛卡尔积导致SUM函数重复计数问题求助

问题背景 #

• Tracking Information表：与销售订单为1-N关系，一个销售订单ID（SOTranId）对应多个运单编号（TrackingNbr）
• Sales Order Information表：存储销售订单的产品及订购数量

表结构 #

Tracking Information表 #

Sales Order Information表 #

现有查询语句 #

SELECT  SUM(t.netamtdue) as freightcost,count(distinct t.TrackingNbr) as number_of_shipments, DATEPART(year,ShipDate) as Year1, COUNT(DISTINCT t.ORDERID) as number_of_orders, DATEPART(week, t.ShipDate) as week_shipped, SUM(s.number_of_items) as number_of_items
  FROM [Tracking] t
  
 
 LEFT JOIN (
        Select [Tran ID], Sum([Item Count]) as number_of_items
        FROM [Sales Order Information] 
        GROUP BY [Tran ID]) s
        ON s.[Tran ID] = t.SOTranID

  
  GROUP BY  DATEPART(year,ShipDate), DATEPART(week, t.ShipDate)

问题现象 #

手动统计预期结果：运费总额95.5美元，运单数量12，销售订单数量1，商品数量24；但实际查询得到商品数量为288，其余结果正确。原因是笛卡尔积导致每个订单的商品数量被重复计算（订单1对应12个运单，24×12=288）。

解决方案 #

方案一：先聚合Tracking表再关联订单数据 #

先对Tracking表按订单ID和日期维度聚合，避免关联时产生笛卡尔积，再与已聚合的订单商品数据关联，最后按日期汇总：

WITH TrackingAgg AS (
    SELECT 
        DATEPART(year, ShipDate) AS Year1,
        DATEPART(week, ShipDate) AS week_shipped,
        SUM(NetAmtDue) AS freightcost,
        COUNT(DISTINCT TrackingNbr) AS number_of_shipments,
        SOTranId
    FROM [Tracking]
    GROUP BY DATEPART(year, ShipDate), DATEPART(week, ShipDate), SOTranId
),
OrderItemAgg AS (
    SELECT 
        [Tran ID],
        SUM([Item Count]) AS number_of_items
    FROM [Sales Order Information]
    GROUP BY [Tran ID]
)
SELECT 
    ta.Year1,
    ta.week_shipped,
    SUM(ta.freightcost) AS freightcost,
    SUM(ta.number_of_shipments) AS number_of_shipments,
    COUNT(DISTINCT ta.SOTranId) AS number_of_orders,
    SUM(oi.number_of_items) AS number_of_items
FROM TrackingAgg ta
LEFT JOIN OrderItemAgg oi ON ta.SOTranId = oi.[Tran ID]
GROUP BY ta.Year1, ta.week_shipped;

方案二：在原查询中修正商品数量计算 #

利用AVG获取单个订单的商品数，再乘以订单数量，避免重复计数：

SELECT  
    SUM(t.netamtdue) AS freightcost,
    COUNT(DISTINCT t.TrackingNbr) AS number_of_shipments,
    DATEPART(year, ShipDate) AS Year1,
    COUNT(DISTINCT t.SOTranId) AS number_of_orders,
    DATEPART(week, t.ShipDate) AS week_shipped,
    AVG(s.number_of_items) * COUNT(DISTINCT t.SOTranId) AS number_of_items
FROM [Tracking] t
LEFT JOIN (
    SELECT [Tran ID], SUM([Item Count]) AS number_of_items
    FROM [Sales Order Information] 
    GROUP BY [Tran ID]
) s ON s.[Tran ID] = t.SOTranID
GROUP BY DATEPART(year, ShipDate), DATEPART(week, t.ShipDate);

两种方案都能解决重复计数问题，方案一逻辑更清晰，适合复杂场景；方案二更简洁，适合简单场景。

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