Detect and merge date range successive overlaps in SQL

debugcn 投稿 Dev

Anirudh

I have a need to detect and combine overlapping date ranges in a table but only in successive rows, non-succesive overlaps are to be ignored.

CREATE TABLE konto (konto_nummer     INTEGER, start_datum DATE, end_datum DATE); 
INSERT INTO konto VALUES (1,   '2020-01-01 00:00:00.000000', '2020-01-10 00:00:00.000000');
INSERT INTO konto VALUES (1,   '2020-01-12 00:00:00.000000',    '2020-01-20 00:00:00.000000');
INSERT INTO konto VALUES (2,    '2020-01-01 00:00:00.000000',   '2020-01-10 00:00:00.000000');
INSERT INTO konto VALUES (2,    '2020-01-05 00:00:00.000000',   '2020-01-20 00:00:00.000000');
INSERT INTO konto VALUES (2,    '2020-01-15 00:00:00.000000',   '2020-01-25 00:00:00.000000');
INSERT INTO konto VALUES (2,    '2020-02-05 00:00:00.000000',   '2020-02-20 00:00:00.000000');
INSERT INTO konto VALUES (3,    '2020-01-01 00:00:00.000000',   '2020-01-25 00:00:00.000000');
INSERT INTO konto VALUES (4,    '2020-04-01 00:00:00.000000',   '2020-04-10 00:00:00.000000');
INSERT INTO konto VALUES (4,    '2020-04-05 00:00:00.000000',   '2020-04-15 00:00:00.000000');
INSERT INTO konto VALUES (4,    '2020-04-16 00:00:00.000000',   '2020-04-25 00:00:00.000000');
INSERT INTO konto VALUES (4,    '2020-04-20 00:00:00.000000',   '2020-04-30 00:00:00.000000');

The same colored rows are having successive overlaps.

I tried the following

    SELECT
    ROW_NUMBER () OVER (ORDER BY konto_nummer, start_datum, end_datum) AS RN,
    konto_nummer,
    start_datum,
    end_datum,
    MAX(end_datum) OVER (PARTITION BY konto_nummer ORDER BY start_datum, end_datum ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Previousend_datum
   FROM konto;

But it combines also non-consecutive overlaps.

Mike Organek

Gaps and Islands has multiple steps.

First, mark the gaps

with mark as (
  select *, 
         lag(end_datum) over w
           not between start_datum and end_datum as island
    from konto
  window w as (partition by konto_nummer
                   order by start_datum, end_datum)
),

Then, number the islands

 grps as (
  select *, 
         sum(coalesce(island, true)::int) over w as grpnum 
    from mark
  window w as (partition by konto_nummer
                   order by start_datum, end_datum)
)

Then aggregate by group

select konto_nummer, 
       min(start_datum) as start_datum, 
       max(end_datum) as end_datum
  from grps
 group by konto_nummer, grpnum
 order by 1, 2, 3;

Working fiddle here.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]