如何在PostgreSQL中计算START对应STOP的时长并保留中间活动记录

要实现这个需求，核心是先把每条记录划分到对应的START-STOP会话组，再为每个组提取起始时间，最后仅在STOP行计算时长。以下是通用的SQL实现逻辑，适配多数主流数据库：

核心思路 #

1. 会话分组：通过累积计数为每个START到下一个STOP之间的所有记录分配唯一组ID，确保同一会话的记录归属同一组。
2. 提取起始时间：从每个会话组中提取START对应的时间戳。
3. 计算时长：仅在descript为STOP的行，用当前时间戳减去对应会话的起始时间，其他行返回NULL。

MySQL 实现代码 #

WITH session_groups AS (
    SELECT 
        descript,
        timestamp,
        -- 每次遇到START就开启新会话组
        SUM(CASE WHEN descript = 'START' THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) AS session_group
    FROM your_table
),
session_start_times AS (
    SELECT 
        session_group,
        MIN(timestamp) AS start_time  -- 每个组的START时间（组内第一条记录）
    FROM session_groups
    WHERE descript = 'START'
    GROUP BY session_group
)
SELECT 
    sg.descript,
    sg.timestamp,
    -- 仅STOP行计算时长，这里以秒为单位，可按需替换为MINUTE/HOUR等
    CASE 
        WHEN sg.descript = 'STOP' THEN TIMESTAMPDIFF(SECOND, sst.start_time, sg.timestamp)
        ELSE NULL 
    END AS duration
FROM session_groups sg
LEFT JOIN session_start_times sst ON sg.session_group = sst.session_group
ORDER BY sg.timestamp;

PostgreSQL 实现代码 #

如果使用PostgreSQL，时间差计算可以直接用时间类型相减，返回间隔格式：

WITH session_groups AS (
    SELECT 
        descript,
        timestamp,
        SUM(CASE WHEN descript = 'START' THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) AS session_group
    FROM your_table
),
session_start_times AS (
    SELECT 
        session_group,
        MIN(timestamp) AS start_time
    FROM session_groups
    WHERE descript = 'START'
    GROUP BY session_group
)
SELECT 
    sg.descript,
    sg.timestamp,
    CASE 
        WHEN sg.descript = 'STOP' THEN sg.timestamp - sst.start_time
        ELSE NULL 
    END AS duration
FROM session_groups sg
LEFT JOIN session_start_times sst ON sg.session_group = sst.session_group
ORDER BY sg.timestamp;

说明 #

• 该方法不受START与STOP之间活动记录数量的限制，无论中间有多少条ACTIVITY记录，都能正确匹配对应的起始时间。
• 最终结果会保留所有原始记录，仅STOP行填充时长，其他行的duration字段为NULL。

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