如何计算工单（Ticket）各状态的持续时长？附尝试SQL

表结构及数据 #

有一张记录工单状态变更的表，结构和数据如下：

需求 #

计算每个工单各状态的持续时长。

尝试的错误SQL #

select t1.ticketID,t1.new_value,TRUNC( t2.changeddate ) - TRUNC( t1.changeddate )  
duration 
from ticket_table t1 
join ticket_table t2 on t1.ticketid = t2.ticketid 
and t2.changeddate < t1.changeddate 
group by t1.ticketid,t1.t1.new_value
order by t1.ticketid,t1.new_value

正确实现方法 #

使用窗口函数LEAD获取每个状态的下一次变更日期，以此计算持续时长（适用于Oracle环境）：

SELECT
    TicketID,
    new_value AS status,
    changeddate AS start_date,
    LEAD(changeddate) OVER (PARTITION BY TicketID ORDER BY changeddate) AS end_date,
    -- 计算持续天数，最后一个状态可按需处理
    CASE 
        WHEN LEAD(changeddate) OVER (PARTITION BY TicketID ORDER BY changeddate) IS NOT NULL
        THEN TRUNC(LEAD(changeddate) OVER (PARTITION BY TicketID ORDER BY changeddate)) - TRUNC(changeddate)
        ELSE TRUNC(SYSDATE) - TRUNC(changeddate) -- 未完结状态统计到当前日期
    END AS duration_days
FROM ticket_table
WHERE attribute = 'status' -- 仅筛选状态变更记录
ORDER BY TicketID, changeddate;

说明 #

1. LEAD(changeddate) OVER (PARTITION BY TicketID ORDER BY changeddate)：按工单ID分组、变更日期排序，获取当前状态的下一次变更日期，作为该状态的结束时间。
2. 持续天数通过结束日期与开始日期的差值（截断时间部分）得到。
3. 对于工单的最后一个状态，若需统计到当前时间用SYSDATE计算；若仅统计到变更节点，可去掉ELSE分支保留NULL。

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