如何在有間隙的列上創建遞歸查詢？

我已經從這裡調整了遞歸查詢，以便從動態的遊戲結果列表中計算ELO 評級（如在國際象棋中）。

給定一張遊戲表，例如：

id | home_player | away_player | home_score | away_score |
|--|-------------|-------------|------------|------------|
1  | 1           | 2           | 21         | 18         |
2  | 2           | 3           | 14         | 21         |
3  | 1           | 3           | 12         | 21         |
5  | 3           | 2           | 21         | 8          |

我想在每場比賽結束後為每個玩家建構一個 ELO 等級表，如下所示：

| current_game_number | player_id | previous_elo | new_elo |
|---------------------|-----------|--------------|---------|
| 0                   | 1         | 1000         | 1000    |
| 0                   | 2         | 1000         | 1000    |
| 0                   | 3         | 1000         | 1000    |
| 1                   | 3         | 1000         | 1000    |
| 1                   | 2         | 1000         | 984     |
| 1                   | 1         | 1000         | 1016    |
| 2                   | 1         | 1016         | 1016    |
| 2                   | 2         | 984          | 969     |
| 2                   | 3         | 1000         | 1015    |
| 3                   | 3         | 1015         | 1031    |
| 3                   | 2         | 969          | 969     |
| 3                   | 1         | 1016         | 1000    |

我可以通過這個遞歸查詢獲得大部分的方式：

WITH RECURSIVE p(current_game_number) AS (
 SELECT
   0               AS id,
   id as player_id,
   1000.0 :: FLOAT AS previous_elo,
   1000.0 :: FLOAT AS new_elo
 FROM players
 UNION ALL
 (
   WITH previous_elos AS (
       SELECT *
       FROM p
   )
   SELECT
     games.id,
     player_id,
     previous_elos.new_elo AS previous_elo,
     round(CASE WHEN player_id NOT IN (home_player, away_player)
       THEN previous_elos.new_elo
           WHEN player_id = home_player
             THEN previous_elos.new_elo + 32.0 * ((CASE WHEN home_score > away_score THEN 1 ELSE 0 END) - (r1 / (r1 + r2)))
           ELSE previous_elos.new_elo + 32.0 * ((CASE WHEN away_score > home_score THEN 1 ELSE 0 END) - (r2 / (r1 + r2))) END)
   FROM games
     JOIN previous_elos
       ON current_game_number = games.id - 1
     JOIN LATERAL (
          SELECT
            pow(10.0, (SELECT new_elo
                       FROM previous_elos
                       WHERE current_game_number = games.id - 1 AND player_id = home_player) / 400.0) AS r1,
            pow(10.0, (SELECT new_elo
                       FROM previous_elos
                       WHERE current_game_number = games.id - 1 AND player_id = away_player) / 400.0) AS r2
          ) r
       ON TRUE
 )
) SELECT * FROM p;

但是，如果遊戲 ID 序列中存在間隙，如上面的範例表中所示，則查詢將在該間隙處停止，並且不會繼續計算 ELO。我想支持間隙，以便可以刪除遊戲並支持並發插入。

我不知道如何將查詢擴展到 id 序列中的“跳過間隙”。我的預感是它需要更改遞歸連接以跳過間隙：

FROM games
 JOIN previous_elos
   ON current_game_number = games.id - 1

有沒有人有解決這個問題的建議？非常感謝任何幫助！

請參閱此SQLFiddle，其中填充了架構和範例數據。

為避免出現間隙，請使用額外的 CTE：

WITH re_enumerated_games AS 
( SELECT ROW_NUMBER() OVER (ORDER BY id) id,
        id real_game_id,
        home_player,
        away_player,
        home_score,
        away_score 
  FROM games ),

引用自：https://dba.stackexchange.com/questions/256119