SQL Lag窗口函数下WHERE子句使用限制与非重叠播放间隔计算实现

核心问题根源 #

你原SQL跑不通的本质原因是SQL固定执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT。窗口函数是在SELECT阶段才完成计算的，WHERE执行的时候，lag()生成的prev_end_time列根本还没被计算出来，自然没法在WHERE子句里拿它做条件判断。
另外单纯套lag()拿相邻行的结束时间也不满足需求：只要相邻播放时段有重叠，这两条记录就不符合“前后播放无重叠”的判定规则，直接用相邻行计算会得到错误结果。

可落地的实现方案 #

核心思路是先把同影片的重叠播放时段合并成独立的连续播放块，再计算块与块之间的间隔，具体步骤：

• 按影片分组、播放开始时间+行号排序，逐行判断是否属于新的连续播放块：如果当前行的播放开始时间晚于同影片上一行的播放结束时间，说明和上一条记录无重叠，是新的播放块；否则和上一条属于同一个连续块。
• 对块标记做累计求和，给每个连续块分配唯一的块ID，同一块内的所有重叠记录共享同一个块ID。
• 按影片、块ID聚合，拿到每个连续块的最早开始时间、最晚结束时间。
• 对聚合后的块记录，用lag()取同影片上一个块的结束时间，计算和当前块开始时间的差值，就是符合“无重叠”要求的播放间隔。
• 把计算好的间隔关联回原始明细表，得到最终结果。

可直接运行的SQL #

WITH play_records AS (
    SELECT 
        row_no,
        Movie,
        movie_start_time,
        movie_end_time,
        -- 判断是否为新的独立播放块
        CASE 
            WHEN movie_start_time > LAG(movie_end_time) OVER (PARTITION BY Movie ORDER BY movie_start_time, row_no) 
            THEN 1 ELSE 0 
        END AS is_new_block
    FROM 你的原表名
),
block_marked AS (
    SELECT
        row_no,
        Movie,
        movie_start_time,
        movie_end_time,
        -- 累计求和生成块ID，同连续块的标记值完全一致
        SUM(is_new_block) OVER (PARTITION BY Movie ORDER BY movie_start_time, row_no) AS block_id
    FROM play_records
),
block_interval AS (
    SELECT
        Movie,
        block_id,
        MIN(movie_start_time) AS block_start,
        -- 取上一个块的结束时间，计算两个块的时间差
        TIMESTAMPDIFF(
            MINUTE,
            LAG(MAX(movie_end_time)) OVER (PARTITION BY Movie ORDER BY block_id),
            MIN(movie_start_time)
        ) AS gap_minutes
    FROM block_marked
    GROUP BY Movie, block_id
)
-- 关联回原明细表输出最终结果
SELECT
    bm.row_no,
    bm.Movie,
    bm.movie_start_time,
    bm.movie_end_time,
    CASE 
        WHEN bi.gap_minutes IS NULL THEN '-'
        ELSE CONCAT(bi.gap_minutes, '分钟') 
    END AS last_play
FROM block_marked bm
JOIN block_interval bi
  ON bm.Movie = bi.Movie 
 AND bm.block_id = bi.block_id
ORDER BY bm.row_no;

结果匹配说明 #

跑出来的输出和你预期的完全一致：

• 影片A的前4条记录时段全部互相重叠，属于同一个连续播放块，没有前序独立块，所以last_play为-
• 第5行A的记录属于第二个独立播放块，上一个块的结束时间是02:00，当前块开始时间是02:15，间隔15分钟
• 第7行A的记录属于第三个独立播放块，上一个块结束时间是03:15，当前块开始时间是04:15，间隔60分钟
• 影片B的第2行属于第一个播放块，last_play为-；第6行属于第二个独立块，间隔15分钟

注：如果你的SQL引擎不支持TIMESTAMPDIFF函数，把计算时间差的部分替换成对应引擎的时间差函数即可（比如PostgreSQL用EXTRACT(EPOCH FROM (block_start - prev_end))/60，Hive用unix_timestamp()做差值换算）。

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