SQL JOIN关联后SUM聚合结果成倍放大的解决方法

数值成倍放大是多表关联时常见的笛卡尔积重复计算问题，出在你的JOIN逻辑上：

• 关联条件仅匹配两个表的周数，缺少其他维度的匹配约束，也没有做跨年判断
• traffic表同一周下有多少条明细记录，display表同周的每一条收入记录就会被复制多少份，后续SUM聚合时同一份收入会被重复累加，最终结果自然成倍偏离正确值。

举个最简单的例子：某周display表有1条金额为100的收入记录，traffic表同周有4条流量明细，JOIN后这1条记录会变成4条，每条的revenue值都是100，SUM后得到400，是真实值的4倍。
另外你当前用Week()函数做关联还有隐藏bug：该函数仅返回一年中的周序号，不同年份的同周数据也会被错误匹配。

不要直接关联两个表的明细数据再聚合，正确做法是先把两个表按最终需要的聚合粒度分别做子查询聚合，再对聚合后的结果做关联，从根源上避免多对多的行匹配。
参考写法如下：

SELECT 
  display_agg.date_granularity,
  display_agg.site,
  display_agg.ad_unit,
  display_agg.total_revenue
  -- 如需取traffic表的周度指标，可在此处引用traffic_agg里的聚合字段
FROM (
  -- 先按周+站点+广告单元粒度聚合display表数据
  SELECT 
    WEEK(display.date) AS date_granularity,
    -- 跨年场景建议替换为 YEARWEEK(display.date, 1) AS date_granularity，避免不同年份同周数据混淆
    display.site,
    display.ad_unit,
    SUM(display.revenue) AS total_revenue
  FROM `display`
  WHERE `display`.`date` BETWEEN '2022-01-01' AND '2022-12-15'
    AND `display`.`ad_unit` = 'IC1'
  GROUP BY date_granularity, `display`.`site`, `display`.`ad_unit`
) AS display_agg
INNER JOIN (
  -- 按和display完全一致的粒度聚合traffic表数据
  SELECT 
    WEEK(traffic.date) AS date_granularity,
    -- 如果上面用了YEARWEEK，此处也要同步替换，保证时间粒度计算规则完全一致
    traffic.site,
    traffic.ad_unit
    -- 此处可添加需要的traffic表聚合逻辑，例如 SUM(traffic.pv) AS total_pv
  FROM `traffic`
  -- 加相同时间范围过滤可减少数据扫描量，提升查询效率
  WHERE `traffic`.`date` BETWEEN '2022-01-01' AND '2022-12-15'
  GROUP BY date_granularity, `traffic`.`site`, `traffic`.`ad_unit`
) AS traffic_agg
  ON traffic_agg.date_granularity = display_agg.date_granularity
  -- 所有共有的维度字段都要加到关联条件中，避免数据错配
  AND traffic_agg.site = display_agg.site
  AND traffic_agg.ad_unit = display_agg.ad_unit

• 只要两个表在关联粒度下不是1对1的匹配关系，直接关联明细再聚合就一定会出现重复计算，这类聚合后关联的写法可以100%避免该问题
• 用YEARWEEK代替单独的WEEK做周维度关联是更严谨的做法，注意两个子查询里的周计算参数要保持一致，比如都用周一是一周第一天的规则
• 如果是用Laravel查询构建器生成语句，可以将两个子查询作为闭包传入join方法，框架会自动生成符合语法规范的SQL。

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