基于SQL窗口函数实现球队主客场射门数的5行移动平均

表结构 #

year_round
home_team_name
away_team_name
home_kicks
away_kicks

需求说明 #

需要计算home_kicks和away_kicks的移动平均，核心要求：

• 计算某队作为主队时的射门数移动平均，需纳入该队此前作为客队时的away_kicks数据
• 用SQL窗口函数实现，替代原有复杂且缓慢的查询

现有查询代码 #

select fms.year_round ,
avg(fms.home_kicks) over (partition by fms.home_team_name order by fms.year_round desc range between 5 preceding and 1 preceding) as avg_home_kicks_5 
from fw_matches_stats fms 
order by fms.year_round desc

解决方案 #

要整合主客队数据计算移动平均，关键是先把主客场记录统一格式，再用窗口函数处理。以下是具体实现：

步骤1：统一主客数据格式 #

通过UNION ALL将每条比赛记录拆成两条：一条对应主队射门数据，一条对应客队射门数据，形成球队-场次-射门数的统一结构：

WITH team_kicks AS (
    -- 主队数据条目
    SELECT
        year_round,
        home_team_name AS team_name,
        home_kicks AS kicks
    FROM fw_matches_stats
    UNION ALL
    -- 客队数据条目
    SELECT
        year_round,
        away_team_name AS team_name,
        away_kicks AS kicks
    FROM fw_matches_stats
)

步骤2：计算各球队的移动平均 #

基于统一后的数据集，为每个球队计算最近5场（不含当前场）的射门数移动平均：

WITH team_kicks AS (
    SELECT
        year_round,
        home_team_name AS team_name,
        home_kicks AS kicks
    FROM fw_matches_stats
    UNION ALL
    SELECT
        year_round,
        away_team_name AS team_name,
        away_kicks AS kicks
    FROM fw_matches_stats
),
team_moving_avg AS (
    SELECT
        team_name,
        year_round,
        AVG(kicks) OVER (
            PARTITION BY team_name
            ORDER BY year_round DESC
            RANGE BETWEEN 5 PRECEDING AND 1 PRECEDING
        ) AS avg_kicks_last5
    FROM team_kicks
)

步骤3：关联回原表输出结果 #

将移动平均结果关联回原比赛表，分别匹配主队和客队对应的移动平均数据：

WITH team_kicks AS (
    SELECT
        year_round,
        home_team_name AS team_name,
        home_kicks AS kicks
    FROM fw_matches_stats
    UNION ALL
    SELECT
        year_round,
        away_team_name AS team_name,
        away_kicks AS kicks
    FROM fw_matches_stats
),
team_moving_avg AS (
    SELECT
        team_name,
        year_round,
        AVG(kicks) OVER (
            PARTITION BY team_name
            ORDER BY year_round DESC
            RANGE BETWEEN 5 PRECEDING AND 1 PRECEDING
        ) AS avg_kicks_last5
    FROM team_kicks
)
SELECT
    fms.year_round,
    fms.home_team_name,
    fms.away_team_name,
    fms.home_kicks,
    fms.away_kicks,
    tma_home.avg_kicks_last5 AS avg_home_kicks_5,
    tma_away.avg_kicks_last5 AS avg_away_kicks_5
FROM fw_matches_stats fms
LEFT JOIN team_moving_avg tma_home
    ON fms.home_team_name = tma_home.team_name
    AND fms.year_round = tma_home.year_round
LEFT JOIN team_moving_avg tma_away
    ON fms.away_team_name = tma_away.team_name
    AND fms.year_round = tma_away.year_round
ORDER BY fms.year_round DESC;

关键说明 #

• UNION ALL拆分主客数据是核心，让每个球队的所有主客场射门记录都归到同一分组下
• 窗口函数的RANGE BETWEEN 5 PRECEDING AND 1 PRECEDING确保只计算当前场次之前的5场数据，符合移动平均的要求
• 两次左关联保证原表每条记录都能匹配到对应主客队的移动平均

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