计算非重复时间

Question

status     状态
start_time    开始时间
end_time     结束时间
求状态下运行的时间： 各时间的累加和减去重复的时间
status               start_time                              end_time 
 q                  2008-08-13 15::00:00                 2008-08-13 15:30:00             --->30分钟
 q                  2008-08-13 15:20:00                  2008-08-13 15:40:00             ---->20分钟
 q                  2008-08-13 16:00:00                  2008-08-13 16:20:00             ----->20分钟
 结果
   q总的运行时间 30+20+20-10=60 分钟，其中10分钟为重复的时间。

Answer 1

with tt as(select 'q' status,to_date('2008-8-13 15:00:00','yyyy-mm-dd hh24:mi:ss')start_time,to_date('2008-8-13 15:30:00','yyyy-mm-dd hh24:mi:ss')end_time from dual
  union all select 'q',to_date('2008-8-13 15:20:00','yyyy-mm-dd hh24:mi:ss'),to_date('2008-8-13 15:40:00','yyyy-mm-dd hh24:mi:ss') from dual
  union all select 'q',to_date('2008-8-13 16:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2008-8-13 16:20:00','yyyy-mm-dd hh24:mi:ss') from dual)
 
select status,round(sum(ma-mi)*24*60)||'分钟' time from(
    select status,max(end_time)ma,min(start_time)mi
    from(
      select tt.*,row_number()over(partition by status order by start_time,end_time)rn
      from tt)t1
    start with not exists(
      select 1 from tt where status=t1.status 
        and start_time<t1.start_time and end_time>t1.start_time 
         --or start_time=t1.start_time and end_time<t1.end_time
         )
    connect by prior status =status and prior rn=rn-1
      and exists(
      select 1 from tt where status=t1.status 
        and start_time<t1.start_time and end_time>t1.start_time 
         --or start_time=t1.start_time and end_time<t1.end_time
         )
    group by status,connect_by_root rn)
group by statusSTATUS TIME
q 60分钟