W bazach danych często występuje potrzeba pracy na kalendarzach.
Ale skąd wziąć taki kalendarz?
Każdy sobie radzi jak może. A inwencja twórcza programistów jest niczym nie ograniczona :)
Niekiedy jest to dedykowana tabela pracowicie uzupełniana dzień po dniu.
Innym razem jest to procedura zwracająca listę dni.
Kiedy indziej jest to DISTICNT z kolumny typu date na istniejącej tabeli.
Rozwiązania te jednak bądź są niepewne (tabela z danymi nie musi mieć danych za wszystkie dni) lub są pracochłonne (proces uzupełniający tabelę z kalendarzem). Mimo że w niektórych sytuacjach ich zastosowanie może być jak najbardziej uzasadnione – w większości przypadków wystarczy nam coś prostszego.
Coś tak prostego jak jeden zwykły SQL.
Za pomocą zwykłego SQL, klauzuli CONNECT BY i tabeli DUAL możemy sobie wygenerować kalendarz za ostatnie na przykład :ile_dni dni.
SELECT TRUNC(SYSDATE)- :ile_dni + L dni FROM (
SELECT level l FROM dual CONNECT BY level <= :ile_dni);
Lub tylko miesiące za ostatnie 100 dni:
SELECT ADD_MONTHS(TRUNC(SYSDATE -:ile_dni,'month' ), L ) miesiace FROM (
SELECT level l FROM dual CONNECT BY level <= MONTHS_BETWEEN(TRUNC(SYSDATE,'month' ),TRUNC(SYSDATE -:ile_dni ,'month' ))+1 );
Albo za zakres dat:
SELECT to_date(:data_poczatkowa,'dd-mm-yyyy')-1 + L
FROM (
SELECT level l FROM dual CONNECT BY level <= to_date(:data_koncowa,'dd-mm-yyyy')-to_date(:data_poczatkowa,'dd-mm-yyyy'));
Pewnie te trzy zapytanie nie wyczerpują wszystkich potrzeb i zastosowań. Ale można swobodnie modyfikować zapytania i dostosowywać je do własnych potrzeb. Można na ich bazie utworzyć widok czy opakować w funkcję. To są zapytania z rodziny "przydasie" - każdemu programiście wcześniej czy później umiejętność tworzenia kalendarza za pomocą SQL się przyda :)
na przykład dni miesiąca wg parametru:
OdpowiedzUsuńwith param as
(select 202204 d from dual),
ld as
(
select to_date(d,'yyyymm')dt, last_day(to_date(d,'yyyymm'))-to_date(d,'yyyymm')+1 i from param
)
select to_char(dt -1 + level,'yyyy-mm-dd') dzien_m FROM ld CONNECT BY level <= ld.i;
a potem pomyślałem, że można jeszcze prościej:
OdpowiedzUsuńwith param as (select '202202' d, 31 i from dual)
select to_char(dzien_m,'yyyy-mm-dd') dzien_m
from (select d, to_date(d,'yyyymm') -1 + level dzien_m FROM param CONNECT BY level <= i)
where to_char(dzien_m,'yyyymm') = d;