Overlapping intervals - split - match_recognize

match_recognize - only.sql

with
 t(name,id1,id2) as (
    select 'A', to_date('01.01.2020','dd.mm.yyyy'),to_date('01.02.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('12.12.2021','dd.mm.yyyy'),to_date('20.05.2022','dd.mm.yyyy') from dual union all
    select 'A', to_date('12.12.2022','dd.mm.yyyy'),to_date('30.01.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('01.03.2023','dd.mm.yyyy'),to_date('01.04.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('01.01.2020','dd.mm.yyyy'),to_date('01.01.2024','dd.mm.yyyy') from dual union all
    select 'В', to_date('01.02.2020','dd.mm.yyyy'),to_date('01.04.2023','dd.mm.yyyy') from dual union all
    select 'В', to_date('01.05.2023','dd.mm.yyyy'),to_date('01.01.2024','dd.mm.yyyy') from dual union all
    select 'А', to_date('01.02.2024','dd.mm.yyyy'),to_date('01.03.2024','dd.mm.yyyy') from dual union all
    select 'А', to_date('01.04.2024','dd.mm.yyyy'),to_date('01.05.2024','dd.mm.yyyy') from dual
)
select *
from t
match_recognize (
   partition by name
   order by id1
   measures
      min(id1) as start_
     ,max(id2) as end_
     ,match_number() as mn
   pattern ( (a b+ c*)| (a))
   define
      b as id1 <= max(a.id2) and id2 > min(a.id1)
     ,c as id1 <= max(b.id2) and id2 > min(b.id1)
)
/

overlapping_intervals.sql

with
 t(name,id1,id2) as (
    select 'A', to_date('01.01.2020','dd.mm.yyyy'),to_date('01.02.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('12.12.2021','dd.mm.yyyy'),to_date('20.05.2022','dd.mm.yyyy') from dual union all
    select 'A', to_date('12.12.2022','dd.mm.yyyy'),to_date('30.01.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('01.03.2023','dd.mm.yyyy'),to_date('01.04.2023','dd.mm.yyyy') from dual union all
    select 'A', to_date('01.01.2020','dd.mm.yyyy'),to_date('01.01.2024','dd.mm.yyyy') from dual union all
    select 'В', to_date('01.02.2020','dd.mm.yyyy'),to_date('01.04.2023','dd.mm.yyyy') from dual union all
    select 'В', to_date('01.05.2023','dd.mm.yyyy'),to_date('01.01.2024','dd.mm.yyyy') from dual union all
    select 'А', to_date('01.02.2024','dd.mm.yyyy'),to_date('01.03.2024','dd.mm.yyyy') from dual union all
    select 'А', to_date('01.04.2024','dd.mm.yyyy'),to_date('01.05.2024','dd.mm.yyyy') from dual
)
,time_points as (
    select distinct *
    from  t
    unpivot (dt for r in (id1,id2))
    order by name,dt,r
)
,intervals as (
    select *
    from (
       select name,dt as dt1,lead(dt)over(partition by name order by dt) as dt2
       from time_points
    ) i
    where exists(select * from t where t.id1<i.dt2 and t.id2>i.dt1 and i.name=t.name)
)
select *
from intervals i
match_recognize (
   partition by name
   order by dt1
   measures
      first(dt1) as start_
     ,last(dt2)  as end_
   pattern (a b*)
   define
      b as dt1=prev(dt2)
)

https://dbfiddle.uk/fhgZ-v5V

https://dbfiddle.uk/V6viq75g