基于SQL统计板球比赛击球手连续3次4分（4分帽子戏法）的次数

原有实现的问题 #

• 冗余的局号判断：完全不需要用case分支硬编码局号范围，Ball_No本身已经是全局有序的投球序号
• 逻辑错误：原有实现是统计每局出现3次4分的次数，和题目要求的「连续3球得4分」逻辑完全不匹配

核心思路 #

直接利用窗口函数LEAD获取当前球之后两球的得分，一次性判断是否满足连续3球得4分的条件，代码简洁且逻辑准确，兼容MySQL 8.0+、SQLite 3.25+版本。

简洁实现代码 #

-- 统计全局连续3球都得4分的次数（符合题目括号内的定义）
SELECT COUNT(*) AS four_run_hattrick_count
FROM (
    SELECT
        Runs,
        LEAD(Runs, 1) OVER (ORDER BY Ball_No) AS next_1_ball_runs,
        LEAD(Runs, 2) OVER (ORDER BY Ball_No) AS next_2_ball_runs
    FROM cricket_scores
) temp
WHERE Runs = 4 AND next_1_ball_runs = 4 AND next_2_ball_runs = 4;

如果需求是同一个击球手连续3次击球都打出4分，只需在窗口函数中增加击球手分区即可：

-- 统计单个击球手连续3次击球都得4分的次数
SELECT COUNT(*) AS batsman_four_hattrick_count
FROM (
    SELECT
        Batsman,
        Runs,
        LEAD(Runs, 1) OVER (PARTITION BY Batsman ORDER BY Ball_No) AS next_1_hit_runs,
        LEAD(Runs, 2) OVER (PARTITION BY Batsman ORDER BY Ball_No) AS next_2_hit_runs
    FROM cricket_scores
) temp
WHERE Runs = 4 AND next_1_hit_runs = 4 AND next_2_hit_runs = 4;

样例执行结果 #

针对提供的样例数据，全局连续3球得4分的统计结果为2，对应两处符合条件的记录：

1. 704、705、706号球得分均为4
2. 901、902、903号球得分均为4

内容的提问来源于stack exchange，提问作者Sasidar B R