如何查询event列标记的两个事件之间的所有连续frame_id数据行

你最初的写法默认每个(game_id, play_id)分组下两个目标事件都唯一存在，当某个分组缺失任一事件、或同一事件重复出现时，计算出的边界就会出错，这就是异常表现的来源。

方案1：CTE先算边界再关联（兼容性最好） #

先通过聚合计算每个回合的两个事件边界，再关联原表筛选中间的frame：

WITH event_boundaries AS (
    SELECT
        game_id,
        play_id,
        MIN(CASE WHEN event = 'ball_snap' THEN frame_id END) AS start_frame,
        MAX(CASE WHEN event = 'punt_received' THEN frame_id END) AS end_frame
    FROM tracking
    GROUP BY game_id, play_id
)
SELECT t.*
FROM tracking t
INNER JOIN event_boundaries eb
    ON t.game_id = eb.game_id
    AND t.play_id = eb.play_id
WHERE t.frame_id BETWEEN eb.start_frame AND eb.end_frame
    AND eb.start_frame IS NOT NULL
    AND eb.end_frame IS NOT NULL
ORDER BY t.game_id, t.play_id, t.frame_id;

这个写法兼容绝大多数SQL数据库，需要更换目标事件时，只要修改CASE WHEN里的event值即可，同时会自动过滤掉没有同时出现两个目标事件的无效回合。

方案2：窗口函数实现（适合复杂场景） #

如果你的数据库支持窗口函数（MySQL 8.0+、PostgreSQL、BigQuery等），可以用状态标记的方式实现，更适合需要拆分多段事件区间的场景：

SELECT *
FROM (
    SELECT
        *,
        SUM(CASE WHEN event = 'ball_snap' THEN 1 ELSE 0 END) OVER (
            PARTITION BY game_id, play_id ORDER BY frame_id
        ) AS entered_start,
        SUM(CASE WHEN event = 'punt_received' THEN 1 ELSE 0 END) OVER (
            PARTITION BY game_id, play_id ORDER BY frame_id
        ) AS entered_end
    FROM tracking
) t
WHERE entered_start >= 1 
    -- 若不需要包含punt_received对应的行，将下方的<=1改为=0即可
    AND entered_end <= 1
ORDER BY game_id, play_id, frame_id;

如果要查询其他两个事件的区间，比如ball_snap到tackle，只要替换对应event的取值即可，逻辑通用。

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