MySQL JOIN后出现多余行：按周聚合网站数据异常排查

问题原因 #

1. 连接条件不完整：子查询tr已经按site和周维度聚合了pageviews，但主查询的INNER JOIN只关联了周维度（tr.date_granularity = Week(display.date)），没有关联site字段。这会导致display表中Google的17周数据，和tr里所有site的17周数据做笛卡尔积连接，从而出现多条不同pageviews的行。
2. GROUP BY字段错误：主查询的GROUP BY里包含了tr.pageviews，而tr.pageviews是子查询聚合后的不同值，这会强制把每个不同的pageviews拆成独立分组，即使revenue值相同，也会生成多行结果。

解决方法 #

修正方案一：补全连接条件并调整GROUP BY #

SELECT 
    display.site,
    Week(display.date) AS dateweek,
    Sum(display.revenue) AS revenue,
    tr.pageviews
FROM display
INNER JOIN (
    SELECT 
        traffic.site,
        Week(date) AS date_granularity,
        Sum(pageviews) AS pageviews
    FROM traffic
    GROUP BY traffic.site, date_granularity
) AS tr
    ON tr.date_granularity = Week(display.date)
    AND tr.site = display.site -- 补全site关联条件
WHERE display.site = 'Google'
GROUP BY dateweek, display.site -- 移除tr.pageviews，tr已是聚合后单值

修正方案二：先分别聚合再连接（更高效） #

先对两张表各自按site和周聚合，再进行连接，避免主查询二次聚合：

SELECT 
    d.site,
    d.dateweek,
    d.revenue,
    t.pageviews
FROM (
    SELECT 
        site,
        Week(date) AS dateweek,
        Sum(revenue) AS revenue
    FROM display
    WHERE site = 'Google'
    GROUP BY site, dateweek
) d
INNER JOIN (
    SELECT 
        site,
        Week(date) AS dateweek,
        Sum(pageviews) AS pageviews
    FROM traffic
    WHERE site = 'Google'
    GROUP BY site, dateweek
) t
    ON d.site = t.site
    AND d.dateweek = t.dateweek

修正后结果 #

执行上述任意SQL，都会得到预期的唯一行：

+--------+----------+--------------------+-----------+
|  site  | dateweek |      revenue       | pageviews |
+--------+----------+--------------------+-----------+
| Google |       17 | 1424.0631110000002 |   1169364 |
+--------+----------+--------------------+-----------+

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