MySQL多次连接同一张表并分组时，如何正确统计多时段聚合值？

嗨，这个问题我之前做电商数据统计时也踩过坑！核心问题出在多次左同一张表时产生的笛卡尔积——当你同时连接lastDay、lastMonth和lastYear这三个子数据集时，它们会互相交叉匹配，导致单条统计记录被重复计算N次，最终的SUM结果自然就会虚高。

举个简单例子：如果某商品在lastDay有1440条记录（每分钟1条），lastMonth有43200条记录，连接后就会产生1440*43200条交叉记录，求和时每条unitsSold都会被重复计算成百上千次，数值能对才怪。

下面给你两种实用的解决方案：

方案一：子查询预聚合后再连接 #

先对每个时段单独做聚合统计，得到每个商品对应时段的总和，再和主表连接。这样每个子查询的结果都是按productId聚合后的单条记录，完全避免笛卡尔积：

SELECT 
    p.id, 
    p.name,
    -- 用COALESCE把NULL转为0，避免无数据商品显示空值
    COALESCE(ld.latDayUnitsSold, 0) AS latDayUnitsSold,
    COALESCE(lm.latMonthUnitsSold, 0) AS latMonthUnitsSold,
    COALESCE(ly.latYearUnitsSold, 0) AS latYearUnitsSold
FROM `products` p
LEFT JOIN (
    SELECT productId, SUM(unitsSold) AS latDayUnitsSold
    FROM `productStats`
    WHERE `time` BETWEEN '2018-05-27 00:00:00' AND '2018-05-28 00:00:00'
    GROUP BY productId
) ld ON p.id = ld.productId
LEFT JOIN (
    SELECT productId, SUM(unitsSold) AS latMonthUnitsSold
    FROM `productStats`
    WHERE `time` BETWEEN '2018-04-28 00:00:00' AND '2018-05-28 00:00:00'
    GROUP BY productId
) lm ON p.id = lm.productId
LEFT JOIN (
    SELECT productId, SUM(unitsSold) AS latYearUnitsSold
    FROM `productStats`
    WHERE `time` BETWEEN '2017-05-28 00:00:00' AND '2018-05-28 00:00:00'
    GROUP BY productId
) ly ON p.id = ly.productId;

方案二：条件聚合（推荐，性能更优） #

只连接一次productStats表，用CASE WHEN在SUM里筛选对应时段的数据，这种方法只需要扫描一次统计表格，性能比多次连接好很多：

SELECT 
    p.id, 
    p.name,
    SUM(CASE 
        WHEN ps.`time` BETWEEN '2018-05-27 00:00:00' AND '2018-05-28 00:00:00' 
        THEN ps.unitsSold ELSE 0 
    END) AS latDayUnitsSold,
    SUM(CASE 
        WHEN ps.`time` BETWEEN '2018-04-28 00:00:00' AND '2018-05-28 00:00:00' 
        THEN ps.unitsSold ELSE 0 
    END) AS latMonthUnitsSold,
    SUM(CASE 
        WHEN ps.`time` BETWEEN '2017-05-28 00:00:00' AND '2018-05-28 00:00:00' 
        THEN ps.unitsSold ELSE 0 
    END) AS latYearUnitsSold
FROM `products` p
LEFT JOIN `productStats` ps ON p.id = ps.productId
GROUP BY p.id, p.name;

另外还要提一句：你原来的SQL里有个低级笔误——lastMonth和lastYear的连接条件里用了lastDay.time，应该改成lastMonth.time和lastYear.time，不过即使修正了这个，笛卡尔积的问题依然存在，还是上面两种方案靠谱。

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