如何基于当前与下一个事件计算动作持续时长？SQL技术问询

问题背景 #

现有一张名为activity_log的表，包含time（动作开始时间）和action（动作名称）两列，原始数据如下：

需要获取每个连续动作段的起止时间：以当前动作段的最早开始时间为time_start，下一个不同动作的开始时间为time_end。例如walking动作的期望输出为：

SQL实现方案 #

通用方案（获取所有动作的起止时间） #

WITH consecutive_actions AS (
    SELECT 
        time,
        action,
        -- 标记当前行是否为连续相同动作的第一条记录
        CASE 
            WHEN LAG(action) OVER (ORDER BY time) != action 
                OR LAG(action) OVER (ORDER BY time) IS NULL 
            THEN 1 
            ELSE 0 
        END AS is_first_in_group
    FROM activity_log
),
action_segments AS (
    SELECT 
        time AS time_start,
        action
    FROM consecutive_actions
    WHERE is_first_in_group = 1
)
SELECT 
    time_start,
    LEAD(time_start) OVER (ORDER BY time_start) AS time_end,
    action
FROM action_segments
-- 可选：过滤掉最后一个没有后续动作的记录（保留的话time_end为NULL）
WHERE LEAD(time_start) OVER (ORDER BY time_start) IS NOT NULL;

针对特定动作的方案（例如仅获取walking的起止时间） #

WITH consecutive_actions AS (
    SELECT 
        time,
        action,
        CASE 
            WHEN LAG(action) OVER (ORDER BY time) != action 
                OR LAG(action) OVER (ORDER BY time) IS NULL 
            THEN 1 
            ELSE 0 
        END AS is_first_in_group
    FROM activity_log
),
action_segments AS (
    SELECT 
        time AS time_start,
        action
    FROM consecutive_actions
    WHERE is_first_in_group = 1
)
SELECT 
    time_start,
    LEAD(time_start) OVER (ORDER BY time_start) AS time_end
FROM action_segments
WHERE action = 'walking'
AND LEAD(time_start) OVER (ORDER BY time_start) IS NOT NULL;

逻辑说明 #

1. 标记连续动作段
   使用LAG()窗口函数对比当前行与上一行的动作名称，标记出每个连续动作段的第一条记录。
2. 提取动作段起始时间
   筛选出所有标记为"连续段第一条"的记录，得到每个动作的起始时间。
3. 获取动作结束时间
   使用LEAD()窗口函数获取下一个动作段的起始时间，作为当前动作的结束时间。

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