计算视频学习启停交互时间差：MySQL查询报错与结果需求

原SQL语法错误原因 #

你写的SQL里，错误地将CASE表达式和窗口函数的OVER()子句嵌套在一起，MySQL不支持这种语法结构。窗口函数（如LEAD/LAG）需要作为独立表达式使用，不能嵌套在CASE的返回值中。同时原SQL未过滤无关的Run记录，也没有正确匹配每个开始事件对应的结束事件，逻辑上也存在问题。

正确实现SQL #

以下是满足需求的查询语句，会自动匹配每个Start/Play对应的下一个Pause/Stop，并计算时间差：

SELECT 
    ll.learnerid,
    TIMESTAMPDIFF(SECOND, lll.createdAt, next_end_time) AS `Length of interaction`,
    lll.createdAt AS start_timestamp
FROM (
    SELECT 
        learnerlessonid,
        createdAt,
        interaction,
        -- 获取当前记录之后的第一个事件时间（用于匹配结束事件）
        LEAD(createdAt) OVER (PARTITION BY learnerlessonid ORDER BY createdAt) AS next_end_time,
        -- 标记当前是否为播放开始事件
        CASE WHEN interaction IN ('Start', 'Play') THEN 1 ELSE 0 END AS is_start_event
    FROM learner_lesson_logs
    -- 过滤掉中间自动生成的Run记录，不参与时长计算
    WHERE interaction != 'Run'
) AS lll
JOIN learner_lessons ll ON ll.learnerlessonid = lll.learnerlessonid
-- 只保留开始事件，且确保对应的下一个事件是结束事件
WHERE is_start_event = 1 
  AND (SELECT interaction FROM learner_lesson_logs WHERE createdAt = next_end_time) IN ('Pause', 'Stop')
ORDER BY start_timestamp;

执行结果 #

运行上述SQL后，会得到你期望的结果：

learnerid      Length of interaction      start_timestamp
24                  4                     2022-11-02 07:51:30
24                  11                    2022-11-02 07:52:20

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