赛事统计SQL查询优化咨询：高效生成球队战绩表

编写SQL查询生成包含以下列的表格：球队名称、参赛场次、胜场数、负场数、平局数、积分。积分规则：胜一场得3分，平一场得1分，负场无积分。

表结构 #

表teams #

表matches #

示例数据 #

表teams #

表matches #

预期输出 #

现有实现代码 #

SELECT
    t.name, 
    count(m.team_1) filter(WHERE t.id = m.team_1)
        + count(m.team_2) filter(WHERE t.id = m.team_2) "matches",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals) "victories",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals < m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals > m.team_2_goals) "defeats",
    count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "draws",
    ((count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals))* 3) +
        count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
        + count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "score"
FROM
    teams t
JOIN matches m ON t.id IN (m.team_1, m.team_2)
GROUP BY t.name
ORDER BY "victories" DESC

可以通过UNION ALL将每场比赛的两个球队拆分为独立行，简化统计逻辑的同时提升查询性能：

WITH team_matches AS (
    -- 处理team_1的比赛结果
    SELECT
        team_1 AS team_id,
        CASE WHEN team_1_goals > team_2_goals THEN 1 ELSE 0 END AS is_victory,
        CASE WHEN team_1_goals < team_2_goals THEN 1 ELSE 0 END AS is_defeat,
        CASE WHEN team_1_goals = team_2_goals THEN 1 ELSE 0 END AS is_draw
    FROM matches
    UNION ALL
    -- 处理team_2的比赛结果
    SELECT
        team_2 AS team_id,
        CASE WHEN team_2_goals > team_1_goals THEN 1 ELSE 0 END AS is_victory,
        CASE WHEN team_2_goals < team_1_goals THEN 1 ELSE 0 END AS is_defeat,
        CASE WHEN team_2_goals = team_1_goals THEN 1 ELSE 0 END AS is_draw
    FROM matches
)
SELECT
    t.name,
    COUNT(tm.team_id) AS matches,
    COALESCE(SUM(tm.is_victory), 0) AS victories,
    COALESCE(SUM(tm.is_defeat), 0) AS defeats,
    COALESCE(SUM(tm.is_draw), 0) AS draws,
    COALESCE(SUM(tm.is_victory)*3 + SUM(tm.is_draw), 0) AS score
FROM teams t
LEFT JOIN team_matches tm ON t.id = tm.team_id
GROUP BY t.id, t.name
ORDER BY victories DESC, score DESC;

优化说明 #

1. 逻辑简化：拆分每个球队的比赛记录后，聚合时只需简单求和/计数，避免了原查询中重复的filter判断，代码可读性大幅提升。
2. 性能提升：拆分后每条比赛记录仅处理两次（对应两个球队），减少了数据库的重复计算开销；LEFT JOIN确保无参赛记录的球队也能被统计（原查询的JOIN会遗漏此类数据）。
3. 鲁棒性增强：用COALESCE处理无比赛球队的统计值，避免出现NULL；分组时同时使用id和name，防止球队名称重复导致的分组错误。

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