如何在SQLite单查询中实现Elo评分计算？

你的核心问题是原递归CTE只保留了获胜队伍的评分记录，丢失了其他队伍的状态，导致无法维护每个队伍的独立评分。要修复这个问题，需要确保每一轮递归都保留所有队伍的当前评分，并仅针对当前游戏的胜负方调整分数。

简化版实现（获胜加1，失败不扣分） #

先基于你简化的规则编写，确保每个队伍的评分独立维护：

WITH ranked_games AS (
  -- 给游戏按顺序编号，确保处理顺序正确
  SELECT
    winner_team,
    loser_team,
    ROW_NUMBER() OVER (ORDER BY rowid) AS game_seq
  FROM games
),
elo_ratings AS (
  -- 初始状态：所有队伍初始Elo为1000，对应第0场游戏前的状态
  SELECT
    0 AS current_game_seq,
    name AS team,
    1000.0 AS elo
  FROM teams

  UNION ALL

  -- 递归处理每一场游戏，保留所有队伍的评分
  SELECT
    rg.game_seq AS current_game_seq,
    t.name AS team,
    -- 根据队伍角色调整Elo
    CASE
      WHEN t.name = rg.winner_team THEN er.elo + 1  -- 获胜队伍加1
      WHEN t.name = rg.loser_team THEN er.elo       -- 简化处理：失败不扣分，可改为er.elo -1
      ELSE er.elo                                   -- 其他队伍评分不变
    END AS elo
  FROM elo_ratings er
  -- 关联上一轮状态与当前要处理的游戏
  JOIN ranked_games rg ON er.current_game_seq = rg.game_seq - 1
  -- 关联所有队伍，确保每轮都保留所有队伍的记录
  JOIN teams t ON 1=1
)
-- 查询最终所有队伍的Elo评分
SELECT team, elo
FROM elo_ratings
WHERE current_game_seq = (SELECT COALESCE(MAX(game_seq), 0) FROM ranked_games)
ORDER BY elo DESC;

完整Elo评分实现 #

如果需要使用标准Elo公式（K值取32），可以修改递归中的评分计算逻辑：

WITH ranked_games AS (
  SELECT
    winner_team,
    loser_team,
    ROW_NUMBER() OVER (ORDER BY rowid) AS game_seq
  FROM games
),
elo_ratings AS (
  SELECT
    0 AS current_game_seq,
    name AS team,
    1000.0 AS elo
  FROM teams

  UNION ALL

  SELECT
    rg.game_seq AS current_game_seq,
    t.name AS team,
    CASE
      WHEN t.name = rg.winner_team THEN 
        er_winner.elo + 32 * (1 - (1 / (1 + POWER(10, (er_loser.elo - er_winner.elo)/400))))
      WHEN t.name = rg.loser_team THEN 
        er_loser.elo + 32 * (0 - (1 - (1 / (1 + POWER(10, (er_loser.elo - er_winner.elo)/400)))))
      ELSE er.elo
    END AS elo
  FROM elo_ratings er
  JOIN ranked_games rg ON er.current_game_seq = rg.game_seq - 1
  -- 获取当前游戏双方上一轮的Elo评分
  JOIN elo_ratings er_winner ON er_winner.current_game_seq = rg.game_seq - 1 AND er_winner.team = rg.winner_team
  JOIN elo_ratings er_loser ON er_loser.current_game_seq = rg.game_seq - 1 AND er_loser.team = rg.loser_team
  JOIN teams t ON 1=1
)
SELECT team, elo
FROM elo_ratings
WHERE current_game_seq = (SELECT COALESCE(MAX(game_seq), 0) FROM ranked_games)
ORDER BY elo DESC;

关键修复点 #

1. 保留所有队伍状态：每轮递归都通过JOIN teams t ON 1=1关联所有队伍，确保不会丢失非参与当前游戏队伍的评分记录。
2. 游戏顺序控制：用ROW_NUMBER()给游戏生成连续序号，避免因rowid不连续导致的处理顺序错误。
3. 针对性调整评分：通过CASE语句仅修改当前游戏胜负方的评分，其他队伍保持原有分数。

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