游戏ELO排行榜SQL查询未统计失败场次问题求助

我为游戏制作ELO评分排行榜，基于单条SELECT语句实现（因只读权限限制），目前功能正常但losses字段始终显示为0，即使存在输掉比赛的玩家。以下是我的SQL代码：

WITH RECURSIVE p(current_game_number) AS (
    WITH game_ids AS (
SELECT DISTINCT games.id g,MAX(turns.updated_at) tmx FROM games
LEFT JOIN turns ON turns.game_id = games.id
WHERE meta LIKE '%pvp%over%'
AND deleted_at IS NULL
AND turns.updated_at > CURRENT_TIMESTAMP - INTERVAL '1 days'
GROUP BY games.id
), pre1 AS (
SELECT game_ids.*, player_one_score p1, player_two_score p2, u.username player1 FROM game_ids
LEFT JOIN turns ON turns.game_id = g
INNER JOIN games_users gu ON gu.game_id = g
LEFT JOIN users u ON u.id = gu.user_id
WHERE turns.updated_at = tmx
AND is_first = TRUE
)
, pre2 AS (
SELECT game_ids.*, player_one_score p1, player_two_score p2, u.username player2 FROM game_ids
LEFT JOIN turns ON turns.game_id = g
INNER JOIN games_users gu ON gu.game_id = g
LEFT JOIN users u ON u.id = gu.user_id
WHERE turns.updated_at = tmx
AND is_first = FALSE
), pre3 AS (
SELECT pre2.*, pre1.player1 FROM pre2
INNER JOIN pre1 ON pre1.tmx = pre2.tmx
), rd AS (
SELECT *,
    COUNT(*) AS CNT
FROM pre3
GROUP BY g, tmx, p1, p2, player1, player2 
HAVING COUNT(*) = 1
), plays AS (
SELECT CAST(ROW_NUMBER() OVER() AS BIGINT) game_number, player1 p1name, p1 p1score, player2 p2name, p2 p2score FROM rd
),  players AS (
    SELECT DISTINCT p1name AS player_name
    FROM plays
    UNION
    SELECT DISTINCT p2name
    FROM plays
  )
  SELECT
    CAST(0 AS BIGINT)               AS game_number,
    player_name,
    1000.0 :: FLOAT AS previous_elo,
    1000.0 :: FLOAT AS new_elo
  FROM players
  UNION ALL
  (
    WITH previous_elos AS (
        SELECT *
        FROM p
    )
    SELECT
      plays.game_number,
      player_name,
      previous_elos.new_elo AS previous_elo,
      round(CASE WHEN player_name NOT IN (p1name, p2name)
        THEN previous_elos.new_elo
            WHEN player_name = p1name
              THEN previous_elos.new_elo + 32.0 * (p1score - (r1 / (r1 + r2)))
            ELSE previous_elos.new_elo + 32.0 * (p2score - (r2 / (r1 + r2))) END)
    FROM plays
      JOIN previous_elos
        ON current_game_number = plays.game_number - 1
      JOIN LATERAL (
           SELECT
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = plays.game_number - 1 AND player_name = p1name) / 400.0) AS r1,
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = plays.game_number - 1 AND player_name = p2name) / 400.0) AS r2
           ) r
        ON TRUE
  )
)
SELECT
  player_name,
  (
    SELECT new_elo
    FROM p
    WHERE t.player_name = p.player_name
    ORDER BY current_game_number DESC
    LIMIT 1
  )                    AS elo,
  count(CASE WHEN previous_elo < new_elo
    THEN 1
        ELSE NULL END) AS wins,
  count(CASE WHEN previous_elo > new_elo
    THEN 1
        ELSE NULL END) AS losses
FROM
  (
    SELECT *
    FROM p
    WHERE previous_elo <> new_elo
    ORDER BY current_game_number, player_name
  ) t
GROUP BY player_name
ORDER BY elo DESC;

问题原因分析 #

1. ELO计算逻辑错误：输家的ELO更新公式依赖p2score - (r2/(r1+r2))的结果，如果p1score/p2score是实际比赛得分而非1（赢）/0（输）的胜负标识，会导致计算出的ELO变化量为正或零，无法反映输家的ELO下降。
2. 递归记录过滤遗漏：WHERE previous_elo <> new_elo会排除所有ELO无变化的记录，若输家的ELO被错误计算为与之前相等，就会被过滤，无法进入losses统计。
3. 玩家记录生成不完整：当前逻辑仅为参与当前比赛的玩家生成ELO记录，但关联逻辑可能遗漏部分输家的记录，导致其ELO变化未被追踪。

解决方法 #

1. 修正胜负判断与ELO计算逻辑 #

将实际得分转换为1（赢）/0（输）的标准胜负标识，确保ELO计算的输入正确：

-- 替换原CASE中的p1score/p2score为胜负标识
CASE WHEN player_name = p1name THEN CASE WHEN p1score > p2score THEN 1 ELSE 0 END
     WHEN player_name = p2name THEN CASE WHEN p2score > p1score THEN 1 ELSE 0 END
END AS actual_score

2. 移除不必要的过滤条件 #

删除WHERE previous_elo <> new_elo，保留所有玩家的ELO记录，确保输家的变化能被统计到。

3. 完整修改后的SQL #

WITH RECURSIVE p(current_game_number, player_name, previous_elo, new_elo) AS (
    WITH game_ids AS (
SELECT DISTINCT games.id g,MAX(turns.updated_at) tmx FROM games
LEFT JOIN turns ON turns.game_id = games.id
WHERE meta LIKE '%pvp%over%'
AND deleted_at IS NULL
AND turns.updated_at > CURRENT_TIMESTAMP - INTERVAL '1 days'
GROUP BY games.id
), pre1 AS (
SELECT game_ids.*, player_one_score p1, player_two_score p2, u.username player1 FROM game_ids
LEFT JOIN turns ON turns.game_id = g
INNER JOIN games_users gu ON gu.game_id = g
LEFT JOIN users u ON u.id = gu.user_id
WHERE turns.updated_at = tmx
AND is_first = TRUE
)
, pre2 AS (
SELECT game_ids.*, player_one_score p1, player_two_score p2, u.username player2 FROM game_ids
LEFT JOIN turns ON turns.game_id = g
INNER JOIN games_users gu ON gu.game_id = g
LEFT JOIN users u ON u.id = gu.user_id
WHERE turns.updated_at = tmx
AND is_first = FALSE
), pre3 AS (
SELECT pre2.*, pre1.player1 FROM pre2
INNER JOIN pre1 ON pre1.tmx = pre2.tmx
), rd AS (
SELECT *,
    COUNT(*) AS CNT
FROM pre3
GROUP BY g, tmx, p1, p2, player1, player2 
HAVING COUNT(*) = 1
), plays AS (
SELECT CAST(ROW_NUMBER() OVER() AS BIGINT) game_number, player1 p1name, p1 p1score, player2 p2name, p2 p2score FROM rd
),  players AS (
    SELECT DISTINCT p1name AS player_name
    FROM plays
    UNION
    SELECT DISTINCT p2name
    FROM plays
  )
  SELECT
    CAST(0 AS BIGINT)               AS current_game_number,
    player_name,
    1000.0 :: FLOAT AS previous_elo,
    1000.0 :: FLOAT AS new_elo
  FROM players
  UNION ALL
  (
    WITH previous_elos AS (
        SELECT *
        FROM p
    )
    SELECT
      plays.game_number AS current_game_number,
      players.player_name,
      previous_elos.new_elo AS previous_elo,
      round(CASE WHEN players.player_name NOT IN (p1name, p2name)
        THEN previous_elos.new_elo
            WHEN players.player_name = p1name
              THEN previous_elos.new_elo + 32.0 * (CASE WHEN p1score > p2score THEN 1 ELSE 0 END - (r1 / (r1 + r2)))
            ELSE previous_elos.new_elo + 32.0 * (CASE WHEN p2score > p1score THEN 1 ELSE 0 END - (r2 / (r1 + r2))) END) AS new_elo
    FROM plays
      CROSS JOIN players
      JOIN previous_elos
        ON previous_elos.current_game_number = plays.game_number - 1 AND previous_elos.player_name = players.player_name
      JOIN LATERAL (
           SELECT
             pow(10.0, (SELECT new_elo FROM previous_elos WHERE current_game_number = plays.game_number - 1 AND player_name = p1name) / 400.0) AS r1,
             pow(10.0, (SELECT new_elo FROM previous_elos WHERE current_game_number = plays.game_number - 1 AND player_name = p2name) / 400.0) AS r2
           ) r
        ON TRUE
  )
)
SELECT
  player_name,
  (
    SELECT new_elo
    FROM p
    WHERE t.player_name = p.player_name
    ORDER BY current_game_number DESC
    LIMIT 1
  )                    AS elo,
  count(CASE WHEN previous_elo < new_elo
    THEN 1
        ELSE NULL END) AS wins,
  count(CASE WHEN previous_elo > new_elo
    THEN 1
        ELSE NULL END) AS losses
FROM
  (
    SELECT *
    FROM p
    ORDER BY current_game_number, player_name
  ) t
GROUP BY player_name
ORDER BY elo DESC;

关键修改点 #

• 新增胜负标识转换：将比赛得分转换为1/0的标准值，确保ELO计算逻辑符合预期。
• 调整记录生成方式：用CROSS JOIN players确保每个游戏场次下所有玩家都有对应的ELO记录，避免遗漏输家。
• 移除无效过滤：保留所有ELO记录，确保输家的负向变化能被统计到。

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