PieCloudDB中球队赛事统计SQL查询性能优化问询

数据表结构 #

Teams表 #

记录球队ID与名称，字段：team_id、team_name

Matches表 #

记录比赛双方ID及进球数，字段：home_team_id、away_team_id、home_team_goals、away_team_goals

统计需求 #

生成包含以下字段的统计报表：

• team_name：球队名称
• games_played：参赛场数
• points：总积分（胜方得3分，负方0分，平局双方各得1分）
• goals_scored：总进球数
• goals_against：总失球数
• goals_difference：净胜球（goals_scored - goals_against）

排序规则：

1. 按points降序
2. 积分相同则按goals_difference降序
3. 仍相同则按team_name字典序升序

示例结果：

原SQL问题 #

原SQL通过交叉连接Teams和Matches后过滤，再计算各项指标，数据量大时执行效率低下：

select team_name,count(*) games_played,
3*(sum(g1)+sum(g2))+ sum(g3) points,
sum(goals_scored) goals_scored,
sum(goals_against) goals_against,
sum(goals_scored-goals_against) goals_difference
from (select *,
case when home_team_id = team_id then home_team_goals  when away_team_id = team_id then away_team_goals else 0 end as goals_scored,
case when home_team_id = team_id then away_team_goals  when away_team_id = team_id then home_team_goals else 0 end as goals_against,
case when home_team_id = team_id and  home_team_goals > away_team_goals then 1 else 0 end as g1,
case when away_team_id = team_id and  home_team_goals < away_team_goals then 1 else 0 end as g2,
case when home_team_goals = away_team_goals then 1 else 0 end as g3
from Teams t ,Matches m
where team_id in (home_team_id,away_team_id)) a 
group by 1 
order by points desc,goals_difference desc,team_name;

优化方案 #

采用UNION ALL将每场比赛拆成主客场两条记录，直接关联Teams表后聚合，避免交叉连接产生的冗余数据，大幅提升查询效率：

SELECT
    t.team_name,
    COUNT(*) AS games_played,
    SUM(points) AS points,
    SUM(goals_scored) AS goals_scored,
    SUM(goals_against) AS goals_against,
    SUM(goals_scored - goals_against) AS goals_difference
FROM (
    -- 处理主场球队数据
    SELECT
        home_team_id AS team_id,
        home_team_goals AS goals_scored,
        away_team_goals AS goals_against,
        CASE
            WHEN home_team_goals > away_team_goals THEN 3
            WHEN home_team_goals = away_team_goals THEN 1
            ELSE 0
        END AS points
    FROM Matches
    UNION ALL
    -- 处理客场球队数据
    SELECT
        away_team_id AS team_id,
        away_team_goals AS goals_scored,
        home_team_goals AS goals_against,
        CASE
            WHEN away_team_goals > home_team_goals THEN 3
            WHEN away_team_goals = home_team_goals THEN 1
            ELSE 0
        END AS points
    FROM Matches
) AS match_stats
JOIN Teams t ON match_stats.team_id = t.team_id
GROUP BY t.team_name
ORDER BY points DESC, goals_difference DESC, t.team_name;

优化说明 #

1. 避免冗余数据：原SQL中Teams与Matches交叉连接后过滤，会产生大量不必要的行；而UNION ALL仅生成每场比赛的两条有效记录（主、客场各一条），数据量大幅减少，降低后续聚合计算的压力。
2. 简化计算逻辑：直接在子查询中计算单场比赛的积分、进球和失球，避免外层多次嵌套CASE函数求和，逻辑更清晰且执行效率更高。
3. 高效关联：通过JOIN替代原有的过滤条件，数据库可利用team_id上的索引快速匹配，进一步提升查询速度。

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