A table a, Oracle data are as follows:

I want to TAB in the sum of duration, statistics and logic as follows

Want to how to write a query, please?

CodePudding user response:

Blind to write, but the same time as the mean

 with tab2 as (
Select a t1. *, 
The case when the t1. Start_time between 
Lag (t1 start_time) over (order by t1. Start_time) and lag (t1) end_time) over (order by t1. Start_time) 
Then 0 
The else 1 
End GRP 
The from tab1 t1 
) 
, tab3 as (
Select a t1. *, 
The sum (NVL (t1) GRP, 0)) over (order by t1. Start_time) grp_flag 
The from tab2 t1 
) 
Select a t1. *, 
To_char (sum (t1) end_time - t1) start_time) over (partition by t1. Grp_flag) + trunc (sysdate), 'hh24: mi: ss') SSSSS 
The from tab3 t1 
;