Nakładające się zakresy dat

w dniu

Na blogu poświęciłam już dwa wpisy tematowi weryfikacji pól typu DATES pod kątem kontynuacji: Jak sprawdzić, czy rekord jest "kontynuacją" poprzedniego? Cześć 1 oraz 

Kontynuacja wiersza to nie jedyny problem, z jakim spotykamy się w prawdziwym świecie. Równie często zadajemy pytanie: czy zakresy dat w wierszach się na siebie nakładają? Dzisiaj postaram się za pomocą zapytania oraz składni MATCH_RECOGNIZE odpowiedzieć na to pytanie. 

Przykłady uruchomione na schemacie HR w bazie Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Schematu HR.

Utworzę tabelę DATES_TEST
CREATE TABLE dates_test (
  id         NUMBER,
  start_date DATE,
  end_date   DATE,
  text varchar2(100)
);

Przypadek 1-   następujące po sobie wiersze 
Będziemy chcieli znaleźć takie wiersze, dla których data końcowa bieżącego wiersza jest o jeden dzień mniejsza niż data początkowa kolejnego wiersza. Lub patrząc w drugą stronę: data początkowa bieżącego wiersza jest o jeden większa niż data końcowa poprzedniego wiersza. 
Przygotujmy dane testowe: dodamy 3 wiersze z kolejnymi zakresami dat oraz dla weryfikacji – 2 wiersze z innymi zakresami dat. 

begin
    delete from dates_test;
     -- consecutive rows
    INSERT INTO dates_test VALUES (1, TO_DATE('20120101','YYYYMMDD'), TO_DATE('20120105','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (2, TO_DATE('20120106','YYYYMMDD'), TO_DATE('20120108','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (3, TO_DATE('20120109','YYYYMMDD'), TO_DATE('20120110','YYYYMMDD'), 'consecutive');
    
    -- normal rows
    INSERT INTO dates_test VALUES (90, TO_DATE('20120401','YYYYMMDD'), TO_DATE('20120404','YYYYMMDD'), 'normal');
    INSERT INTO dates_test VALUES (91, TO_DATE('20120406','YYYYMMDD'), TO_DATE('20120407','YYYYMMDD'), 'normal');

COMMIT;
end;
/
Zobaczmy dane: 
   ID START_DATE END_DATE   TEXT           
----- ---------- ---------- ---------------
    1 01-01-2012 05-01-2012 consecutive    
    2 06-01-2012 08-01-2012 consecutive    
    3 09-01-2012 10-01-2012 consecutive    
   90 01-04-2012 04-04-2012 normal         
   91 06-04-2012 07-04-2012 normal

Widzimy, że data początkowa wierszy jest o 1 większa, niż data końcowa poprzedniego wiersza.

Jak możemy znaleźć takie wiersze w tabeli i sprawdzić, czy zakresy wierszy nakładają się na siebie? W poprzednim poście pokazałam sposób z użyciem MATCH_RECGNIZE oraz zmiennej ALWAYS TRUE. Tym razem spróbujemy innym sposobem – w definicji wzorca porównam nie tylko jak poprzednio datę początkową do daty końcowej poprzedniego wiersza ale również datą końcową do daty początkowej NASTĘPNEGO wiersza. W tej wersji nie potrzebujemy stosować zmiennej ALWAYS TRUE. 
Najważniejsze jest tutaj odpowiednie posortowanie rekordów – sortuję po dacie początkowej a następnie po dacie końcowej. W definicji zaś wzorca podajemy taką weryfikację, jak w opisie przypadku: 
 - data końcowa bieżącego wiersza + 1 = data początkowa kolejnego wiersza - data początkowa bieżacego wiersza – 1 = data końcowa poprzedzającego wiersza 

UWAGA! Dla przejrzystości zapytania nie są tutaj brane pod uwagę daty końcowe o wartości null, co jest często spotykane w zakresach dat. Jeśli macie taki przypadek – po prostu na kolumnie END_DATE dodajcie funkcję NVL i ustawcie jakąś odległą datę np. 12.12.2999.

SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ALL ROWS PER MATCH   with unmatched rows
  PATTERN (consecutive+)    
  DEFINE  consecutive as  consecutive.end_date   + 1 = next(consecutive.start_date)
                      or  consecutive.start_date - 1 = prev(consecutive.end_date))
order by start_date, end_date;

Oto wyniki 😊

START_DATE END_DATE          FID SD         ED         MED        NSD               MNO CLS                ID TEXT           
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ----- ---------------
01-01-2012 05-01-2012          1 01-01-2012 10-01-2012 05-01-2012 06-01-2012          1 CONSECUTIVE         1 consecutive    
06-01-2012 08-01-2012          1 01-01-2012 10-01-2012 08-01-2012 09-01-2012          1 CONSECUTIVE         2 consecutive    
09-01-2012 10-01-2012          1 01-01-2012 10-01-2012 10-01-2012 01-04-2012          1 CONSECUTIVE         3 consecutive    
01-04-2012 04-04-2012                                                                                      90 normal         
06-04-2012 07-04-2012                                                                                      91 normal

Widzimy, że wiersze z kolejnymi zakresami dat zostały zakwalifikowane do jednego wzorca (kolumna MNO =1 dla wszystkich rekordów).

Dwa wiersze kontrolne o id 90 i 91 – nie zostały zakwalifikowane do wzorca. 

Możemy też połączyć kolejne rekordy w jeden wiersze. 

SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ONE ROW PER MATCH
  PATTERN (consecutive+)    
  DEFINE  consecutive as  consecutive.end_date   + 1 = next(consecutive.start_date)
                      or  consecutive.start_date - 1 = prev(consecutive.end_date))
;


Sprawdźmy wyniki: 
       FID SD         ED         MED        NSD               MNO CLS            
---------- ---------- ---------- ---------- ---------- ---------- ---------------
         1 01-01-2012 10-01-2012 10-01-2012 01-04-2012          1 CONSECUTIVE

Data początkowa naszego zestawu wierszy testowych to 01.01.2012 a data końcowa to 10.01.2012. Wszystko się zgadza!

Pierwszy przypadek załatwiony 😊 

Przypadek 2 – nachodzące na siebie zakresy dat 
Przypadek drugi jest równie ciekawy. Będziemy chcieli przeszukać tabelę i znaleźć wiersze, których zakresy dat na siebie nachodzą. W przypadku kolejnych wierszy data końcowa bieżącego wiesza musiała być o jeden mniejsza niż data początkowa kolejnego wiersza. W tym zaś przypadku musimy wyszukać wiersze spełniające poniższy warunek:
 - data początkowa bieżącego wiersza musi być mniejsza lub równa dacie końcowej kolejnego wiersza Znów kluczowe jest tutaj sortowanie. Wiersze sortujemy po dacie początkowej a następnie po dacie końcowej. 

Załadujmy odpowiednie dane, w których zakresy dat nakładają się na siebie. Dodatkowo zainsertujemy dwa wiersze, których zakresy dat są inne. Tak dla pewności. 

begin
    delete from dates_test;
   -- overlapping rows
    INSERT INTO dates_test VALUES (4, TO_DATE('20120201','YYYYMMDD'), TO_DATE('20120207','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (5, TO_DATE('20120206','YYYYMMDD'), TO_DATE('20120209','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (6, TO_DATE('20120208','YYYYMMDD'), TO_DATE('20120210','YYYYMMDD'), 'overlapping');
    
    -- normal rows
    INSERT INTO dates_test VALUES (90, TO_DATE('20120401','YYYYMMDD'), TO_DATE('20120404','YYYYMMDD'), 'normal');
    INSERT INTO dates_test VALUES (91, TO_DATE('20120406','YYYYMMDD'), TO_DATE('20120407','YYYYMMDD'), 'normal');

COMMIT;
end;
/

Dane w tabeli:

   ID START_DATE END_DATE   TEXT           
----- ---------- ---------- ---------------
    4 01-02-2012 07-02-2012 overlapping    
    5 06-02-2012 09-02-2012 overlapping    
    6 08-02-2012 10-02-2012 overlapping    
   90 01-04-2012 04-04-2012 normal         
   91 06-04-2012 07-04-2012 normal

Dla wiersza o id 4 mamy zakres dat nakładający się: data końcowa wiersza o ID = 4 to 07.02.2012 zaś data początkowa kolejnego wiersza, wiersza o ID = 5  jest mniejsza niż data końcowa poprzedniego wiersza. Zakresy dat nakładają się.

Spróbujmy za pomocą zapytania znaleźć takie wiersze z nakładającymi się zakresami dat:

 SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ALL ROWS PER MATCH   with unmatched rows
  PATTERN (str overlap+)    
   DEFINE  overlap as  overlap.start_date  <= prev(overlap.end_date) )
;

W definicji wzorca zadeklarowaliśmy warunek, że szukamy wierszy, dla których data poczatkowa bieżącego wiersza jest mniejsza lub równa dacie końcowej poprzedniego wiersza. We wzorcu musimy się posłużyć zmienną ALWAY TRUE (str)– „wirtualny” wiersz odniesienia dla pierwszego wiersza w zbiorze.

Sprawdźmy wyniki: 
START_DATE END_DATE          FID SD         ED         MED        NSD               MNO CLS                ID TEXT           
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ----- ---------------
01-02-2012 07-02-2012          4 01-02-2012 10-02-2012 07-02-2012 06-02-2012          1 STR                 4 overlapping    
06-02-2012 09-02-2012          4 01-02-2012 10-02-2012 09-02-2012 08-02-2012          1 OVERLAP             5 overlapping    
08-02-2012 10-02-2012          4 01-02-2012 10-02-2012 10-02-2012 01-04-2012          1 OVERLAP             6 overlapping    
01-04-2012 04-04-2012                                                                                      90 normal         
06-04-2012 07-04-2012                                                                                      91 normal

Wygląda nieźle. Daty SD (start date dla całego wzorca) oraz ED (end date dla całego wzorca) są takie same. Wszystkie wiersze zostały zakwalifikowane do jednego wzorca MNO =1 dla wierszy o ID 4, 5, 6.

Zwińmy to do jednego wiersza. 
SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  one row per match
  PATTERN (str overlap+ )    
   DEFINE  overlap as   overlap.start_date  <= prev(overlap.end_date)
                  )
;

Zobaczmy czy działa: 
      FID SD         ED         MED        NSD               MNO CLS            
---------- ---------- ---------- ---------- ---------- ---------- ---------------
         4 01-02-2012 10-02-2012 10-02-2012 01-04-2012          1 OVERLAP

Data SD (start date) to 01.02.2012 a ED (end date) to 10.02.2012. Tego właśnie oczekiwaliśmy.

Test Case 3 – kolejne zakresy + nakładające się zakresy 
Co jeśli połączymy dane testowe z przypadku 1 i 2 i będziemy chcieli znaleźć wiersze, która mają kolejne zakresy dat oraz wiersze, które mają nakładające się zakresy dat? Czy nasze zapytania dalej będą działać poprawnie?

Przygotujmy dane testowe: 
 - 3 wiersze z kolejnymi zakresami 
 - 3 wiersze z nakładającymi się zakresami 
 - 2 wiersze z innymi zakresami dla porównania 

begin
   delete from dates_test;
     -- consecutive rows
    INSERT INTO dates_test VALUES (1, TO_DATE('20120101','YYYYMMDD'), TO_DATE('20120105','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (2, TO_DATE('20120106','YYYYMMDD'), TO_DATE('20120108','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (3, TO_DATE('20120109','YYYYMMDD'), TO_DATE('20120110','YYYYMMDD'), 'consecutive');
    
    -- overlapping rows
    INSERT INTO dates_test VALUES (4, TO_DATE('20120201','YYYYMMDD'), TO_DATE('20120207','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (5, TO_DATE('20120206','YYYYMMDD'), TO_DATE('20120209','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (6, TO_DATE('20120208','YYYYMMDD'), TO_DATE('20120210','YYYYMMDD'), 'overlapping');

    -- normal rows
    INSERT INTO dates_test VALUES (90, TO_DATE('20120401','YYYYMMDD'), TO_DATE('20120404','YYYYMMDD'), 'normal');
    INSERT INTO dates_test VALUES (91, TO_DATE('20120406','YYYYMMDD'), TO_DATE('20120407','YYYYMMDD'), 'normal');


    INSERT INTO dates_test VALUES (7, TO_DATE('20120501','YYYYMMDD'), TO_DATE('20120505','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (8, TO_DATE('20120506','YYYYMMDD'), TO_DATE('20120508','YYYYMMDD'), 'consecutive');
    INSERT INTO dates_test VALUES (9, TO_DATE('20120509','YYYYMMDD'), TO_DATE('20120510','YYYYMMDD'), 'consecutive');
    
    -- overlapping rows
    INSERT INTO dates_test VALUES (10, TO_DATE('20120301','YYYYMMDD'), TO_DATE('20120307','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (11, TO_DATE('20120306','YYYYMMDD'), TO_DATE('20120309','YYYYMMDD'), 'overlapping');
    INSERT INTO dates_test VALUES (12, TO_DATE('20120308','YYYYMMDD'), TO_DATE('20120310','YYYYMMDD'), 'overlapping');
COMMIT;
end;
/

 Połączymy zapytania z testu 1 i 2: 
SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ALL ROWS PER MATCH   with unmatched rows
  PATTERN ((str overlap+ )| consecutive+ )    
  DEFINE    
    consecutive as  consecutive.end_date   + 1 = next(consecutive.start_date)
                or  consecutive.start_date - 1 = prev(consecutive.end_date),
    overlap as   overlap.start_date  <= prev(overlap.end_date)
            )    
order by mno, id;

Teraz mamy zdefiniowane dwa wzorce:
 CONSECUTIVE i OVERLAP

W klauzuli PATTERN zaczynamy od weryfikacji wzorca OVERLAP wraz ze zmienną ALWAYS TRUE. A następnie ten wzorzec łączymy za pomocą operatora LUB | z wzorcem CONSECUTIVE.

Sprawdźmy wyniki: 
START_DATE END_DATE          FID SD         ED         MED        NSD               MNO CLS                ID TEXT           
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ----- ---------------
01-01-2012 05-01-2012          1 01-01-2012 10-01-2012 05-01-2012 06-01-2012          1 CONSECUTIVE         1 consecutive    
06-01-2012 08-01-2012          1 01-01-2012 10-01-2012 08-01-2012 09-01-2012          1 CONSECUTIVE         2 consecutive    
09-01-2012 10-01-2012          1 01-01-2012 10-01-2012 10-01-2012 01-02-2012          1 CONSECUTIVE         3 consecutive    
01-02-2012 07-02-2012          4 01-02-2012 10-02-2012 07-02-2012 06-02-2012          2 STR                 4 overlapping    
06-02-2012 09-02-2012          4 01-02-2012 10-02-2012 09-02-2012 08-02-2012          2 OVERLAP             5 overlapping    
08-02-2012 10-02-2012          4 01-02-2012 10-02-2012 10-02-2012 01-03-2012          2 OVERLAP             6 overlapping    
01-03-2012 07-03-2012         10 01-03-2012 10-03-2012 07-03-2012 06-03-2012          3 STR                10 overlapping    
06-03-2012 09-03-2012         10 01-03-2012 10-03-2012 09-03-2012 08-03-2012          3 OVERLAP            11 overlapping    
08-03-2012 10-03-2012         10 01-03-2012 10-03-2012 10-03-2012 01-04-2012          3 OVERLAP            12 overlapping    
01-05-2012 05-05-2012          7 01-05-2012 10-05-2012 05-05-2012 06-05-2012          4 CONSECUTIVE         7 consecutive    
06-05-2012 08-05-2012          7 01-05-2012 10-05-2012 08-05-2012 09-05-2012          4 CONSECUTIVE         8 consecutive    
09-05-2012 10-05-2012          7 01-05-2012 10-05-2012 10-05-2012                     4 CONSECUTIVE         9 consecutive    
01-04-2012 04-04-2012                                                                                      90 normal         
06-04-2012 07-04-2012                                                                                      91 normal

Wiersze o id 1-3 oraz 7-8 zostały prawidłowo zakwalifikowane do wzorca CONSECUTIVE, wiersze 4-6 i 10-12 zostały prawidłowo zakwalifikowane do wzorca OVERLAP zaś wiersze porównawcze, 90 i 91 prawidłowo nie zostały zakwalifikowane do żadnego wzorca. 

Zwińmy wyniki do jednego wiersza per wzorzec.

SELECT  * 
  FROM dates_test   
   MATCH_RECOGNIZE (  
      ORDER BY start_date , end_Date nulls last
      MEASURES  
        final first(id) as fid,
        first(start_date ) as sd,
        final last(end_date ) as ed,
        max(end_date) as med,
        next(start_Date) nsd,
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  one row per match
  PATTERN ((str overlap+ )|consecutive+)    
  DEFINE    
    consecutive as  consecutive.end_date   + 1 = next(consecutive.start_date)
                or  consecutive.start_date - 1 = prev(consecutive.end_date),
    overlap as  overlap.start_date  <= prev(overlap.end_date)
            ) 
order by sd;

Sprawdźmy wyniki: 
       FID SD         ED         MED        NSD               MNO CLS            
---------- ---------- ---------- ---------- ---------- ---------- ---------------
         1 01-01-2012 10-01-2012 10-01-2012 01-02-2012          1 CONSECUTIVE    
         4 01-02-2012 10-02-2012 10-02-2012 01-03-2012          2 OVERLAP        
        10 01-03-2012 10-03-2012 10-03-2012 01-04-2012          3 OVERLAP        
         7 01-05-2012 10-05-2012 10-05-2012                     4 CONSECUTIVE
Widzimy, że mamy jeden zakres kolejnych dat dla rekordów z kontynuacją CONSECUTIVE oraz jeden wiersz dla rekordów z nakładającymi się zakresami dat. 

Ogólnie badanie zakresów ze względu na mnogość możliwych przypadków jest dość skomplikowane. Jeśli znajdziecie jakiś przypadek, który nie wpasuje się w powyższe zapytania – dajcie znać w komentarzu. Zbadam chętnie temat!

Komentarze

Prześlij komentarz