如何在MySQL中累加多次Review与Review in Progress的状态变更时间差

要计算每次从Review变为Review in Progress的时间差总和，你需要匹配每一对连续的状态变更记录，而非直接取首尾时间。可以借助窗口函数LAG()关联上一条状态记录来实现，具体方案如下：

假设表结构说明 #

假设你的表（以下用status_log代指，可替换为实际表名）包含核心字段：

• id: 业务关联ID
• identifier: 唯一标识
• status: 状态字段（值包含Review、Review in Progress）
• created: 状态变更的时间戳字段

完整SQL语句 #

SELECT SUM(TIMESTAMPDIFF(DAY, prev_created, created)) AS total_review_duration
FROM (
    SELECT 
        created,
        status,
        -- 获取同分组内上一条记录的状态
        LAG(status) OVER (PARTITION BY id, identifier ORDER BY created) AS prev_status,
        -- 获取同分组内上一条记录的时间戳
        LAG(created) OVER (PARTITION BY id, identifier ORDER BY created) AS prev_created
    FROM status_log
    WHERE id = 555755 AND identifier = '64230be02cee496759ecf80d'
    -- 仅保留目标状态记录，减少计算量
    AND status IN ('Review', 'Review in Progress')
) AS status_pairs
-- 筛选出符合"Review→Review in Progress"的变更记录
WHERE prev_status = 'Review' AND status = 'Review in Progress';

代码说明 #

1. 内层子查询：

   • 通过LAG()窗口函数，按id和identifier分组、按created排序，获取每条记录对应的上一条状态和时间戳。
   • 提前过滤出仅包含目标状态的记录，减少不必要的计算开销。

2. 外层查询：

   • 精准筛选出状态从Review切换到Review in Progress的记录对。
   • 用TIMESTAMPDIFF(DAY, ...)计算单次变更的天数差，再通过SUM()累加所有差值得到总时长。

如果需要更精确的时间单位（如小时、分钟），只需将TIMESTAMPDIFF的第一个参数替换为HOUR、MINUTE即可。

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