如何基于全轮次结果计算各国际象棋冠军赛的获胜者？

规则说明 #

选手在某赛事所有轮次的总结果即为该选手的得分/成绩。

数据Schema #

|-- game_id: string (nullable = true)
 |-- game_order: integer (nullable = true)
 |-- event: string (nullable = true)
 |-- site: string (nullable = true)
 |-- date_played: string (nullable = true)
 |-- round: double (nullable = true)
 |-- white: string (nullable = true)
 |-- black: string (nullable = true)
 |-- result: string (nullable = true)
 |-- white_elo: integer (nullable = true)
 |-- black_elo: integer (nullable = true)
 |-- white_title: string (nullable = true)
 |-- black_title: string (nullable = true)
 |-- winner: string (nullable = true)
 |-- winner_elo: integer (nullable = true)
 |-- loser: string (nullable = true)
 |-- loser_elo: integer (nullable = true)
 |-- winner_loser_elo_diff: integer (nullable = true)
 |-- eco: string (nullable = true)
 |-- date_created: string (nullable = true)
 |-- tournament_name: string (nullable = true)

示例DataFrame #

+--------------------+----------+--------+----------+-----------+-----+----------------+----------------+-------+---------+---------+-----------+-----------+---------+----------+----------------+---------+---------------------+---+--------------------+---------------+
|             game_id|game_order|   event|      site|date_played|round|           white|           black| result|white_elo|black_elo|white_title|black_title|   winner|winner_elo|           loser|loser_elo|winner_loser_elo_diff|eco|        date_created|tournament_name|
+--------------------+----------+--------+----------+-----------+-----+----------------+----------------+-------+---------+---------+-----------+-----------+---------+----------+----------------+---------+---------------------+---+--------------------+---------------+
|86e0b7f5-7b94-4ae...|         1|WCh 2021| Dubai UAE| 2021.11.26|  1.0|Nepomniachtchi,I|       Carlsen,M|1/2-1/2|     2782|     2855|       null|       null|     draw|      null|            draw|     null|                    0|C88|2022-07-22T22:33:...| WorldChamp2021|
|dc4a10ab-54cf-49d...|         2|WCh 2021| Dubai UAE| 2021.11.27|  2.0|       Carlsen,M|Nepomniachtchi,I|1/2-1/2|     2855|     2782|       null|       null|     draw|      null|            draw|     null|                    0|E06|2022-07-22T22:33:...| WorldChamp2021|
|f042ca37-8899-488...|         3|WCh 2021| Dubai UAE| 2021.11.28|  3.0|Nepomniachtchi,I|       Carlsen,M|1/2-1/2|     2782|     2855|       null|       null|     draw|      null|            draw|     null|                    0|C88|2022-07-22T22:33:...| WorldChamp2021|
|f70e4bbc-21e3-46f...|         4|WCh 2021| Dubai UAE| 2021.11.30|  4.0|       Carlsen,M|Nepomniachtchi,I|1/2-1/2|     2855|     2782|       null|       null|     draw|      null|            draw|     null|                    0|C42|2022-07-22T22:33:...| WorldChamp2021|
|c941c323-308a-4c8...|         5|WCh 2021| Dubai UAE| 2021.12.01|  5.0|Nepomniachtchi,I|       Carlsen,M|1/2-1/2|     2782|     2855|       null|       null|     draw|      null|            draw|     null|                    0|C88|2022-07-22T22:33:...| WorldChamp2021|
|58e83255-93bb-4d5...|         6|WCh 2021| Dubai UAE| 2021.12.03|  6.0|       Carlsen,M|Nepomniachtchi,I|    1-0|     2855|     2782|       null|       null|Carlsen,M|      2855|Nepomniachtchi,I|     2782|                   73|D02|2022-07-22T22:33:...| WorldChamp2021|
|29181d93-73f4-4fb...|         7|WCh 2021| Dubai UAE| 2021.12.04|  7.0|Nepomniachtchi,I|       Carlsen,M|1/2-1/2|     2782|     2855|       null|       null|     draw|      null|            draw|     null|                    0|C88|2022-07-22T22:33:...| WorldChamp2021|
|8a4ccd8c-d437-429...|         8|WCh 2021| Dubai UAE| 2021.12.05|  8.0|       Carlsen,M|Nepomniachtchi,I|    1-0|     2855|     2782|       null|       null|Carlsen,M|      2855|Nepomniachtchi,I|     2782|                   73|C43|2022-07-22T22:33:...| WorldChamp2021|
|55a122db-27d1-495...|         9|WCh 2021| Dubai UAE| 2021.12.07|  9.0|Nepomniachtchi,I|       Carlsen,M|    0-1|     2782|     2855|       null|       null|Carlsen,M|      2855|Nepomniachtchi,I|     2782|                   73|A13|2022-07-22T22:33:...| WorldChamp2021|
|1f900d18-5ea3-4f4...|        10|WCh 2021| Dubai UAE| 2021.12.08| 10.0|       Carlsen,M|Nepomniachtchi,I|1/2-1/2|     2855|     2782|       null|       null|     draw|      null|            draw|     null|                    0|C42|2022-07-22T22:33:...| WorldChamp2021|
+--------------------+----------+--------+----------+-----------+-----+----------------+----------------+-------+---------+---------+-----------+-----------+---------+----------+----------------+---------+---------------------+---+--------------------+---------------+

当前错误代码及结果 #

错误代码 #

winners = df_history_info.filter(df_history_info['winner'] != "draw").groupBy("tournament_name").agg({"winner":"max"}).show() 

错误结果 #

+---------------+--------------------+
|tournament_name|         max(winner)|
+---------------+--------------------+
| WorldChamp2004|              Leko,P|
| WorldChamp1894|   Steinitz, William|
| WorldChamp2013|     Carlsen, Magnus|
|  FideChamp2000|       Yermolinsky,A|
| WorldChamp2007|           Svidler,P|
|  FideChamp1993|       Timman, Jan H|
|WorldChamp1910b|     Lasker, Emanuel|
| WorldChamp1921|Capablanca, Jose ...|
| WorldChamp1958|    Smyslov, Vassily|
| WorldChamp1981|  Kortschnoj, Viktor|
| WorldChamp1961|         Tal, Mihail|
| WorldChamp1978|  Kortschnoj, Viktor|
| WorldChamp1960|         Tal, Mihail|
| WorldChamp1948|    Smyslov, Vassily|
| WorldChamp1929|    Bogoljubow, Efim|
| WorldChamp1934|    Bogoljubow, Efim|
| WorldChamp1986|      Kasparov, Gary|
|   PCAChamp1995|      Kasparov, Gary|
| WorldChamp1886|Zukertort, Johann...|
| WorldChamp1907|     Lasker, Emanuel|
+---------------+--------------------+

问题分析 #

当前代码用max(winner)是取选手名字的字典序最大值，完全不符合赛事得分规则，自然结果错误。必须通过计算每个选手在赛事中的总得分，再找出得分最高的选手才是正确的获胜者。

正确解法 #

国际象棋赛事得分规则：

• 胜1局得1分
• 和局双方各得0.5分
• 负局得0分

正确代码（PySpark） #

from pyspark.sql import functions as F
from pyspark.sql.window import Window

# 步骤1：生成每个选手的得分行
player_scores = df_history_info.select(
    F.col("tournament_name"),
    # 白方得分
    F.col("white").alias("player"),
    F.when(F.col("result") == "1-0", 1.0)
     .when(F.col("result") == "1/2-1/2", 0.5)
     .otherwise(0.0).alias("score")
).union(
    df_history_info.select(
        F.col("tournament_name"),
        # 黑方得分
        F.col("black").alias("player"),
        F.when(F.col("result") == "0-1", 1.0)
         .when(F.col("result") == "1/2-1/2", 0.5)
         .otherwise(0.0).alias("score")
    )
)

# 步骤2：按赛事和选手计算总得分
total_scores = player_scores.groupBy("tournament_name", "player")
                            .agg(F.sum("score").alias("total_score"))

# 步骤3：找出每个赛事中得分最高的选手
window_spec = Window.partitionBy("tournament_name").orderBy(F.desc("total_score"))
tournament_winners = total_scores.withColumn("rank", F.rank().over(window_spec))
                                 .filter(F.col("rank") == 1)
                                 .select("tournament_name", "player", "total_score")
                                 .orderBy("tournament_name")

tournament_winners.show()

代码说明 #

• 生成选手得分行：通过union把白方和黑方的得分记录合并，确保每个选手每局的得分都被统计
• 求和总得分：按赛事和选手分组，对得分求和得到总成绩
• 筛选最高分选手：使用窗口函数rank()为每个赛事的选手按得分排序，取排名第1的即为赛事获胜者（若有并列第一会都保留，符合实际赛事规则）

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