按赛事分组查找国际象棋赛事获胜者（PySpark/SQL实现）

问题需求 #

按赛事对数据进行分组，使用PySpark或SQL找出各赛事的获胜者

当前尝试的代码 #

spark.sql("select event,winner from chess group by event,winner").show()

上述代码仅能列出每个赛事下所有出现过的获胜者，无法确定每个赛事的最终获胜者（比如获胜次数最多的选手）。以下是正确的实现方式：

1. SQL 实现 #

-- 先统计每个赛事中每个选手的获胜次数
WITH event_win_counts AS (
    SELECT 
        event, 
        winner, 
        COUNT(*) AS win_count
    FROM chess
    GROUP BY event, winner
),
-- 按赛事对获胜次数进行排名
ranked_wins AS (
    SELECT 
        event, 
        winner, 
        win_count,
        RANK() OVER (PARTITION BY event ORDER BY win_count DESC) AS rank
    FROM event_win_counts
)
-- 筛选出每个赛事的第一名获胜者
SELECT event, winner
FROM ranked_wins
WHERE rank = 1;

2. PySpark DataFrame 实现 #

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

# 统计每个赛事中各选手的获胜次数
win_count_df = chess.groupBy("event", "winner").count().withColumnRenamed("count", "win_count")

# 定义窗口：按赛事分区，按获胜次数降序排列
window_spec = Window.partitionBy("event").orderBy(F.desc("win_count"))

# 添加排名列并筛选出第一名
final_result = win_count_df.withColumn("rank", F.rank().over(window_spec)) \
                          .filter(F.col("rank") == 1) \
                          .select("event", "winner")

final_result.show()

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