PostgreSQL递归查询：基于两列填充球队赛前评分值

我有一个PostgreSQL表nfl.team_rtgs，存储球队的赛前评分（pre_rating）、赛后评分（post_rating），以及对手的赛前、赛后评分。赛后评分基于赛前评分和得分、胜负分差等未展示指标计算得出。每场比赛对应两条记录（球队与对手互换），game_num是球队的累计比赛场次序号。

需求是：用球队上一场的post_rating填充当前场次的pre_rating，同时用对手上一场的post_rating填充当前场次的opponent_pre_rating。

目前已实现球队自身评分的填充，但不知道如何处理对手的评分填充，当前递归查询代码如下：

WITH RECURSIVE ratings AS (
         select r.gameday,
r.game_num,
r.team,
r.opponent,
cast(r.pre_rating as float),
cast(r.opponent_pre_rating as float),
r.post_rating,
r.opponent_post_rating
         from nfl.team_rtgs r
          WHERE r.game_num = 1
        UNION
         SELECT r2.gameday,
r2.game_num,
r2.team,
r2.opponent,
ratings_1.post_rating as pre_rating,
r2.opponent_post_rating as pre_rating,
r2.post_rating,
r2.opponent_post_rating
         from nfl.team_rtgs r2
             JOIN ratings ratings_1 ON (r2.team = ratings_1.team AND r2.game_num = (ratings_1.game_num + 1)))
             select * from ratings

要填充对手的上一场post_rating，需要在递归逻辑中额外关联一次递归CTE，定位对手的上一场比赛记录。修改后的查询代码如下：

WITH RECURSIVE ratings AS (
    -- 基础分支：所有球队的第1场比赛，保留原始赛前评分（无前置场次可复用）
    SELECT 
        r.gameday,
        r.game_num,
        r.team,
        r.opponent,
        CAST(r.pre_rating AS float) AS pre_rating,
        CAST(r.opponent_pre_rating AS float) AS opponent_pre_rating,
        r.post_rating,
        r.opponent_post_rating
    FROM nfl.team_rtgs r
    WHERE r.game_num = 1

    UNION ALL

    -- 递归分支：填充当前球队和对手的赛前评分
    SELECT 
        r2.gameday,
        r2.game_num,
        r2.team,
        r2.opponent,
        -- 当前球队上一场的赛后评分作为当前赛前评分
        team_prev.post_rating AS pre_rating,
        -- 对手上一场的赛后评分作为当前对手赛前评分
        opp_prev.post_rating AS opponent_pre_rating,
        r2.post_rating,
        r2.opponent_post_rating
    FROM nfl.team_rtgs r2
    -- 关联当前球队的上一场记录
    JOIN ratings team_prev 
        ON r2.team = team_prev.team 
        AND r2.game_num = team_prev.game_num + 1
    -- 关联对手的上一场记录：通过对手球队名+比赛日期筛选其最近一场比赛
    JOIN ratings opp_prev 
        ON r2.opponent = opp_prev.team 
        AND opp_prev.gameday = (
            SELECT MAX(gameday) 
            FROM nfl.team_rtgs 
            WHERE team = r2.opponent AND gameday < r2.gameday
        )
)
SELECT * FROM ratings ORDER BY team, game_num;

• 基础分支保留所有球队第1场的原始赛前评分，因为首场比赛没有前置场次，无法复用赛后评分。
• 递归分支通过两次关联实现双填充：
  1. 关联team_prev获取当前球队上一场的post_rating，填充自身pre_rating。
  2. 关联opp_prev时，通过对手球队名称+日期筛选，找到对手在当前比赛之前的最后一场记录，用其post_rating填充当前的opponent_pre_rating。
• 用日期匹配对手上一场比直接用game_num更稳妥，避免球队场次序号不连续的情况。

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