Temat: zapytanie SELECT (przedziały dat)

Mam pytanie odnośnie zapytania SQL w bazie Oracle.
Jest tabelka employees z danymi dotyczącymi zatrudniania i zwalniania pracowników firmy. Zawiera ona kolumny employee_id, data_od i data_do. Klucz główny jest na dwóch kolumnach: employee_id i data_od. Wartości employee_id nie są unikalne (pracownicy byli zwalniani i zatrudniani po kilka razy). Tabelka zawiera kilkadziesiąt milionów wierszy.
Przedziały dat wyglądają tak, że kolumny data_od i data_do dla danego wiersza są lewostronnie zamknięte, a prawostronnie otwarte. Data_od powinna być taka sama jak data_do z poprzedniego wiersza. Mówimy oczywiście o jednym pracowniku.
Jest jednak podejrzenie, że są miejsca, gdzie daty się nie stykają bądź tez nakładają się na siebie.
Zadanie jest takie, żeby napisać zapytanie SELECT, które sprawdzi czy istnieją takie "przerwy" między datami (daty się nie stykają) oraz drugie zapytanie SELECT, które sprawdzi czy istnieją takie rekordy, gdzie daty nakładają się na siebie, czyli, że data_od jest mniejsza od data_do z poprzedniego wiersza. Mowa jest oczywiście o zakresach dat dla jednego pracownika.
Ważną rzeczą jest również, aby zapytania były jak najbardziej optymalne pod kątem wydajności.
Podejrzewam, że trzeba zastosować jakąś funkcję analityczną, ewentualnie exists (ale chyba będzie ono mniej wydajne od funkcji? i nie wiem czy będzie się nadawało do tej sytuacji), ale może ktoś bardziej doświadczony podpowie jak to napisać?Ten post został edytowany przez Autora dnia 21.07.14 o godzinie 23:04

Temat: zapytanie SELECT (przedziały dat)

Policz ilość dni w zakresie MIn i Max przy grupowaniu per pracownik, następnie policz ilość dni. Różnica to przerwa.
Tutaj podobny wątek

Temat: zapytanie SELECT (przedziały dat)

Przy założeniu, że ten sam pracownik pojawia się w tabeli tylko kilka razy można użyć zapytania jak poniżej (do max 5 wystąpień) lub wygenerować sobie zapytanie na więcej.
Co do optymalności - używam czegoś podobnego na tabeli milionowej i zajmuje kilka sekund na słabym serwerze DEV. Jeśli masz możliwość stworzenia indeksu EMPLOYEE_ID+DATA_OD+DATA_DO mogłoby pobiec znacznie szybciej. Sam PK nie wystarczy żeby uniknąć pełnego skanowania tabeli (co wcale nie musi być takie złe).

 

WITH PRAC AS (

     SELECT DISTINCT p.EMP_ID, p.DATA_OD, p.DATA_DO,

                     p.DATA_DO DATA_DO_C,

                     ROW_NUMBER() OVER (PARTITION BY p.EMP_ID ORDER BY p.DATA_OD) RNUM

     FROM SCHEMA.TAB_EMP p

     ),

EMP_ID_NIEPELNA_HISTORIA AS(     

    SELECT DISTINCT PRAC1.EMP_ID EMP_ID_N

    FROM   PRAC PRAC1

    LEFT OUTER JOIN PRAC PRAC2 ON PRAC1.EMP_ID=PRAC2.EMP_ID AND PRAC1.RNUM=1 AND PRAC2.RNUM=2 AND  PRAC1.DATA_OD<>PRAC2.DATA_OD

    LEFT OUTER JOIN PRAC PRAC3 ON PRAC1.EMP_ID=PRAC3.EMP_ID AND PRAC1.RNUM=1 AND PRAC3.RNUM=3 AND  PRAC2.DATA_OD<>PRAC3.DATA_OD

    LEFT OUTER JOIN PRAC PRAC4 ON PRAC1.EMP_ID=PRAC4.EMP_ID AND PRAC1.RNUM=1 AND PRAC4.RNUM=4 AND  PRAC3.DATA_OD<>PRAC4.DATA_OD

    LEFT OUTER JOIN PRAC PRAC5 ON PRAC1.EMP_ID=PRAC5.EMP_ID AND PRAC1.RNUM=1 AND PRAC5.RNUM=5 AND  PRAC4.DATA_OD<>PRAC5.DATA_OD

    WHERE (PRAC2.EMP_ID IS NOT NULL AND  PRAC1.DATA_DO_C<>PRAC2.DATA_OD)  

    OR    (PRAC3.EMP_ID IS NOT NULL AND  PRAC2.DATA_DO_C<>PRAC3.DATA_OD)                 

    OR    (PRAC4.EMP_ID IS NOT NULL AND  PRAC3.DATA_DO_C<>PRAC4.DATA_OD) 

    OR    (PRAC5.EMP_ID IS NOT NULL AND  PRAC4.DATA_DO_C<>PRAC5.DATA_OD) 

    )

SELECT DISTINCT c.EMP_ID NIECIAGLY_EMP

FROM SCHEMA.TAB_EMP c

INNER JOIN EMP_ID_NIEPELNA_HISTORIA ON EMP_ID_NIEPELNA_HISTORIA.EMP_ID_N=c.EMP_ID

ORDER BY c.EMP_ID, c.DATA_OD;

Temat: zapytanie SELECT (przedziały dat)

Najlepiej będzie chyba napisać własną funkcję agregującą i w niej przetwarzać przedział za przedziałem.
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/...

Pierwsze podejście wyłoży się, gdy w ramach jednego pracownika część dni się nałoży, a część będzie z przerwą, natomiast drugie to SQLowy potworek.

Temat: zapytanie SELECT (przedziały dat)

Funkcjami analitycznymi można to zrobić tak, jak w przykładach poniżej.
Oba rozwiązania różnią się tylko znakami < bądź >.

Przerwy w okresach:

select employee_id, data_od, data_do, poprz_data_do

from (

    select employee_id, data_od, data_do, lag(data_do) over (partition by employee_id order by data_od) poprz_data_do

    from employees

)

where data_od > poprz_data_do

order by employee_id, data_od;

Nachodzące na siebie okresy:

select employee_id, data_od, data_do, poprz_data_do

from (

    select employee_id, data_od, data_do, lag(data_do) over (partition by employee_id order by data_od) poprz_data_do

    from employees

)

where data_od < poprz_data_do

order by employee_id, data_od;

Generalnie anomalnie są wtedy, gdy data_od != poprz_data_do, więc całość można zrobić jednym zapytaniem.
Podział danych na pracowników i sortowanie są realizowane zgodnie z indeksem klucza głównego. Tabelka jest czytana tylko raz. Z wydajnością nie powinno być problemu.

Temat: zapytanie SELECT (przedziały dat)

Też tak kombinowałem, ale lag może mieć problemy z przedziałami zawierającymi się w sobie (np. jeśli kilka poprawnych będzie zawartych w jednym obejmującym wszystkie). Natomiast powinno wystarczyć do wskazania, że z którymś pracownikiem jest problem.

Temat: zapytanie SELECT (przedziały dat)

Dzięki za każdą podpowiedź. Trochę mnie przeraziło drugie rozwiązanie, tak jak kolega określił SQLowy potworek :)
Natomiast najprostsze jest to napisane przez kolegę Andrzeja i chyba będzie najlepsze w tym przypadku. Sam też kombinowałem z funkcjami lag i lead tylko nie wiedziałem jak to wszystko ze sobą poskładać. Także dzięki za pomoc.

A jeszcze odnośnie tego samego tematu będzie mi potrzebne takie zapytanie SELECT, które będzie wyświetlało wszystkie wiersze z pracownikami tam, gdzie występują dziury, ale tak, żeby wyświetlić też wiersz poprzedni i następny względem tego, gdzie występuje dziura. Oczywiście też tak jak poprzednio chodzi o zakresy dat w obrębie jednego pracownika. Nie wiem czy trzeba tu coś pokombinować z sekwencjami czy też coś z funkcjami lag i lead czy jeszcze inne rozwiązanie?Ten post został edytowany przez Autora dnia 03.12.14 o godzinie 16:44