SQL关联不同粒度表聚合时如何避免重复值求和错误

核心原因是两表数据粒度不匹配：purchases表统计粒度为国家+设备+日期+商品ID，同一国家+设备+日期组合下会有多条不同商品的记录；而views表统计粒度为国家+设备+日期，同一组合下只有1条总浏览量记录。直接用三个字段关联后，views表的单条浏览量记录会被复制到同组合下的所有商品行上，直接对total_views求和必然会重复计算。

两种可落地的实现方案如下： #

方案1：先聚合到目标粒度再关联（优先推荐） #

这是性能最好、逻辑最不容易出错的写法，从根源上避免粒度不匹配导致的重复问题：先把两张表分别聚合到最终需要的国家+日期粒度，再关联计算指标。
示例SQL（标准SQL，兼容绝大多数数仓/OLTP数据库）：

WITH purchase_country_day AS (
    -- 先按国家、日期维度汇总总购买量
    SELECT
        country,
        day,
        SUM(num_purchases) AS total_purchases
    FROM purchases
    GROUP BY country, day
),
view_country_day AS (
    -- 直接从原始浏览表按国家、日期维度汇总总浏览量，无重复问题
    SELECT
        country,
        day,
        SUM(total_views) AS total_views
    FROM views
    GROUP BY country, day
)
SELECT
    p.country,
    p.day,
    v.total_views,
    -- 处理浏览量为0的除零异常，乘1.0避免整数除法丢精度
    CASE WHEN v.total_views = 0 THEN 0
         ELSE p.total_purchases * 1.0 / v.total_views
    END AS purchase_per_view -- 即单浏览对应购买量
FROM purchase_country_day p
LEFT JOIN view_country_day v
    ON p.country = v.country AND p.day = v.day;

对应题中美国区示例：购买量聚合结果为2+5+8=15，浏览量聚合结果为500+400=900，最终计算结果15/900完全准确，不会出现400被重复累加的问题。 #

方案2：先关联再聚合时去重计算浏览量 #

如果业务场景要求必须先做行级关联（比如需要同时用到商品维度的其他字段做过滤），聚合时不要直接SUM(total_views)，按views表的原始粒度（国家+设备+日期）去重后再求和即可，两种常用写法：

1. 用聚合函数取同组内的单值：因为同一国家+设备+日期关联出来的所有行的total_views完全相同，用MAX/MIN都能拿到原始不重复的值，外层求和即可
2. 用窗口函数给同组行打标，只取第一行的浏览量，其余行记为0再求和
   示例SQL：

SELECT
    country,
    day,
    SUM(total_purchases) AS total_purchases,
    SUM(valid_views) AS total_views,
    CASE WHEN SUM(valid_views) = 0 THEN 0
         ELSE SUM(total_purchases)*1.0 / SUM(valid_views)
    END AS purchase_per_view
FROM (
    SELECT
        p.country,
        p.day,
        p.num_purchases AS total_purchases,
        -- 写法1：同组内取max拿到不重复的浏览量
        MAX(v.total_views) OVER(PARTITION BY v.country, v.device, v.day) AS valid_views
        -- 写法2替换：打标记只取第一行的浏览量，和上面效果一致
        -- CASE WHEN ROW_NUMBER() OVER(PARTITION BY v.country, v.device, v.day ORDER BY p.itemid) = 1 
        --      THEN v.total_views ELSE 0 
        -- END AS valid_views
    FROM purchases p
    LEFT JOIN views v
        ON p.country = v.country
        AND p.device = v.device
        AND p.day = v.day
) t
GROUP BY country, day;

注意事项 #

• 数据量较大时优先选方案1，不需要做窗口函数计算、排序，执行效率远高于先关联再去重的写法
• 所有比率计算记得处理除零异常，同时做浮点转换避免整数除法截断精度
• 如果后续需要下钻到设备维度看指标，只需要把两个方案里的device字段加到分组、关联字段里即可，逻辑完全一致

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