Merging date intervals with MATCH_RECOGNIZE

27/04/19 10:12
Merging 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

SELECT *
FROM …


MATCH_RECOGNIZE (
    PARTITION BY list of keys
    ORDER BY start_dat, end_dat
    MEASURES first(start_dat) AS start_dat, max(end_dat) AS end_dat
    PATTERN(merged* strt)
    DEFINE
        merged AS max(end_dat) >= next(start_dat)
);