如何基于特定条件计算History表中状态转换的累计时长

核心逻辑梳理 #

我们要抓取的是「非Finished状态记录（起点）→ 后续第一条Finished状态记录（终点）」的完整周期，且仅计算person不为空的有效记录。通过分组标记周期的方式实现：

• 先过滤掉person为空的无效记录，保留有效状态变更；
• 按时间升序排序，用累计标记生成周期组：每遇到Finished状态，就开启下一个新周期；
• 在每个周期组内提取起点、终点时间，计算时长差；
• 最后对所有有效周期的时长求和。

示例SQL代码（以PostgreSQL为例） #

WITH valid_records AS (
    -- 过滤有效记录并生成周期组标记
    SELECT 
        date,
        status,
        -- 每遇到一次Finished，组号递增，划分独立周期
        SUM(CASE WHEN status = 'Finished' THEN 1 ELSE 0 END) OVER (ORDER BY date) AS cycle_group
    FROM History
    WHERE person IS NOT NULL
),
cycle_durations AS (
    -- 提取每个周期的起止时间并计算时长
    SELECT
        cycle_group,
        -- 周期起点：组内第一条非Finished记录的时间
        MIN(CASE WHEN status != 'Finished' THEN date END) AS start_date,
        -- 周期终点：组内Finished记录的时间
        MAX(CASE WHEN status = 'Finished' THEN date END) AS end_date
    FROM valid_records
    GROUP BY cycle_group
    -- 过滤掉不完整的周期（无起点或无终点）
    HAVING MIN(CASE WHEN status != 'Finished' THEN date END) IS NOT NULL
       AND MAX(CASE WHEN status = 'Finished' THEN date END) IS NOT NULL
)
-- 求和所有周期时长（此处以秒为单位，可按需调整）
SELECT SUM(EXTRACT(EPOCH FROM (end_date - start_date))) AS total_duration_seconds
FROM cycle_durations;

代码说明 #

• valid_records CTE：通过窗口函数SUM() OVER()生成周期组号，确保每个「非Finished→Finished」的流程被划分为独立组；
• cycle_durations CTE：按组提取起止时间，过滤掉不完整的无效周期；
• 最终求和：用EXTRACT(EPOCH FROM ...)将时间差转为秒，若需其他单位（分钟/小时），可替换为DATE_PART等函数。

其他数据库适配调整 #

• MySQL（8.0+）：时间差计算用TIMESTAMPDIFF(SECOND, start_date, end_date)；
• SQL Server：时间差计算用DATEDIFF(SECOND, start_date, end_date)。

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