如何合并SQL查询中同一国家的主客场统计值并去重

原始问题 #

当前SQL查询会将同一国家分别作为主队、客队的败场统计分开返回，需要将同一国家的两次统计值求和，仅显示一条汇总记录。

原始SQL：

SELECT home_team, COUNT(home_team) 
from results 
WHERE results.home_score < results.away_score 
GROUP BY home_team 
UNION 
SELECT away_team, COUNT(away_team) 
FROM results 
WHERE results.away_score < results.home_score 
GROUP BY away_team

解决方案 #

将两个子查询的结果用UNION ALL合并（比UNION更高效，无需额外去重），再在外层按国家分组求和。修改后的Hive SQL如下：

SELECT team, SUM(team_count) AS total_losses
FROM (
    -- 统计作为主队时的败场数
    SELECT home_team AS team, COUNT(home_team) AS team_count
    FROM results 
    WHERE home_score < away_score 
    GROUP BY home_team
    UNION ALL
    -- 统计作为客队时的败场数
    SELECT away_team AS team, COUNT(away_team) AS team_count
    FROM results 
    WHERE away_score < home_score 
    GROUP BY away_team
) AS team_losses
GROUP BY team
-- 可选：按总败场数排序，按需添加
ORDER BY total_losses DESC;

说明 #

1. 替换UNION为UNION ALL：避免不必要的去重操作，保留同一国家在两个子查询中的所有记录，确保求和准确。
2. 统一字段别名：将home_team和away_team统一命名为team，计数列统一为team_count，方便外层聚合操作。
3. 外层分组求和：通过GROUP BY team合并同一国家的统计数据，用SUM(team_count)计算总败场数。
4. 排序兼容：添加的ORDER BY语句符合Hive语法，不会引发排序报错，可按需启用。

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