SQL使用SUM和GROUP BY关联疫情数据集求和结果偏大如何解决

问题根源 #

计算偏差是JOIN操作产生笛卡尔积导致的：

• deaths和confirmed_cases表中，同一个国家都对应多条省市维度的数据
• 直接按国家关联两个表时，会产生 该国家死亡表行数 × 该国家确诊表行数 的关联结果，以你给出的中国数据为例，关联后会生成16×16=256行重复数据
• 最后再做SUM聚合时，每条死亡数据会被重复计算16次（确诊表的行数），每条确诊数据也会被重复计算16次（死亡表的行数），所以最终结果是正确值的16倍，和你观察到的输出一致。

解决方法：先分别聚合再关联 #

正确的逻辑是先单独对两个表按国家做聚合，得到每个国家唯一的汇总值之后，再做表关联，就能避免重复计算：

-- 方法1：使用子查询实现
SELECT
    COALESCE(d.country, c.country, 'Unknown') AS country,
    IFNULL(d.total_deaths, 0) AS deaths,
    IFNULL(c.total_cases, 0) AS cases
FROM (
    -- 先单独聚合死亡表
    SELECT 
        country,
        SUM(_11_16_21) AS total_deaths
    FROM `covid.deaths`
    GROUP BY country
) AS d
FULL OUTER JOIN (
    -- 先单独聚合确诊表
    SELECT 
        country,
        SUM(_11_16_21) AS total_cases
    FROM `covid.confirmed_cases`
    GROUP BY country
) AS c ON d.country = c.country
WHERE COALESCE(d.country, c.country) = 'China';

如果你的SQL引擎支持CTE（公共表表达式），可以写成更易读的形式：

-- 方法2：使用CTE实现
WITH death_agg AS (
    SELECT 
        country,
        SUM(_11_16_21) AS total_deaths
    FROM `covid.deaths`
    GROUP BY country
),
case_agg AS (
    SELECT 
        country,
        SUM(_11_16_21) AS total_cases
    FROM `covid.confirmed_cases`
    GROUP BY country
)
SELECT
    COALESCE(d.country, c.country, 'Unknown') AS country,
    IFNULL(d.total_deaths, 0) AS deaths,
    IFNULL(c.total_cases, 0) AS cases
FROM death_agg d
FULL OUTER JOIN case_agg c ON d.country = c.country
WHERE COALESCE(d.country, c.country) = 'China';

补充说明 #

• 这里用FULL OUTER JOIN是为了兼容只有死亡数据或者只有确诊数据的国家
• IFNULL是为了把没有对应数据的字段默认置为0，避免出现null值
• 执行上述查询后，中国的结果会返回正确的死亡数4849，确诊数17441。

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