MySQL如何统计球员比赛结果的连续streak（连胜/连败/连平）场次

初始查询优化建议 #

• 移除派生表内的无效排序：绝大多数关系型数据库不会保留子查询/派生表内部的ORDER BY排序结果给外层使用，嵌套在子查询里的排序只会增加额外计算开销，排序逻辑统一放到窗口计算环节即可。
• 简化查询嵌套：原查询两层嵌套的逻辑可以合并，比赛结果的判断直接在三表关联层计算即可，减少中间表扫描成本。
• 调整排序方向：需要按比赛时间从早到晚遍历统计，直接使用球员ID升序、比赛日期升序的排序规则即可，不需要先按日期倒序排再反向遍历。

连续结果streak统计实现 #

连续同值分段计数属于典型的空隙与岛屿（gaps-and-islands）SQL场景，核心逻辑是给每位球员每一段连续相同的比赛结果分配唯一分组标识，再按分组聚合得到连续场次长度，支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）可直接用以下实现：

WITH base_match_result AS (
    -- 关联三表生成基础结果集：球员、比赛日期、单场结果
    SELECT
        j.nickname AS player,
        p.day,
        CASE
            WHEN r.team = p.res THEN 'win'
            WHEN p.res = 'e' THEN 'draw'
            ELSE 'lose'
        END AS result
    FROM rosters r
    INNER JOIN matches p ON r.match = p.id
    INNER JOIN players j ON r.player = j.id
),
streak_group_mark AS (
    SELECT
        player,
        result,
        -- 两个行号的差值相同即代表属于同一段连续相同结果
        ROW_NUMBER() OVER (PARTITION BY player ORDER BY day ASC)
        - ROW_NUMBER() OVER (PARTITION BY player, result ORDER BY day ASC) AS group_id
    FROM base_match_result
)
-- 按分组聚合得到每段连续结果的场次
SELECT
    player,
    result,
    COUNT(*) AS streak
FROM streak_group_mark
GROUP BY player, result, group_id
ORDER BY player, MIN(day) ASC;

结果使用说明 #

• 上述查询直接返回需要的player/result/streak三个字段，每一行对应球员某一段连续相同结果的长度，例如球员John连续4场赢球，会返回('john', 'win', 4)的记录。
• 基于该结果集可以直接计算各类统计指标：
  • 最高连胜：按球员分组，取result='win'记录的最大streak值
  • 最长连败：按球员分组，取result='lose'记录的最大streak值
  • 最长连续不胜：按球员分组，取result in ('draw','lose')记录的最大streak值

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