使用SQLite在单个SQL查询中实现Elo计算

t30tvxxf 于 2023-10-23 发布在 SQLite

关注(0)|答案(1)|浏览(144)

我试图在一个SQL查询中实现Elo评级计算，但无法使其工作。我发现an article关于如何使用Postgres，但我不使用SQLite。另外，我的表模式有点不同：

CREATE TABLE teams (
  name TEXT NOT NULL PRIMARY KEY
)

CREATE TABLE games (
  "winner_team" TEXT REFERENCES teams,
  "loser_team" TEXT REFERENCES teams
)

到目前为止，我有以下代码（为了简化问题，我只看赢家的球队，只增加1 elo，而不是做完整的计算）：

WITH RECURSIVE elo_ratings(current_game_num, team, previous_elo, new_elo) AS (
  -- Initialize all teams with the default elo
  SELECT
    0 AS game_number,
    name,
    1000.0 AS previous_elo,
    1000.0 AS new_elo
  FROM teams

  UNION

  SELECT
    rowid AS game_number,
    winner_team,
    elo_ratings.new_elo as previous_elo,
    elo_ratings.new_elo + 1
  FROM games
  JOIN elo_ratings ON current_game_num == games.rowid - 1
  LIMIT 10000
)
SELECT * 
FROM elo_ratings

但是对于这段代码，有一个全局elo_rating，而不是每个参与者都有一个不同的elo_rating。
如何才能完成呢？

sqlite

来源：https://stackoverflow.com/questions/77033010/implement-elo-calculation-in-a-single-sql-query-with-sqlite

1条答案

按热度按时间

vlju58qv1#

我还不能提出一个纯SQL的解决方案，但是有一种方法可以使用JSON对象作为中间“表”来实现它。实际上，这可能是一个更好的解决方案。

WITH  RECURSIVE elo_ratings(current_game_num, elo_map, utc) AS (
  SELECT
    0 AS game_number,
    -- Track all elo ratings in a JSON object
    -- (initialized to 1000 here)
    json_group_object(name, 1000) as elo_map,
    '1970-01-01T00:00:00Z'
  FROM teams

  UNION

  SELECT
    rowid AS game_number,
    -- Apply elo rating changes to JSON object
    json_patch(
      json_patch(
        elo_map, 
        json_object(
          winner_team, 
          elo_map->>winner_team + (32 * (1 -
              ((pow(10, elo_map->>winner_team / 400))  /
              (pow(10, elo_map->>winner_team / 400) + pow(10, elo_map->>loser_team / 400)))
            )
          )
        )
      ),
      json_object(
        loser_team,
        elo_map->>loser_team + (32 * (0 -
            ((pow(10, elo_map->>loser_team / 400)) /
            (pow(10, elo_map->>winner_team / 400) + pow(10, elo_map->>loser_team/ 400)))
          )
        )
      )
    ),
    games.utc
  FROM games
  JOIN elo_ratings ON current_game_num == games.rowid - 1
  ORDER BY games.utc
)
SELECT DISTINCT key, value
FROM 
  (SELECT * 
    FROM elo_ratings 
    ORDER BY current_game_num DESC 
    LIMIT 1
  ),
  json_each(elo_map)
ORDER BY value DESC

赞(0）回复(0）举报 2023-10-23

我来回答

使用SQLite在单个SQL查询中实现Elo计算

1条答案

相关问题

热门标签

最新问答