自连接SQL表仅返回单条结果：Jira状态时长计算问题

问题背景 #

需要计算Jira Issue在各状态的停留时长，Issue可能多次往返同一状态。日志表记录状态变更事件，需匹配当前状态结束记录与最近的前置状态变更记录来计算时长：

• 若当前状态变更的起始状态是New，则以Issue创建时间IssueCreatedDate作为时长计算的起始点
• 其他情况取当前记录Created与最近的前置状态变更记录Created的时间差
• 此前自连接查询返回重复行，间隙岛法、Min/Max连接方式性能极差，无法正确去重计算

示例数据 #

优化后的SQL代码 #

使用窗口函数LAG()按Issue分组并按时间排序，直接获取每条记录的最近前置变更记录，避免自连接的重复和性能问题：

SELECT 
    IssueKey,
    HistoryId,
    IssueId,
    IssueCreatedDate,
    ItemFromString,
    ItemToString,
    Created,
    -- 获取当前记录的上一条同Issue的状态变更记录的创建时间
    LAG(Created) OVER (PARTITION BY IssueKey ORDER BY Created, HistoryID) AS PrevCreated,
    -- 计算停留时长（单位：天）
    CASE
        -- 第一条记录且起始状态是New，用Issue创建时间计算
        WHEN LAG(Created) OVER (PARTITION BY IssueKey ORDER BY Created, HistoryID) IS NULL 
             AND ItemFromString LIKE '%New%'
            THEN ROUND(DATEDIFF(hour, IssueCreatedDate, Created) / 24.0, 2)
        -- 其他情况用上一条记录的创建时间计算
        WHEN LAG(Created) OVER (PARTITION BY IssueKey ORDER BY Created, HistoryID) IS NOT NULL
            THEN ROUND(DATEDIFF(hour, LAG(Created) OVER (PARTITION BY IssueKey ORDER BY Created, HistoryID), Created) / 24.0, 2)
        ELSE 0.01
    END AS Duration
FROM TableNameRedacted
WHERE IssueKey LIKE '%TPP%'
ORDER BY IssueKey, Created, HistoryID;

代码说明 #

1. LAG()窗口函数：按IssueKey分组，按Created（配合HistoryID处理同一时间的多条变更）排序，获取当前记录的上一条状态变更记录的Created时间，精准匹配最近的前置记录，无重复行
2. 时长计算逻辑：
   • 对于Issue的第一条状态变更（PrevCreated为空），如果起始状态是New，则用IssueCreatedDate到当前Created的小时差除以24得到天数
   • 其他情况直接用PrevCreated到当前Created的时间差计算时长
   • 保留两位小数，确保结果精度
3. 性能优势：窗口函数的执行效率远高于自连接和Min/Max聚合，避免了大量重复匹配和数据扫描

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