如何计算Bug历史记录中的等待总时长及从新建到关闭的总时长？

核心逻辑 #

要统计Bug处于Waiting状态的总时长，需先定位每个Bug进入Waiting状态的时间点，以及离开该状态的时间点（即下一次状态变更的时间），计算每段Waiting状态的持续天数后按BugID求和。有效闭环时长直接用总闭环天数减去总等待天数即可。

SQL实现代码 #

WITH BugStatusTimeline AS (
    SELECT 
        BugID,
        NewStatus,
        DateModified,
        -- 获取同Bug下的下一次状态变更时间，若为最后一条记录则用Bug的最终关闭时间
        LEAD(DateModified, 1, MAX(DateModified) OVER (PARTITION BY BugID)) 
            OVER (PARTITION BY BugID ORDER BY DateModified) AS NextStatusChangeTime,
        -- 计算总闭环天数（复用需求逻辑，用New状态的时间作为提交时间）
        DATEDIFF(DAY, 
                 MIN(CASE WHEN NewStatus = 'New' THEN DateModified END) OVER (PARTITION BY BugID),
                 MAX(DateModified) OVER (PARTITION BY BugID)) AS DaysToClose
    FROM BugActivityHistory
),
WaitingPeriods AS (
    SELECT 
        BugID,
        SUM(DATEDIFF(DAY, DateModified, NextStatusChangeTime)) AS TotalWaitingDays
    FROM BugStatusTimeline
    WHERE NewStatus = 'Waiting'
    GROUP BY BugID
)
SELECT 
    distinct_bugs.BugID,
    COALESCE(wp.TotalWaitingDays, 0) AS TotalWaitingDays,
    distinct_bugs.DaysToClose,
    distinct_bugs.DaysToClose - COALESCE(wp.TotalWaitingDays, 0) AS EffectiveDaysToClose
FROM (
    SELECT DISTINCT BugID, DaysToClose 
    FROM BugStatusTimeline
) distinct_bugs
LEFT JOIN WaitingPeriods wp ON distinct_bugs.BugID = wp.BugID;

代码说明 #

1. BugStatusTimeline CTE：

   • 用LEAD()窗口函数获取每条状态变更记录的下一次变更时间，作为当前状态的结束时间；如果是该Bug的最后一条记录，则用Bug的最终修改时间（即关闭时间）填充。
   • 通过MIN(CASE...)获取每个Bug的New状态时间，替代需求中的BugSubmitted字段，再计算总闭环天数DaysToClose。

2. WaitingPeriods CTE：

   • 筛选出所有进入Waiting状态的记录，计算每段等待时长并按BugID求和，得到每个Bug的总等待天数。

3. 最终查询：

   • 关联总闭环天数和总等待天数，计算有效闭环时长；用COALESCE()处理从未进入Waiting状态的Bug，默认等待天数为0。

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