多对多表聚合数据时如何避免重复值问题

问题根源 #

直接关联WINNERS与WAGERS表后执行聚合，会因两张表的多对多关系产生笛卡尔积，导致聚合数值被重复计算（例如一条下注记录对应多条中奖记录时，下注金额会被乘以中奖记录数）；同时GROUP BY中包含WAGERS字段会导致分组过细，移除该字段又触发语法错误。

解决方案：先独立聚合再关联 #

核心思路是分别对两张表完成聚合计算，再将聚合结果关联，从根源避免笛卡尔积的影响。

场景1：按指定维度分组聚合 #

假设你需要按group_dim（替换为你实际的分组字段，如用户ID、日期等）汇总数据：

SELECT 
    COALESCE(w.group_dim, wg.group_dim) AS group_dim,
    COALESCE(w.total_win_times, 0) AS total_win_times,
    COALESCE(w.total_win_amount, 0) AS total_win_amount,
    wg.total_wager_times,
    wg.total_wager_amount
FROM (
    -- 预聚合中奖数据
    SELECT 
        group_dim,
        COUNT(*) AS total_win_times,
        SUM(win_amount) AS total_win_amount
    FROM WINNERS
    GROUP BY group_dim
) w
RIGHT JOIN (
    -- 预聚合下注数据
    SELECT 
        group_dim,
        COUNT(*) AS total_wager_times,
        SUM(wager_amount) AS total_wager_amount
    FROM WAGERS
    GROUP BY group_dim
) wg ON w.group_dim = wg.group_dim

• 使用RIGHT JOIN可保留所有下注分组（即使无中奖记录），若仅需包含中奖的分组，替换为INNER JOIN即可；
• COALESCE函数用于处理无中奖记录时的空值，替换为0保证结果统一。

场景2：全局汇总（无分组维度） #

如果仅需全局统计所有中奖和下注的总次数、总金额，直接用子查询独立聚合即可：

SELECT 
    (SELECT COUNT(*) FROM WINNERS) AS total_win_times,
    (SELECT SUM(win_amount) FROM WINNERS) AS total_win_amount,
    (SELECT COUNT(*) FROM WAGERS) AS total_wager_times,
    (SELECT SUM(wager_amount) FROM WAGERS) AS total_wager_amount

这种方式完全不需要表关联，彻底规避笛卡尔积问题，语法也更简洁。

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