SQL查询网球赛事中连续获胜场次最多的球员方法求助

你当前编写的SQL仅统计了每个球员的总胜场数和每个比赛日的总赛事数量，没有实现「连续获胜场次」的判断逻辑，因此无法得到预期结果。

我们可以用SQL经典的岛屿与间隙算法处理连续序列问题，完整实现逻辑如下：

实现思路 #

1. 拆分每场比赛的参赛记录：每场比赛对应2条数据，分别记录赢家、输家的参赛结果，新增字段标记胜负状态
2. 对每个球员的所有参赛记录按比赛时间+赛事ID排序，构造连胜分组标识：遇到输球则分组标识+1，同一分组内的胜利记录属于同一段连续连胜
3. 统计每个球员每个连胜分组的胜场数，取最大值
4. 关联球员表获取球员的姓名、国籍等基础信息

完整SQL代码 #

WITH player_all_matches AS (
    -- 拆分每场比赛的赢家和输家，统一为参赛记录
    SELECT 
        winner_id AS player_id,
        tourney_date,
        mid,
        1 AS is_win -- 标记为获胜
    FROM matches
    UNION ALL
    SELECT 
        loser_id AS player_id,
        tourney_date,
        mid,
        0 AS is_win -- 标记为落败
    FROM matches
),
player_match_rn AS (
    -- 对每个球员的参赛记录按时间排序，构造连胜分组标识
    SELECT 
        *,
        SUM(CASE WHEN is_win = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY player_id 
            ORDER BY tourney_date, mid
        ) AS win_group
    FROM player_all_matches
),
player_win_streak AS (
    -- 统计每个球员每一段连胜的场数
    SELECT 
        player_id,
        win_group,
        COUNT(*) AS consecutive_win_cnt
    FROM player_match_rn
    WHERE is_win = 1 -- 只统计获胜场次
    GROUP BY player_id, win_group
)
-- 取连续胜场最高的球员信息，关联球员表获取详情
SELECT 
    p.playerid,
    p.`First Name`,
    p.`Last Name`,
    p.Country,
    s.consecutive_win_cnt AS max_consecutive_win
FROM player_win_streak s
JOIN players p ON s.player_id = p.playerid
ORDER BY s.consecutive_win_cnt DESC
-- 如果仅需要取最高的1条，取消下方注释即可
-- LIMIT 1;

注意事项 #

如果你的实际表中matches的胜者字段名是样例中多了一个n的winnner_id，请自行调整SQL中的字段名匹配实际表结构。

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