使用BigQuery查询每个人的最长连败记录

假设你的表名为match_results，包含name（姓名）、date（比赛日期）、result（比赛结果，'L'代表失败）字段，以下是针对百万级数据的高效查询方案：

通用SQL（支持窗口函数：MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH ranked_data AS (
    SELECT
        name,
        result,
        -- 用胜利事件分割连续失败段
        SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END) OVER (
            PARTITION BY name 
            ORDER BY date ASC
        ) AS win_segment
    FROM match_results
),
streak_stats AS (
    SELECT
        name,
        win_segment,
        COUNT(*) AS current_streak
    FROM ranked_data
    WHERE result = 'L'
    GROUP BY name, win_segment
)
SELECT
    name,
    COALESCE(MAX(current_streak), 0) AS longest_lose_streak
FROM streak_stats
GROUP BY name
ORDER BY longest_lose_streak DESC;

MySQL 5.x兼容方案（无窗口函数） #

如果你的数据库版本不支持窗口函数，用变量实现：

SELECT
    name,
    COALESCE(MAX(streak), 0) AS longest_lose_streak
FROM (
    SELECT
        name,
        @streak := CASE
            WHEN @prev_name = name AND result = 'L' THEN @streak + 1
            WHEN @prev_name = name AND result = 'W' THEN 0
            ELSE IF(result = 'L', 1, 0)
        END AS streak,
        @prev_name := name
    FROM match_results,
         (SELECT @prev_name := '', @streak := 0) AS init
    ORDER BY name, date
) AS temp
WHERE streak > 0
GROUP BY name
ORDER BY longest_lose_streak DESC;

关键逻辑说明 #

• 用胜利记录作为分割点，把连续的失败归为同一个分段
• 统计每个分段的失败次数，再取每个用户的最大值
• COALESCE确保从未失败的用户返回0而非NULL

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