MySQL查询：仅统计指定交互组合的学习者课程观看时长

问题出在原查询没有排除被后续启动类交互（Start/Play）打断的无效启动记录——第一个Start（2022-11-02 07:50:30）之后紧接着是另一个Start，它并没有对应的有效终止交互（Pause/Stop），但原查询错误地将后续属于第二个Start的终止时间关联给了它。

我们可以通过会话分组的方式，给每个连续的播放周期分配唯一ID，只保留有对应终止交互的会话，并仅统计每个会话的第一个启动事件时长：

WITH log_with_session AS (
    SELECT 
        *,
        -- 每遇到一个启动类交互，会话ID递增，将连续的播放动作归入同一会话
        SUM(CASE WHEN interactionType IN ('Start', 'Play') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY learnerlessonid ORDER BY createdAt) AS session_id
    FROM learner_lesson_log
),
session_end_times AS (
    SELECT 
        session_id,
        learnerlessonid,
        -- 提取每个会话中最后一个终止类交互的时间
        MAX(CASE WHEN interactionType IN ('Pause', 'Stop') THEN createdAt END) AS end_time
    FROM log_with_session
    GROUP BY session_id, learnerlessonid
)
SELECT 
    ll.learnerid AS "Learner ID",
    TIMESTAMPDIFF(SECOND, l.createdAt, s.end_time) AS "Length of Interaction",
    l.createdAt AS "Start Timestamp"
FROM log_with_session l
JOIN session_end_times s 
    ON l.session_id = s.session_id 
    AND l.learnerlessonid = s.learnerlessonid
JOIN learner_lesson ll 
    ON l.learnerlessonid = ll.learnerlessonid
WHERE 
    -- 只筛选启动类交互的记录
    l.interactionType IN ('Start', 'Play')
    -- 排除没有终止交互的会话
    AND s.end_time IS NOT NULL
    -- 每个会话只取第一个启动事件（避免连续Start/Play重复统计）
    AND l.createdAt = (
        SELECT MIN(createdAt) 
        FROM log_with_session 
        WHERE session_id = l.session_id 
          AND learnerlessonid = l.learnerlessonid
    )
ORDER BY l.createdAt ASC;

逻辑说明 #

1. 会话分组：通过窗口函数SUM()给每个启动类交互分配递增的会话ID，确保被后续启动打断的旧会话和新会话区分开。
2. 提取终止时间：按会话分组，获取每个会话中最后一个终止类交互的时间，没有终止的会话会得到NULL。
3. 筛选有效记录：只保留有终止时间的会话，且每个会话仅取第一个启动事件，避免连续启动导致的重复统计。

执行该查询后，会得到你期望的结果：

Learner ID  Length of Interaction   Start Timestamp
24               4                2022-11-02 07:51:30
24              11                2022-11-02 07:52:20

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