Jak sprawdzić, czy rekord jest "kontynuacją" poprzedniego? Cześć 1

W systemach bazodanowych (aczkolwiek pewnie nie tylko w bazodanowych) często określamy początek i koniec czegoś - ważności rekordu, dat rozpoczęcia i zakończenia zatrudnienia, okresu obowiązywania faktury. Przykładów można by mnożyć. Niemniej, mimo że określamy start_date i end_date pojedynczego rekordu to może się okazać, że jeden rekord jest kontynuacją drugiego.

Na przykład zatrudniony pracownik przez 3 lata pracował w jednym zespole a następnie, bez przerwy w zatrudnieniu, pracował w innym zespole. Mimo zmiany zespołu nie przestał być pracownikiem firmy.

Przykład takich danych mamy w schemacie HR w tabeli HR.JOB_HISTORY. Dla przejrzystości przykładu wyświetlę rekordy tylko dla dwóch pracowników - 101 i 200.

 SELECT * FROM hr.job_history where employee_id in (101, 200) ;  

(Dodałam jeszcze jeden, trzeci rekord pracownikowi 101 z datą zatrudnienia do 19-DEC-19, żeby przykład był bardziej wyrazisty

INSERT INTO job_history 

VALUES (101, to_date('16-03-2005', 'dd-mm-yyyy', SYSDATE, 'AC_ACCOUNT', 110);

)

EMPLOYEE_ID	START_DATE	END_DATE	JOB_ID	DEPARTMENT_ID
101	21-SEP-97	27-OCT-01	AC_ACCOUNT	110
101	28-OCT-01	15-MAR-05	AC_MGR	110
101	16-MAR-05	19-DEC-19	AC_ACCOUNT	110
200	17-SEP-95	17-JUN-01	AD_ASST	90
200	01-JUL-02	31-DEC-06	AC_ACCOUNT	90

Widzimy, że pracownik 101 zakończył pracę na stanowisku AC_ACCOUNT, ale bez przerwy w zatrudnieniu,  rozpoczął pracę w tym samym departamencie na stanowisku AC_MGR.

Pracownik 200 natomiast pomiędzy zmianą stanowiska miał prawie rok przerwy w zatrudnieniu w firmie.

Chcielibyśmy uzyskać zapytaniem taki wynik, by pracownik, który nie miał przerwy w zatrudnieniu był wyświetlony tylko raz a daty start_date i end_date powinny być datami rozpoczęcia pracy w departamencie i zakończenia pracy w departamencie bez względu na zajmowane stanowisko.

Czyli oczekiwany wynik wyglądałby tak jak poniżej:

EMPLOYEE_ID	START_DATE	END_DATE
101	21-SEP-97	15-MAR-05
200	17-SEP-95	17-JUN-01
200	01-JUL-02	31-DEC-06

Żeby uzyskać taki wynik warto posłużyć się funkcjami analitycznymi LAG i LEAD.

W pierwszym kroku budowy zapytania chcę uzyskać informację, czy kolejne rekordy są wobec siebie kontynuacją:

Sprawdzam, czy  start_date następnego rekordu (LEAD) jest równa dacie end_date bieżącego rekordu LUB end_date poprzedniego rekordu (LAG) jest równa start_date bieżącego rekordu.

Dlaczego sprawdzam obie daty? Gdyż pierwszy rekord dla pracownika nie będzie miał poprzedniego rekordu a ostatni rekord nie będzie miał następnego. I zignorowanie tych granicznych rekordów przyniosłoby niewłaściwe wyniki. A więc nasze zapytanie badające kontynuację może wyglądać tak:

 select employee_id, start_date, end_date,  
   lead(start_date) over(partition by employee_id order by start_date) kontunuacja_start_date,   
   lag(end_date) over(partition by employee_id order by end_date) kontynuacja_end_date,  
   case when lead(start_date) over(partition by employee_id order by start_date) = end_date + 1   
      OR lag(end_date) over(partition by employee_id order by end_date) + 1 = start_date   
      then 0   
      else rownum  
   end czy_kontynuacja
 from hr.job_history where employee_id in (101, 200)  
 order by employee_id, start_date;   

Jeśli zweryfikuję, że rekord jest kontynuacją, w kolumnę czy_kontynuacja wstawiam wartość 0, jeśli nie ma kontynuacji - wstawiam wartość rownum. Rownum  dlatego, że będę po tym polu grupować i chcę, by dla rekordów z kontynuacją pole miało tę samą wartość, w moim przykładzie 0  a dla rekordów bez kontynuacji - każdy rekord powinien mieć inne wartości, różne od 0.

EMPLOYEE_ID	START_DATE	END_DATE	KONTUNUACJA_START_DATE	KONTYNUACJA_END_DATE	CZY_KONTYNUACJA
101	21-SEP-97	27-OCT-01	28-OCT-01	-	0
101	28-OCT-01	15-MAR-05	16-MAR-05	27-OCT-01	0
101	16-MAR-05	19-DEC-19	-	15-MAR-05	0
200	17-SEP-95	17-JUN-01	01-JUL-02	-	3
200	01-JUL-02	31-DEC-06	-	17-JUN-01	4

Na koniec wystarczy pogrupować po employee_id oraz informacji, czy rekord jest kontynuacją:

 select employee_id, min(start_date) start_date , max(end_date) end_date, count(*)  
 from (  
   select employee_id, start_date, end_date,  
     case when lead(start_date) over(partition by employee_id order by start_date) = end_date + 1   
      OR lag(end_date) over(partition by employee_id order by end_date) + 1 = start_date   
      then 0  
      else rownum  
     end continuity  
   from job_history where employee_id in ( 101,200))  
 group by employee_id, continuity;  

EMPLOYEE_ID	START_DATE	END_DATE	COUNT(*)
101	21-SEP-97	19-DEC-19	3
200	17-SEP-95	17-JUN-01	1
200	01-JUL-02	31-DEC-06	1

I gotowe :)