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

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

我主要使用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将每行自连接到它的下一行，并计算它们之间的时间。然后它只是一个简单的数据聚合，以获得您想要的答案。

Answer 2

您可以使用分析函数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