BigQuery使用SUM函数按赛季统计各队主客场进球数

问题与数据说明 #

需求为按赛季维度，统计每支球队的主场累计进球数（hg）、客场累计进球数（ag）。

• 现有比赛表match存储了每场比赛的赛季、主队进球、客队进球、主队名称、客队名称字段
• 原有SQL仅能统计到单类进球数据，无法拆分主客场两个独立指标

原表样例数据：

season  |home_goal  |away_goal |team_home |team_away|
-----------------------------------------------------
1       |  1        |0         |France    |Spain    |    
1       |  1        |2         |Italie    |Spain    |     
1       |  0        |1         |Spain     |Italie   |      
1       |  1        |3         |France    |Italie   |     
1       |  1        |4         |Spain     |Portugal |    
1       |  3        |4         |Portugal  |Italie   |    
2       |  1        |2         |France    |Portugal |         
2       |  1        |0         |Spain     |Italie   |     
2       |  0        |1         |Spain     |Portugal |      
2       |  3        |2         |Italie    |Spain    | 
2       |  0        |1         |France    |Portugal |    
...     | ...       |...       |...       |...      |  

预期输出格式：

season  |hg         |ag        |team      |
-------------------------------------------
1       |  2        |0         |France    |   
1       |  1        |8         |Italie    |    
1       |  1        |2         |Spain     |      
1       |  3        |4         |Portugal  |    
2       |  1        |0         |France    |  
...     | ...       |...       |...      |    

原有SQL的问题 #

• UNION ALL按列位置匹配数据，原写法两个分支字段语义未对齐：第一个分支取主队+主场进球，第二个分支直接取客队+客场进球放在相同列位置，没有给两类进球打主客场标识
• 最终聚合逻辑仅计算了单字段求和，没有拆分统计主场、客场两个独立进球指标

可直接运行的正确SQL #

核心逻辑为将每场比赛拆分为主队、客队两条记录：主队记录的客场进球记为0，客队记录的主场进球记为0，再按赛季+球队分组求和两个进球字段即可。

WITH team_match_records AS (
  -- 拆分主队维度记录
  SELECT
    season,
    team_home AS team,
    home_goal AS hg,
    0 AS ag
  FROM `match`

  UNION ALL

  -- 拆分客队维度记录
  SELECT
    season,
    team_away AS team,
    0 AS hg,
    away_goal AS ag
  FROM `match`
)
SELECT
  season,
  SUM(hg) AS hg,
  SUM(ag) AS ag,
  team
FROM team_match_records
GROUP BY 1, 4
ORDER BY season, team

注：如果需要和样例输出顺序完全一致，可调整ORDER BY字段自定义排序规则。

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