计算并求和多行之间的时间差

Question

我有一个子表，其中包含特定父记录的状态历史记录。

该表为：

Parent_id NUMBER(38)
Date_Created  DATE
Status VARCHAR2(15)

示例数据：

1, sysdate-20, REQ
1, sysdate-10, INPRG
1, sysdate-5, WAIT
1, sysdate-2, INPRG
1, sysdate, COMP

对于任何特定的parent_id，如何计算parent_id 处于特定状态的总时间？鉴于计算是创建下一个状态的日期减去创建记录的日期。请记住，该状态可能会出现多次。

对于示例数据，如何计算记录处于“INPRG”状态的总时间？

它必须完全在 Oracle SQL 中完成。没有函数、过程、包等。

提前致谢。

Answer 1

您可以使用分析函数 LEAD 和 LAG 访问结果集中下一行或上一行的数据。这样的东西会给你每个状态的总时间

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 parent_id, sysdate-20 date_created, 'REQ' status from dual
  3    union all
  4    select 1, sysdate-10, 'INPRG' from dual
  5    union all
  6    select 1, sysdate-5, 'WAIT' from dual
  7    union all
  8    select 1, sysdate-2, 'INPRG' from dual
  9    union all
 10    select 1, sysdate, 'COMP' from dual
 11  )
 12  select parent_id,
 13         status,
 14         sum(time_in_status)
 15    from (
 16      select parent_id,
 17             date_created,
 18             nvl(lead(date_created) over
 19                          (partition by parent_id
 20                               order by date_created),
 21                 sysdate) next_status_date,
 22             nvl(lead(date_created) over
 23                          (partition by parent_id
 24                               order by date_created),
 25                 sysdate) -
 26               date_created time_in_status,
 27             status
 28        from t)
 29*  group by parent_id, status
SQL> /

 PARENT_ID STATU SUM(TIME_IN_STATUS)
---------- ----- -------------------
         1 REQ                    10
         1 COMP                    0
         1 WAIT                    3
         1 INPRG                   7

Answer 2

我主要使用 SQL Server，而不是 Oracle，所以如果我的语法有点偏差，请原谅我...

with base as (
  select Parent_id, Date_Created, Status,
    row_number() over(partition by Parent_id order by Date_Created) as 'row'
  from Table
)
select Parent_id, Status, sum(timeInStatus)
from (
  select this.Parent_id, this.Status,
    next.Date_Created-this.Date_Created as 'timeInStatus'
  from base this
    join base next on this.Parent_id=next.Parent_id
                  and this.row=next.row-1
  ) t
where Status = 'INPRG'
group by Parent_id, Status

基本概念是利用 row_number 将每一行自连接到下一行并计算之间的时间他们。然后只需对这些数据进行简单的聚合即可获得您想要的答案。