Rekurencyjne użycie klauzuli “WITH”

Ponoć mądrość – a przynajmniej popęd ku niej – rodzi się ze zdziwienia, tak przynajmniej twierdzili antyczni myśliciele. Znać muszę być wyjątkowo bystrym facetem – a przynajmniej pałać ogromną chucią do wszelkich uciech intelektualnych – skoro niemal codziennie coś mnie zaskakuje i to raczej bardziej niż mniej. Ot weźmy choćby sytuację z ostatniego piątku, kiedy to niespodziewanie odkryłem, iż przyszło mi pracować na innej wersji bazy danych niźli mi się do tego dnia zdawało (choć Bóg mi świadkiem, że kilka tygodni wcześniej osobiście to sprawdzałem i wówczas stan rzeczy prezentował się inaczej).

Mniejsza jednak o to czy od początku żyłem – na własną szkodę – w błędzie czy może po prostu ominęła mnie informacja o fundamentalnej zmianie w tym zakresie. Najważniejszy w tym wszystkim jest bowiem fakt, że tym sposobem do mojego instrumentarium SQL mogę świadomie dołożyć kilka rozwiązań, z którymi – jak mi się do niedawna wydawało – musiałem się pożegnać ad calendas graecas. 

Otóż okazuje się bowiem, że w najnowszej odsłonie DBMS-a używanego w moim miejscu pracy dostępne są wreszcie tak powszednie rozwiązania jak choćby tandemy funkcji LEAD – LAG, GREATEST – LEAST, czy mechanizm przestawiania wierszy na kolumny (PIVOT) oraz co najmniej jeszcze kilka innych usprawnień, z których zdarzało mi się w przeszłości korzystać w innym otoczeniu zawodowym.

Tym niemniej przeglądając z zadowoleniem wykaz tych wszystkich “nowości” musiałem z pewnym rozczarowaniem stwierdzić, że dla klauzuli WITH nadal stoi w dokumentacji: “ability for a CTE to reference itself as in a recursive manner is not supported”. W myśl hasła, że nie ma tego złego, co to na dobre nie dałoby się przekuć, pomyślałem sobie, że to w sumie dobra okazja, by w domowym zaciszu odświeżyć to zagadnienie, a przy okazji popełnić wpis na bloga.

Niestety jak już usiadłem do pisania niniejszego tekstu, to z przerażeniem stwierdziłem, że nie za bardzo potrafię działanie tego mechanizmu wyłożyć w prosty sposób, o ile w ogóle potrafię to jakkolwiek objaśnić. Rekurencja jako taka, z którą po raz pierwszy spotkałem się rzecz jasna lata temu, od samego początku była dla mnie rozwiązaniem mało intuicyjnym i objęcie moim małym rozumkiem przyszło dopiero z czasem po prostu w wyniku praktyki. Tym niemniej dla porządku rzeczy napiszę, że rekurencja – przynajmniej na obszarze programowania – to sytuacja, w której funkcja odwołuje się sama do siebie, dzięki czemu w pewnych scenariuszach można osiągnąć zamierzony efekt, jak chociażby policzyć kolejne liczby ciągu Fibonacciego dla podanego zakresu (jedno z “popularnych” ćwiczeń w tym zakresie, którym zamęczani są biedni studenci).

Zanim jednak przejdziemy do meritum muszę zaznaczyć, że sponsorem dzisiejszego wpisu będzie “poczucie porażki”, zaś czytających z całego serca przepraszam za część dalszą niniejszego wpisu.

Rekursywne wykorzystanie klauzuli WITH

Samo rozwiązanie ma już naprawdę długą brodę i do standardu SQL zostało dodane jeszcze pod koniec ubiegłego stulecia. Mechanizm ten pomyślany został do używania we wszelkiego rodzaju zapytaniach, mających wyrażać relacje hierarchiczne, które mogą występować w przechowywanych danych. Dobrym przykładem w tym zakresie jest chociażby zależność służbowa w strukturach organizacji, gdzie lecimy od prezesa przez kolejnych przełożonych, by wreszcie skończyć na najmniejszych z maluczkich. Tym niemniej jego zastosowania mogą być mniej typowe i właśnie na bazie takiego “oryginalnego” wyzwania, polegającego na wyznaczeniu pewnych przedziałów czasowych między dwiema dowolnymi datami, spróbuję przybliżyć rekurencyjne wykorzystanie CTE.

Od razu napiszę, że nie jest to przykład wydumany i w moim poprzednim miejscu pracy musiałem zmierzyć się z praktycznym jego rozwiązaniem. Potrzebowałem bowiem wówczas mechanizmu, które pozwoli mi – w czystym SQL – obsłużyć każdy scenariusz, jaki wykoncypuje sobie użytkownik budowanego przeze mnie raportu. Szło wówczas o to, że zainteresowana osoba za pośrednictwem odpowiedniego formularza w aplikacji przekazywała daty, które następnie należało uwzględnić przy generowaniu raportu, a ten miał za zadanie przedstawić pewne informacje z zadanego w ten sposób okresu, ale w podziale na miesiące kalendarzowe, czyli prosty warunek BETWEEN pierwsza_data AND druga_data nie wchodził tutaj w grę, ponieważ ten okres czasu musiałem podzielić na mniejsze interwały niekoniecznie tej samej długości.

Na potrzeby niniejszego wpisu przygotowałem widoczne niżej zapytanie, którego zadaniem jest właśnie wyznaczenie wszystkich miesięcy (a ściślej: pierwszego i ostatniego dnia miesiąca) dla dwóch arbitralnych dat, w tym wypadku dla 16 marca 2021 oraz 13 listopada 2022. Założenie było przy tym takie, że pod uwagę były brane również te miesiące, w których każda z tych dat wypada.

WITH var_date AS (
SELECT
	'2021-03-16' AS first_date
	,'2022-11-13' AS last_date
),
cte (first_day_month, last_day_month) AS (
SELECT
	DATETRUNC(MONTH, var_date.first_date)
	,EOMONTH(var_date.first_date)
FROM
	var_date 

UNION ALL

SELECT
	DATEADD(MONTH, 1, cte.first_day_month)
	,EOMONTH(DATEADD(MONTH, 1, cte.first_day_month))
FROM
	cte
	JOIN var_date 
		ON 1 = 1 
WHERE
	cte.last_day_month < var_date.last_date
)
SELECT
	*
FROM
	cte

W pierwszej części naszego zapytania utworzona została tabela tymczasową var_date, w której mamy umieszczone coś na wzór zmiennych odpowiadających obu datom. Taki zabieg nie był w tym wypadku konieczny, ale wydaje mi się, że poprawia to czytelność całości, ponieważ te dwie “zmienne” zostaną następnie wykorzystane we właściwej części “mechanizmu” odpowiedzialnego za wyznaczenie miesięcznych przedziałów.

Skoro temat tego “mechanizmu” został wreszcie – na moje nieszczęście – wywołany w poprzednim akapicie, to od razu zdradzę, że cała magia dzieje się tutaj w ramach tabeli tymczasowej cte. Z pozoru wszystko wygląda niewinnie, ponieważ w środku jest jakieś złożenie (UNION ALL) dwóch zapytań, ale jak się dokładniej całości przyjrzymy to zauważymy, że ten drugi SELECT w klauzuli FROM odwołuje się się do tabeli tymczasowej cte, czyli tabela “używa sama siebie”. Tym samym mamy w tym miejscu naszą rekurencję.

Ale po kolei jednak. Pierwsze zapytanie jest raczej dość oczywiste – po prostu wyznaczamy za jego pomocą pierwszy przedział miesięczny na podstawie wcześniejszej z tych dwóch dat (“zmienna” first_date, czyli 16 marca 20221), do czego wykorzystujemy funkcje DATETRUNC (pierwszy dzień miesiąca) oraz EOMONTH (ostatni dzień miesiąca). Następnie wynik tego pierwszego zapytania jest przekazywany jako wartość do czegoś w rodzaju parametrów first_day_month oraz last_day_month i tym sposobem z powrotem trafia do tabeli cte, gdzie przekształcany jest w ramach drugiego zapytania. Czyli do wartości “parametru” first_day_month (przy pierwszej iteracji będzie to rzecz jasna “2023-03-01”) dodawany jest jeden miesiąc przy pomocy funkcji DATEADD, w wyniku czego otrzymujemy wartość “2023-04-01”. W podobny sposób wyliczana jest data końca miesiąca. 

Oczywiście te nowo wyliczone wartości przekazywane są z powrotem do tabeli cte i cała zabawa jest powtarzana, ale nie bez końca na mocy obecności warunku WHERE. Ten ostatni pilnuje, by zapytanie było wywoływane tak długo dopóki przekazana wartość parametru last_day_month była mniejsza od “zmiennej” last_date. Tym samym, jeśli po kolejnej iteracji do drugiego zapytania zostanie przekazana data większa niż 13 listopada 2023, całość przetworzenia zostanie zakończona.

Po zakończeniu działania przedstawionego wyżej kodu na końcu otrzymujemy wynik jak ten poniżej.