使用JOIN结合Sum()函数查询结果异常的问题排查与修正

直接关联两张未聚合的表会产生笛卡尔积：假设某用户在Daily_steps表中有n条记录，在Daily_calories表中有m条记录，关联后会生成n*m条重复关联的记录。此时用SUM计算时，步数会被重复累加m次，卡路里会被重复累加n次，导致结果远大于实际值。

先分别对两张表按Id完成聚合计算，得到每个用户的总步数和总卡路里，再将这两个聚合结果通过Id关联，即可避免重复计算问题。

修正后的SQL语句 #

SELECT 
    steps.Id,
    steps.Total_steps,
    cal.Total_calories
FROM 
    (SELECT Id, SUM(StepTotal) AS Total_steps 
     FROM fitbit.Daily_steps 
     GROUP BY Id) AS steps
JOIN 
    (SELECT Id, SUM(Calories) AS Total_calories 
     FROM fitbit.Daily_calories 
     GROUP BY Id) AS cal
ON steps.Id = cal.Id;

扩展：兼容仅存在单表数据的用户 #

如果需要包含那些只有步数记录或只有卡路里记录的用户，可以使用FULL JOIN配合COALESCE处理空值：

SELECT 
    COALESCE(steps.Id, cal.Id) AS Id,
    COALESCE(steps.Total_steps, 0) AS Total_steps,
    COALESCE(cal.Total_calories, 0) AS Total_calories
FROM 
    (SELECT Id, SUM(StepTotal) AS Total_steps 
     FROM fitbit.Daily_steps 
     GROUP BY Id) AS steps
FULL JOIN 
    (SELECT Id, SUM(Calories) AS Total_calories 
     FROM fitbit.Daily_calories 
     GROUP BY Id) AS cal
ON steps.Id = cal.Id;

上述修正后的语句，查询出的Total_steps和Total_calories数值会与你单独查询两张表的结果完全一致。

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