求大神相助！！！oracle去除重复时间段

[qq_41493762]

寻求高手相助~

我有如下数据 （设备编号，启动时间FROM, 启动时间TO

设备编号 FROM TO
001 2019-12-2114:30:00 2019-12-21 19:30:00
001 2019-12-21 18:30:32 0 2019-12-21 20::30:00
001 2019-12-22 09:00:00 2019-12-22 23:00:10
001 2019-12-22 23:30:00
002 2019-12-21 12:00:00 2019-12-21 13:00:00
002 2019-12-21 15:30:00 2019-12-21 17:30:00
002 2019-12-22 21:00:00

假如现在的SYSDATE 是 2019-12-23 08:30, 我需要的结果是去除各设备启动时间的重复时间段后的 （各设备编号别/日期别/启动时间之和--- 每天最大值 24小时）

001 2019-12-21 6小时 (计算过程：（19：30 - 14：30） + （20：30 - 19：30（因18：30 ~19：30是重复）
001 2019-12-22 23.5 小时 （计算过程 ：（23：00 - 09：00) + （08：30 - 23：30）【TO时间空的用SYSDATE 计算】）
002 2019-12-21 3 小时 （计算过程 ： （13：00 -12：00） + （17：30 - 15：30））
002 2019-12-22 11.5 小时 （计算过程： （12-23 08：30） - 12-22 21：00）

请问这样的Query 应该怎么写？

谢谢， 在线等，奉上我所有的20积分

[一名即将失业的db]

引用 3 楼 qq_41493762 的回复:

[quote=引用 2 楼 biubiu灰 的回复:]

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
xx as
 (select min(from_date) over() as min_date,
         max(to_date) over() as max_date,
         from_date,
         to_date,
         row_number() over(order by from_date) as rn
    from tmp),
    yy as (
select max_date-min_date as minusday,
       --max_date,
       --min_date,
       from_date,
       --to_date,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select max(minusday)-sum(greatest((from_date - max_to_date),0))
   from yy
  

多谢及时回复~ 不过追问一下，按照这个query, 设备运行时间段有三个，分别是不同的时间。但结果结果集不是【日期别总运行时间】？ 我需要的是日期别总运行时间，如 2019-12-29 5 小时， 2019-12-30 15.6 小时 等。不是所有总运行时间， 这样的话，query应该怎么写？ [/quote] 跨天的怎么计算？如果跨天的要拆开有点麻烦。但是如果跨天的按照开始时间算就按照天来分组，我这个sql没有加类别分组，你可以加一下类别和天分组。跨天的看你怎么考虑吧，可以处理一下，拆成两条

[qq_41493762]

引用 4 楼 biubiu灰 的回复:

[quote=引用 3 楼 qq_41493762 的回复:] [quote=引用 2 楼 biubiu灰 的回复:]

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
xx as
 (select min(from_date) over() as min_date,
         max(to_date) over() as max_date,
         from_date,
         to_date,
         row_number() over(order by from_date) as rn
    from tmp),
    yy as (
select max_date-min_date as minusday,
       --max_date,
       --min_date,
       from_date,
       --to_date,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select max(minusday)-sum(greatest((from_date - max_to_date),0))
   from yy
  

多谢及时回复~ 不过追问一下，按照这个query, 设备运行时间段有三个，分别是不同的时间。但结果结果集不是【日期别总运行时间】？ 我需要的是日期别总运行时间，如 2019-12-29 5 小时， 2019-12-30 15.6 小时 等。不是所有总运行时间， 这样的话，query应该怎么写？ [/quote] 跨天的怎么计算？如果跨天的要拆开有点麻烦。但是如果跨天的按照开始时间算就按照天来分组，我这个sql没有加类别分组，你可以加一下类别和天分组。跨天的看你怎么考虑吧，可以处理一下，拆成两条[/quote] 是的，跨天的我用自动任务进行拆分 ， 如 2019-12-29 18:30 ~ 2019-12-30 08:00 拆分成 2019-12-29 18:30 ~ 2019-12-29 24:00:00 和 2019-12-30 00:00 ~ 2019-12-30 08:00。 所以只要能计算每天的，去除重复后的总时间就可，所以最大不会超过24小时。 但这个语句我不会写呢~~~~

[一名即将失业的db]

引用 5 楼 qq_41493762 的回复:

[quote=引用 4 楼 biubiu灰 的回复:] [quote=引用 3 楼 qq_41493762 的回复:] [quote=引用 2 楼 biubiu灰 的回复:]

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
xx as
 (select min(from_date) over() as min_date,
         max(to_date) over() as max_date,
         from_date,
         to_date,
         row_number() over(order by from_date) as rn
    from tmp),
    yy as (
select max_date-min_date as minusday,
       --max_date,
       --min_date,
       from_date,
       --to_date,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select max(minusday)-sum(greatest((from_date - max_to_date),0))
   from yy
  

多谢及时回复~ 不过追问一下，按照这个query, 设备运行时间段有三个，分别是不同的时间。但结果结果集不是【日期别总运行时间】？ 我需要的是日期别总运行时间，如 2019-12-29 5 小时， 2019-12-30 15.6 小时 等。不是所有总运行时间， 这样的话，query应该怎么写？ [/quote] 跨天的怎么计算？如果跨天的要拆开有点麻烦。但是如果跨天的按照开始时间算就按照天来分组，我这个sql没有加类别分组，你可以加一下类别和天分组。跨天的看你怎么考虑吧，可以处理一下，拆成两条[/quote] 是的，跨天的我用自动任务进行拆分 ， 如 2019-12-29 18:30 ~ 2019-12-30 08:00 拆分成 2019-12-29 18:30 ~ 2019-12-29 24:00:00 和 2019-12-30 00:00 ~ 2019-12-30 08:00。 所以只要能计算每天的，去除重复后的总时间就可，所以最大不会超过24小时。 但这个语句我不会写呢~~~~ [/quote]

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
    zz as (
select distinct 
       greatest(from_date,to_date(to_char(trunc(to_date-level+1),'yyyymmdd'),'yyyymmdd hh24:mi:ss')) from_date,
       least(to_date,to_date(to_char(trunc(to_date-level+2),'yyyymmdd'),'yyyymmdd hh24:mi:ss')) to_date
  from tmp 
 connect by level-1<=trunc(to_date)-trunc(from_date)),
xx as
 (select min(from_date) over(partition by trunc(from_date)) as min_date,
         max(to_date) over(partition by trunc(from_date)) as max_date,
         trunc(from_date) day1,
         from_date,
         to_date
    from zz),
    yy as (
select max_date-min_date as minusday,
       from_date,
       day1,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select round(max(minusday)-sum(greatest((from_date - max_to_date),0))*24,4),
        day1
   from yy
   group by day1
  

[一名即将失业的db]

第一个的起始时间不应该是6.30？你这是计算总耗时吧，按照常规理解不是最迟减最早，然后去除中间不运行的，反向求行不行

[一名即将失业的db]

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
xx as
 (select min(from_date) over() as min_date,
         max(to_date) over() as max_date,
         from_date,
         to_date,
         row_number() over(order by from_date) as rn
    from tmp),
    yy as (
select max_date-min_date as minusday,
       --max_date,
       --min_date,
       from_date,
       --to_date,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select max(minusday)-sum(greatest((from_date - max_to_date),0))
   from yy
  

[qq_41493762]

引用 2 楼 biubiu灰 的回复:

with tmp as
 (select sysdate - 1.75 as from_date, sysdate - 1 as to_date
    from dual
  union all
  select sysdate - 1.2 from_date, sysdate - 1.1 as to_date
    from dual
  union all
  select sysdate - 1.7 from_date, sysdate - 0.9 as to_date
    from dual),
xx as
 (select min(from_date) over() as min_date,
         max(to_date) over() as max_date,
         from_date,
         to_date,
         row_number() over(order by from_date) as rn
    from tmp),
    yy as (
select max_date-min_date as minusday,
       --max_date,
       --min_date,
       from_date,
       --to_date,
       max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date
  from xx)
 select max(minusday)-sum(greatest((from_date - max_to_date),0))
   from yy
  

多谢及时回复~ 不过追问一下，按照这个query, 设备运行时间段有三个，分别是不同的时间。但结果结果集不是【日期别总运行时间】？ 我需要的是日期别总运行时间，如 2019-12-29 5 小时， 2019-12-30 15.6 小时 等。不是所有总运行时间， 这样的话，query应该怎么写？

[nayi_224]

sql不会因为差值小而出问题，只能说明你语句错了。。。懒得看了，重写一个了

with tab0 as(
select 1 rowid_, 1 id, to_date('01 01', 'dd hh24') from_, to_date('01 03', 'dd hh24') to_ from dual union all
select 2 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 3 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 4 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 5 rowid_, 1 id, to_date('01 07', 'dd hh24') from_, to_date('01 10', 'dd hh24') to_ from dual union all
select 6 rowid_, 1 id, to_date('01 09', 'dd hh24') from_, to_date('01 10', 'dd hh24') to_ from dual union all
select 6 rowid_, 1 id, to_date('01 13', 'dd hh24') from_, to_date('01 14', 'dd hh24') to_ from dual union all
select 7 rowid_, 1 id, to_date('01 23', 'dd hh24') from_, to_date('04 08', 'dd hh24') to_ from dual
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
, tab2 as (
select t1.*,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by t1.from_
)
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from)
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;





with tab0 as(
select 1 rowid_, 1 id, to_Date('20200102092951', 'YYYYMMDDHH24MISS') from_, to_Date('20200102092952', 'YYYYMMDDHH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
, tab2 as (
select t1.*,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by t1.from_
)
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 86400
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

[一名即将失业的db]

with tmp as
 (select to_Date('20200102092951', 'YYYYMMDDHH24MISS') as from_date,
         to_Date('20200102092952', 'YYYYMMDDHH24MISS') as to_date
    from dual
  union all
  select to_Date('20200102103339', 'YYYYMMDDHH24MISS') as from_date,
         to_Date('20200102103340', 'YYYYMMDDHH24MISS') as to_date
    from dual),
zz as--跨天的拆成两天
 (select distinct greatest(from_date,
                           to_date(to_char(trunc(to_date - level + 1),
                                           'yyyymmdd'),
                                   'yyyymmdd hh24:mi:ss')) from_date,
                  least(to_date,
                        to_date(to_char(trunc(to_date - level + 2),
                                        'yyyymmdd'),
                                'yyyymmdd hh24:mi:ss')) to_date
    from tmp
  connect by level - 1 <= trunc(to_date) - trunc(from_date)),
xx as
 (select min(from_date) over(partition by trunc(from_date)) as min_date,
         --一天开始时间
         max(to_date) over(partition by trunc(from_date)) as max_date,
         --一天结束时间
         trunc(from_date) day1,
         from_date,
         to_date
    from zz),
yy as
 (select max_date - min_date as minusday,--所占时间，含非运行时间
         from_date,
         day1,
         nvl(max(to_date) over(order by from_date rows between unbounded
                  preceding and 1 preceding),--提前一个量的最大值，不包含本身（本次修改的核心代码）
             from_date) as max_to_date--求上一次结束时间
    from xx)
select round((max(minusday) - sum(greatest((from_date - max_to_date), 0))) * 24,--转换为小时
                              --当前开始时间-上一次结束时间
             4),
       day1
  from yy
 group by day1;

[qq_41493762]

你好，再追问一下， 按照这个query, 为什么以下两种情况的结果是错误的呢？ 1. 时间间隔较小的（如 20200102092951 ~ 20200102092952 ==》 计算结果不是 01/02 0.000001 ) 2. 时间间隔较大的 （如 01/07 09:00 ~10:00 和 01/07 18:00 ~20:00 ==> 计算结果不是 01/07 3小时 with tmp as ( select to_Date('20200102092951', 'YYYYMMDDHH24MISS') as from_date, to_Date('20200102092952', 'YYYYMMDDHH24MISS') as to_date from dual union all select to_Date('20200102103339', 'YYYYMMDDHH24MISS') as from_date, to_Date('20200102103340', 'YYYYMMDDHH24MISS') as to_date from dual ), zz as ( select distinct greatest(from_date,to_date(to_char(trunc(to_date-level+1),'yyyymmdd'),'yyyymmdd hh24:mi:ss')) from_date, least(to_date,to_date(to_char(trunc(to_date-level+2),'yyyymmdd'),'yyyymmdd hh24:mi:ss')) to_date from tmp connect by level-1<=trunc(to_date)-trunc(from_date)), xx as (select min(from_date) over(partition by trunc(from_date)) as min_date, max(to_date) over(partition by trunc(from_date)) as max_date, trunc(from_date) day1, from_date, to_date from zz), yy as ( select max_date-min_date as minusday, from_date, day1, max(to_date) over(order by from_date rows between unbounded preceding and current row) as max_to_date from xx) select round(max(minusday)-sum(greatest((from_date - max_to_date),0)),4)*24 , day1 from yy group by day1; 请问查询应该怎么改呢？

[qq_41493762]

引用 8 楼 nayi_224 的回复:

sql不会因为差值小而出问题，只能说明你语句错了。。。懒得看了，重写一个了

with tab0 as(
select 1 rowid_, 1 id, to_date('01 01', 'dd hh24') from_, to_date('01 03', 'dd hh24') to_ from dual union all
select 2 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 3 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 4 rowid_, 1 id, to_date('01 05', 'dd hh24') from_, to_date('01 08', 'dd hh24') to_ from dual union all
select 5 rowid_, 1 id, to_date('01 07', 'dd hh24') from_, to_date('01 10', 'dd hh24') to_ from dual union all
select 6 rowid_, 1 id, to_date('01 09', 'dd hh24') from_, to_date('01 10', 'dd hh24') to_ from dual union all
select 6 rowid_, 1 id, to_date('01 13', 'dd hh24') from_, to_date('01 14', 'dd hh24') to_ from dual union all
select 7 rowid_, 1 id, to_date('01 23', 'dd hh24') from_, to_date('04 08', 'dd hh24') to_ from dual
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
, tab2 as (
select t1.*,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by t1.from_
)
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from)
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;





with tab0 as(
select 1 rowid_, 1 id, to_Date('20200102092951', 'YYYYMMDDHH24MISS') from_, to_Date('20200102092952', 'YYYYMMDDHH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
, tab2 as (
select t1.*,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by t1.from_
)
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 86400
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

多谢及时回复， 不过追问一下，按照帮忙写的query, 为什么有如下重复时间时，会出现错误结果呢？ with tab0 as( select 1 rowid_, 1 id, to_Date('20200103004254', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all select 2 rowid_, 1 id, to_Date('20200103000001', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all select 3 rowid_, 1 id, to_Date('20200103004235', 'YYYYMMDDHH24MISS') from_, to_Date('20200103024830', 'YYYYMMDDHH24MISS') to_ from dual --union all --select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual ) , tab1 as ( select t1.rowid_, t1.id, t1.from_, nvl(t1.to_, sysdate) to_ from tab0 t1 ) , tab2 as ( select t1.*, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_), t1.to_), t1.to_) true_to from tab1 t1 where 1 = 1 order by t1.from_ ) , tab3 as( select t1.*, decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from, decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to, level from tab2 t1 connect by prior t1.rowid_ = t1.rowid_ and prior dbms_random.value is not null and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1 ) --select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1; select t1.id, trunc(t1.true_level_from) day_, sum(t1.true_level_to - t1.true_level_from) * 24 from tab3 t1 group by t1.id, trunc(t1.true_level_from) ;

[nayi_224]

排序改一下

with tab0 as(
select 1 rowid_, 1 id, to_Date('20200103004254', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103000001', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103004235', 'YYYYMMDDHH24MISS') from_, to_Date('20200103024830', 'YYYYMMDDHH24MISS') to_ from dual --union all
--select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
--select * from tab1;
, tab2 as (
select t1.*,
--lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_)
)
--select * from tab2;
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 24
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

[nayi_224]

没考虑完全包围的情况，提前处理一下。可能还有一些边界问题，看着处理吧。

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual --union all
--select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
--select * from tab_0;
, tab0 as (
select * from tab_0 t1 where not exists (
select * from tab_0 v1 
where t1.from_ > v1.from_ and t1.from_ < v1.to_ 
  and t1.to_ > v1.from_ and t1.to_ < v1.to_)
)
--select * from tab0;
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
--select * from tab1;
, tab2 as (
select t1.*,
--lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_)
)
--select * from tab2;
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 24
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

[qq_41493762]

引用 11 楼 nayi_224 的回复:

排序改一下

with tab0 as(
select 1 rowid_, 1 id, to_Date('20200103004254', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103000001', 'YYYYMMDDHH24MISS') from_, to_Date('20200103235959', 'YYYYMMDDHH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103004235', 'YYYYMMDDHH24MISS') from_, to_Date('20200103024830', 'YYYYMMDDHH24MISS') to_ from dual --union all
--select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
--select * from tab1;
, tab2 as (
select t1.*,
--lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_)
)
--select * from tab2;
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 24
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

谢谢。 不过结果还是不对呢，按照这个例子，结果应该是23.99944， 而现在结果是23.29呢？ 我试着改成 nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) true_from, 不过，当第2个和第3个日期在一起是，结果又不对。不知道排序哪里一直有问题呢？

[nayi_224]

结束时间相同会导致这个排序不稳定 nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_)), t1.from_) true_from, 要改成这样 nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_, t1.from_)), t1.from_) true_from, 其实这种数据应该在最开始就筛掉，maxover+distinct要比这个直观的多。

[chengccy]

你们搞的好复杂, 这个按找连续分组, 然后求分组的最长间隔, 最好按id汇总不就好了么

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 4 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 5 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 6 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual  
) 
SELECT ID, SUM(C)
FROM   (SELECT ID, (MAX(TO_) - MIN(FROM_)) * 24 C
         FROM   (SELECT ID, FROM_, TO_,
                         SUM(F) OVER(PARTITION BY ID ORDER BY FROM_) AS RN
                  FROM   (SELECT T.*,
                                  CASE
                                    WHEN LAG(TO_)
                                     OVER(PARTITION BY ID ORDER BY FROM_) >= FROM_ THEN
                                     0
                                    ELSE
                                     1
                                  END AS F
                           FROM   TAB_0 T))
         GROUP  BY ID, RN)
GROUP  BY ID

[qq_41493762]

引用 13 楼 nayi_224 的回复:

没考虑完全包围的情况，提前处理一下。可能还有一些边界问题，看着处理吧。

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual --union all
--select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual 
)
--select * from tab_0;
, tab0 as (
select * from tab_0 t1 where not exists (
select * from tab_0 v1 
where t1.from_ > v1.from_ and t1.from_ < v1.to_ 
  and t1.to_ > v1.from_ and t1.to_ < v1.to_)
)
--select * from tab0;
, tab1 as (
select t1.rowid_,
       t1.id,
       t1.from_,
       nvl(t1.to_, sysdate) to_
  from tab0 t1
)
--select * from tab1;
, tab2 as (
select t1.*,
--lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_) true_from,
       nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_), t1.to_), t1.to_) true_to
  from tab1 t1
 where 1 = 1
 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by  t1.to_)), t1.from_)
)
--select * from tab2;
, tab3 as(
select t1.*,
       decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from,
       decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to,
       level
  from tab2 t1
 connect by prior t1.rowid_ = t1.rowid_
 and prior dbms_random.value is not null
 and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1
)
--select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1;
select t1.id,
       trunc(t1.true_level_from) day_,
       sum(t1.true_level_to - t1.true_level_from) * 24
  from tab3 t1
 group by t1.id,
       trunc(t1.true_level_from)
;

按照下面的语句，结果还是不对呢，应该是 23.99994， 但结果是23.28472 ？ 再麻烦高手帮忙调整下可以吗？非常感谢~

[qq_41493762]

忘记贴 query了。query 如下，谢谢。 （结果应该是 23.99994， 但现在结果是23.28472 ） with tab_0 as( select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all --select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all --select 15 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 16 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual --union all --select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual ) --select * from tab_0; , tab0 as ( select * from tab_0 t1 where not exists ( select * from tab_0 v1 where t1.from_ > v1.from_ and t1.from_ < v1.to_ and t1.to_ > v1.from_ and t1.to_ < v1.to_) ) --select * from tab0; , tab1 as ( select t1.rowid_, t1.id, t1.from_, nvl(t1.to_, sysdate) to_ from tab0 t1 ) --select * from tab1; , tab2 as ( select t1.*, --lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_)), t1.from_) true_from, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_), t1.to_), t1.to_) true_to from tab1 t1 where 1 = 1 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.from_)), t1.from_) ) --select * from tab2; , tab3 as( select t1.*, decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from, decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to, level from tab2 t1 connect by prior t1.rowid_ = t1.rowid_ and prior dbms_random.value is not null and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1 ) --select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1; select t1.id, trunc(t1.true_level_from) day_, sum(t1.true_level_to - t1.true_level_from) * 24 from tab3 t1 group by t1.id, trunc(t1.true_level_from) ;

[qq_41493762]

不好意思，一直在调整排序修改的时候。一着急query 贴错了， 下面才是最终query, query 如下，谢谢。 （结果应该是 23.99994， 但现在结果是23.28472 ） with tab_0 as( select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 4 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 5 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all select 6 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual --union all --select 2 rowid_, 1 id, to_Date('20200102103339', 'YYYYMMDDHH24MISS') from_, to_Date('20200102103340', 'YYYYMMDDHH24MISS') to_ from dual ) --select * from tab_0; , tab0 as ( select * from tab_0 t1 where not exists ( select * from tab_0 v1 where t1.from_ > v1.from_ and t1.from_ < v1.to_ and t1.to_ > v1.from_ and t1.to_ < v1.to_) ) --select * from tab0; , tab1 as ( select t1.rowid_, t1.id, t1.from_, nvl(t1.to_, sysdate) to_ from tab0 t1 ) --select * from tab1; , tab2 as ( select t1.*, --lag(t1.to_) over(partition by t1.id order by t1.from_) lag_to, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_)), t1.from_) true_from, nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_), t1.to_), t1.to_) true_to from tab1 t1 where 1 = 1 order by nvl(greatest(t1.from_, lag(t1.to_) over(partition by t1.id order by t1.to_)), t1.from_) ) --select * from tab2; , tab3 as( select t1.*, decode(level, 1, t1.true_from, trunc(t1.true_from) + level - 1) true_level_from, decode(level, max(level) over(partition by t1.rowid_), true_to, trunc(t1.true_to) - (max(level) over(partition by t1.rowid_) - level - 1)) true_level_to, level from tab2 t1 connect by prior t1.rowid_ = t1.rowid_ and prior dbms_random.value is not null and level <= trunc(t1.true_to) - trunc(t1.true_from) + 1 ) --select t1.*, t1.true_level_to - t1.true_level_from from tab3 t1; select t1.id, trunc(t1.true_level_from) day_, sum(t1.true_level_to - t1.true_level_from) * 24 from tab3 t1 group by t1.id, trunc(t1.true_level_from) ;

[nayi_224]

引用 18 楼 chengccy 的回复:

你们搞的好复杂, 这个按找连续分组, 然后求分组的最长间隔, 最好按id汇总不就好了么

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 4 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 5 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235959', 'YYYYMMDD HH24MISS') to_ from dual union all
select 6 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual  
) 
SELECT ID, SUM(C)
FROM   (SELECT ID, (MAX(TO_) - MIN(FROM_)) * 24 C
         FROM   (SELECT ID, FROM_, TO_,
                         SUM(F) OVER(PARTITION BY ID ORDER BY FROM_) AS RN
                  FROM   (SELECT T.*,
                                  CASE
                                    WHEN LAG(TO_)
                                     OVER(PARTITION BY ID ORDER BY FROM_) >= FROM_ THEN
                                     0
                                    ELSE
                                     1
                                  END AS F
                           FROM   TAB_0 T))
         GROUP  BY ID, RN)
GROUP  BY ID

连续时间分组确实要比我的修数据清晰多了，然而还是要处理一下各种异常数据。

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 4 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all

select 4 rowid_, 1 id, to_Date('20200103 000002', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 002051', 'YYYYMMDD HH24MISS') to_ from dual union all

select 5 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 6 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual  
) 

SELECT ID, SUM(C)
FROM   (SELECT ID, (MAX(TO_) - MIN(FROM_)) * 24 C
         FROM   (SELECT ID, FROM_, TO_,
                         SUM(F) OVER(PARTITION BY ID ORDER BY FROM_) AS RN
                  FROM   (SELECT T.*,
                                  CASE
                                    WHEN LAG(TO_)
                                     OVER(PARTITION BY ID ORDER BY FROM_) >= FROM_ THEN
                                     0
                                    ELSE
                                     1
                                  END AS F
                           FROM   TAB_0 T))
         GROUP  BY ID, RN)
GROUP  BY ID

[chengccy]

引用 19 楼 nayi_224 的回复:

换个方式判断连续就好了, 取当前分组的第一行到前一行的最大值是否大于当前值判断

with tab_0 as(
select 1 rowid_, 1 id, to_Date('20200103 004254', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 2 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 3 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 4 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all

select 4 rowid_, 1 id, to_Date('20200103 000002', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 002051', 'YYYYMMDD HH24MISS') to_ from dual union all

select 5 rowid_, 1 id, to_Date('20200103 000001', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 235950', 'YYYYMMDD HH24MISS') to_ from dual union all
select 6 rowid_, 1 id, to_Date('20200103 004235', 'YYYYMMDD HH24MISS') from_, to_Date('20200103 024830', 'YYYYMMDD HH24MISS') to_ from dual  
)  
SELECT ID, SUM(C)
FROM   (SELECT ID, RN, (MAX(TO_) - MIN(FROM_)) * 24 C
         FROM   (SELECT ID, FROM_, TO_,
                         SUM(F) OVER(PARTITION BY ID ORDER BY FROM_) AS RN
                  FROM   (SELECT T.*,
                                  CASE
                                    WHEN MAX(TO_)
                                     OVER(PARTITION BY ID ORDER BY FROM_
                                              ROWS BETWEEN UNBOUNDED PRECEDING AND 1
                                              PRECEDING) >= FROM_ THEN
                                     0
                                    ELSE
                                     1
                                  END AS F
                           FROM   TAB_0 T))
         GROUP  BY ID, RN)
GROUP  BY ID