Jak sprawdzić, czy rekord jest kontynuacją? Część 2

w dniu
Dawno temu, w roku 2019 napisałam post z zapytaniem rozwiązującym dość często pojawiający się problem - a mianowicie jak w zapytaniu SQL ropoznać, czy wiersze w tabeli są kontunuacją poprzedniego wiersza. 

O co dokładnie chodzi? 

Zobaczmy na przykład pracownika o employee_id = 101 i sprawdźmy historię jego zatrudnienia (wszystkie przykłady korzystają ze schematu HR).
 SELECT * FROM hr.job_history   
 WHERE  employee_id in (101) ;   

 EMPLOYEE_ID START_DA END_DATE  JOB_ID       DEPARTMENT_ID  
 ----------- -------- --------  ----------  -------------  
     101     97/09/21 01/10/27  AC_ACCOUNT  110  
     101     01/10/28 05/03/15  AC_MGR      110
W 2001 roku Pani Kochhar zmieniła stanowisko z AC_ACCOUNT na AC_MGR. Niemniej cały czas była zatrudniona. Chcielibyśmy znaleźć daty zatrudnienia pracowników od - do, bez względu na to, czy w międzyczasie zmieniali stanowisko lub departament czy też nie.

W 2019 roku zaproponowałam sposób, jeszcze na starszej wersji Oracle (zdaje się że na Oracle wersja 10 lub 11), jak za pomocą funkcji analitycznych można rozwiązać ten problem. Wpis można znaleźć TUTAJ

Jednak w wersji Oracle 12c pojawiło się coś, co może znacząco uprościć tego typu analizy - MATCH_RECOGNIZE. Postanowiłam odświeżyć tamten wpis i spróbować rozwiązać ten problem właśnie za pomocą funkcji MATCH_RECOGNIZE.

Przykłady przygotowane na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production. Korzystam z przykładowego schematu HR.

Do dzieła zatem!

Na początek dodam jeszcze jeden rekord do JOBS_HISTORY. Tym razem Pani Kochhar zmieni departament.
 insert into hr.job_history values(101, to_date('20050316', 'yyyymmdd'), sysdate, 'IT_PROG', 60);
 SELECT * FROM hr.job_history 
 where employee_id in (101) ;  
 EMPLOYEE_ID START_DA END_DATE JOB_ID     DEPARTMENT_ID
----------- -------- -------- ---------- -------------
        101 97/09/21 01/10/27 AC_ACCOUNT           110
        101 01/10/28 05/03/15 AC_MGR               110
        101 05/03/16 23/01/16 IT_PROG               60
A teraz sprawdźmy możliwości MATCH_RECOGNIZE:
 SELECT employee_id, start_date, end_date,department_id, cls, mno  
  FROM hr.job_history   
   MATCH_RECOGNIZE (  
      PARTITION BY employee_id   
      ORDER BY start_date   
      MEASURES  
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ALL ROWS PER MATCH        
  PATTERN (str e+)    
  DEFINE   
   e AS start_date = prev(end_date) +1)  
  order by employee_id, start_date, mno;  
  /  
 
EMPLOYEE_ID START_DA END_DATE DEPARTMENT_ID CLS     MNO
----------- -------- -------- ------------- ----- -----
        101 97/09/21 01/10/27           110 STR       1
        101 01/10/28 05/03/15           110 E         1
        101 05/03/16 23/01/16            60 E         1
        176 06/03/24 06/12/31            80 STR       1
        176 07/01/01 07/12/31            80 E         1
Wow, działa :) 

W powyższym zapytaniu pokazaliśmy tylko tych pracowników, którzy zmieniali czy statnowisko czy departament (mają wiersze z kontynuacją).  
Ale możemy również pokazać wszystkie wiersze z tabeli jobs_history z zaznaczeniem, które rekordu mają kontynuację a które nie. 
 SELECT employee_id, start_date, end_date,department_id, cls, mno  
  FROM hr.job_history   
   MATCH_RECOGNIZE (  
      PARTITION BY employee_id   
      ORDER BY start_date   
      MEASURES  
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ALL ROWS PER MATCH WITH UNMATCHED ROWS   
  PATTERN (str e+)    
  DEFINE   
   e AS start_date = prev(end_date) +1)  
  order by employee_id, start_date, mno;  
  /  
EMPLOYEE_ID START_DA END_DATE DEPARTMENT_ID CLS     MNO
----------- -------- -------- ------------- ----- -----
        101 97/09/21 01/10/27           110 STR       1
        101 01/10/28 05/03/15           110 E         1
        101 05/03/16 23/01/16            60 E         1
        102 01/01/13 06/07/24            60            
        114 06/03/24 07/12/31            50            
        122 07/01/01 07/12/31            50            
        176 06/03/24 06/12/31            80 STR       1
        176 07/01/01 07/12/31            80 E         1
        200 95/09/17 01/06/17            90            
        200 02/07/01 06/12/31            90            
        201 04/02/17 07/12/19            20            

11 rows selected.
A możemy również pokazać wyniki zagregowane do jednego wiersza per pracownik. 
 SELECT employee_id, start_date, end_date,department_id  
  FROM hr.job_history   
   MATCH_RECOGNIZE (  
      PARTITION BY employee_id   
      ORDER BY start_date   
      MEASURES  
        start_date as start_date,  
        end_Date as end_date,   
        department_id as department_id,  
        MATCH_NUMBER() AS mno,  
        CLASSIFIER() AS cls  
  ONE ROW PER MATCH   
  PATTERN (str e+)    
  DEFINE   
   e AS start_date = prev(end_date) +1)  
  order by employee_id, start_date, mno;  
  /  
 EMPLOYEE_ID START_DA END_DATE DEPARTMENT_ID
----------- -------- -------- -------------
        101 05/03/16 23/01/16            60
        176 07/01/01 07/12/31            80
Funkcjonalność MATCH_RECOGNIZE daje niesamowite możliwości! Przyznam, że początki mogą być trudne. Ale wystarczy zrobić kilka przykładów - w którymś momencie wszystko ułoży się w głowie w sensowną całość i wtedy otworzą się przed Wami nowe, ogromne możliwości analizy zbiorów danych w prawie nieograniczony sposób i bez uciekania się w "sztuczki" SQLowe!

Jak zacząć?
Zerknijcie na początek na poniższe źródła:


Powodzenia :)

Komentarze

Prześlij komentarz