Oracle 计算启动/停止时完成任务所需的时间

亲爱的先生，

TASKID | EMPLOYEECODE| SLOT_ACTION | date

1         A             S            08/jan/2020 09:30:32 am
1         A             p            08/jan/2020 02:10:20 pm
1         A             S            09/jan/2020 09:50:50 am
1         A             P            09/jan/2020 12/50:58 pm
1         A             S            10/jan/2020 09:30:32 am
1         A             P            10/jan/2020 12/50:58 pm
1         A             S            11/jan/2020 09:30:32 am
1         A             p            11/jan/2020 02:10:20 pm
1         A             S            12/jan/2020 09:50:50 am
1         A             P            12/jan/2020 12/50:58 pm
1         A             S            13/jan/2020 09:30:32 am
1         A             P            13/jan/2020 12/50:58 pm
1         A             S            14/jan/2020 09:30:32 am
1         A             p            14/jan/2020 02:10:20 pm
1         A             S            15/jan/2020 09:50:50 am
1         A             P            15/jan/2020 12/50:58 pm
1         A             S            16/jan/2020 09:30:32 am
1         A             P            16/jan/2020 12/50:58 pm
1         A             S            17/jan/2020 09:30:32 am
1         A             p            17/jan/2020 02:10:20 pm
1         A             S            18/jan/2020 09:50:50 am
1         A             P            18/jan/2020 12/50:58 pm
1         A             S            19/jan/2020 09:30:32 am
1         A             P            19/jan/2020 12/50:58 pm
复制

。
。
就像那里的100记录
第二个最后一个记录是

1         A              S           10/feb/2020 09:30:58 am
1         A              E           10/feb/2020 05:20:25 pm 
复制

他们想要计算时间。
他们问为员工A完成takid 1需要多少时间？

哪里
S-开始工作
P-暂停工作
电子结束或完成的工作
A-员工代码
1-塔斯基

假设序列总是

S -> P -> ... -> S -> E

即每个S后面都有一个P或e，这只是一个问题

* 将开始与暂停/结束配对
* 从结尾减去开始
* 总和

有很多方法可以做到这一点。这里有一个枢轴方法让你开始:

* 为每个S和P/E分配一个row_number()，按日期排序
* 地图E -> P
* 为每个emp、任务和rn调整日期

with rws as (
  select 1 id, 'A' emp, 'S' state, to_date ( '08/jan/2020 09:30:32 am', 'dd/mon/yyyy hh:mi:ss am' ) dt from dual union all
  select 1 id, 'A' emp, 'P' state, to_date ( '08/jan/2020 02:10:20 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
  select 1 id, 'A' emp, 'S' state, to_date ( '09/jan/2020 09:50:50 am', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
  select 1 id, 'A' emp, 'P' state, to_date ( '09/jan/2020 12/50:58 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
  select 1 id, 'A' emp, 'S' state, to_date ( '10/jan/2020 09:30:32 am', 'dd/mon/yyyy hh:mi:ss am' ) from dual union all
  select 1 id, 'A' emp, 'E' state, to_date ( '10/jan/2020 12/50:58 pm', 'dd/mon/yyyy hh:mi:ss am' ) from dual
), rks as (
  select id, emp, 
         dt,
         case 
           when state in ('E','P') then 'P' 
           else 'S' 
         end state, 
         row_number() over ( 
           partition by case 
             when state in ('E','P') then 'P' 
             else 'S' 
           end 
           order by dt 
         ) rn
  from   rws r
)
  select * from rks
  pivot (
    min ( dt ) for state in ( 'S' st, 'P' en )
  );

ID    EMP    RN    ST                      EN                     
    1 A          1 08-JAN-2020 09:30:32    08-JAN-2020 14:10:20    
    1 A          2 09-JAN-2020 09:50:50    09-JAN-2020 12:50:58    
    1 A          3 10-JAN-2020 09:30:32    10-JAN-2020 12:50:58 
复制

从这里开始，您需要做的就是对差异进行分组和求和。