如何在SQL中计算球队跨主客场得分的移动平均值

需求是计算球队得分的移动平均值，需包含球队作为主场和客场时的所有得分。

现有Match表数据：

原SQL仅计算球队主场得分的移动平均：

SELECT
    *,
    AVG(home_score) OVER (PARTITION BY home_team 
                          ORDER BY match_date 
                          ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS avgscore
FROM 
    Match

但需要将球队作为客场时的Away_score也纳入计算，例如CHI队的得分应包含客场99分、主场119和126分，预期移动平均值为114.67，而原SQL仅得到主场得分的平均值122.5。

要实现包含主客场得分的移动平均，需先将原表数据拆分为单球队-单得分的结构，再计算移动平均：

1. 拆分主客场得分记录 #

使用UNION ALL将每场比赛的客队得分和主队得分拆分为独立记录，统一字段为team、score和match_date：

SELECT 
    away_team AS team,
    Away_score AS score,
    match_date
FROM Match
UNION ALL
SELECT 
    home_team AS team,
    home_score AS score,
    match_date
FROM Match

2. 计算移动平均值 #

基于拆分后的数据集，用窗口函数按球队分组、比赛日期排序，计算最近11场比赛（当前行+前10行）的得分平均值：

WITH TeamScores AS (
    SELECT 
        away_team AS team,
        Away_score AS score,
        match_date
    FROM Match
    UNION ALL
    SELECT 
        home_team AS team,
        home_score AS score,
        match_date
    FROM Match
)
SELECT
    team,
    match_date,
    score,
    ROUND(AVG(score) OVER (
        PARTITION BY team 
        ORDER BY match_date 
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_score
FROM TeamScores
ORDER BY team, match_date;

关键说明 #

• UNION ALL保留所有记录，确保不遗漏任何一场比赛的主客场得分
• PARTITION BY team限定仅对当前球队的得分计算平均值
• ORDER BY match_date保证移动平均按比赛时间顺序计算
• ROWS BETWEEN 10 PRECEDING AND CURRENT ROW控制计算范围（可根据需求调整行数）
• ROUND(..., 2)用于将结果保留两位小数，匹配示例中的114.67

针对CHI队，拆分后会得到3条有效得分记录，计算后移动平均值为(99+119+126)/3 ≈ 114.67，符合预期。

内容的提问来源于stack exchange，提问作者Jan-Maarten Van Osch