如何计算日志中BUG从OPEN到FIXED/REQUEST CLOSE的时间差

你的核心需求是按BUG每次打开的周期独立计算耗时，原有方案直接取全量最大/最小时间，无法区分BUG重复打开的多个周期，我们可以用会话窗口分组的思路实现，步骤如下：

核心逻辑 #

• 按BugID分组，按状态变更时间升序排列所有日志
• 每当检测到OldStatus = 'OPEN'的记录，就生成一个新的分组ID，同一个ID下的所有状态变更都属于同一次打开周期
• 筛选出存在结束状态（NewStatus IN ('FIXED', 'REQUEST CLOSE')）的分组，计算每个分组内首次OPEN时间到结束状态时间的差值即可

完整查询SQL #

SELECT 
    BugID,
    CAST(finish_time AS DATE) AS 日期,
    DATEDIFF(HOUR, start_time, finish_time) AS 耗时_小时
FROM (
    SELECT 
        BugID,
        group_id,
        MIN(CASE WHEN OldStatus = 'OPEN' THEN CurrentTime END) AS start_time,
        MAX(CASE WHEN NewStatus IN ('FIXED', 'REQUEST CLOSE') THEN CurrentTime END) AS finish_time
    FROM (
        SELECT *,
            -- 每次遇到OPEN就生成新的分组ID
            SUM(CASE WHEN OldStatus = 'OPEN' THEN 1 ELSE 0 END) 
                OVER (PARTITION BY BugID ORDER BY CurrentTime ROWS UNBOUNDED PRECEDING) AS group_id
        FROM Log
    ) t1
    GROUP BY BugID, group_id
) t2
-- 只返回有有效结束状态的周期
WHERE finish_time IS NOT NULL AND start_time IS NOT NULL
ORDER BY BugID, start_time

结果说明 #

针对你提供的测试数据（修正最后一条日志的时间笔误为2020-01-01 09:00:00、原查询中NewString为NewStatus笔误，否则时间逻辑异常），查询会返回两条结果：

完全符合你预期的按每次打开周期独立统计耗时的要求。

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