Gap with MATCH_RECOGNIZE

01/11/19 22:02
Finding gaps in date intervals is a classical and potentially costly operation in the general case where intervals may overlap.
Here is a simple code pattern using match_recognize






WITH t AS (
select to_DATE( '2007-01-01', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-01-15', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-01-03', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-01-10', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-01-12', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-01-25', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-01-20', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-02-01', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-02-05', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-02-10', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-02-05', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-02-28', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-02-10', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-02-15', 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-03-01', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-03-02' , 'YYYY-MM-DD') AS end_date FROM dual
UNION all
select to_DATE( '2007-03-03', 'YYYY-MM-DD') AS start_date, to_DATE( '2007-03-16', 'YYYY-MM-DD') AS end_date FROM dual
)
select start_gap, end_gap from t
match_recognize(
order by start_date
measures max(end_date) start_gap, next(start_date) end_gap
all rows per match
pattern((A| {- B -} )+)
define A as max(end_date) < next(start_date)
);