Rekurencja w SQL Server czyli Common Table Expressions - podstawy

Co to są Common Table Expressions?

Według dokumentacji MSDN Common Table Expression (CTE) to tymczasowy zbiór danych zdefiniowany w kontekście pojedynczej kwerendy SELECT, INSERT, UPDATE, DELETE i CREATE VIEW. CTE nie jest fizycznym obiektem w bazie danych i istnieje tylko w czasie wykonywania się jedenj operacji. Co istotne, CTE może samo się do siebie odwoływać. Tutaj właśnie drzemią rekurencyjne możliwości.

Składnia CTE wygląda następująco:

Nie jest to może oczywiste na pierwszy rzut oka, ale zaraz się wszystko wyjaśni. Jeżeli zależy nam na rekurencyjnym zastosowaniu CTE to składnia nieco się modyfikuje i wygląda mniej więcej tak, jak poniżej:

W miejsce UNION ALL można oczywiście wstawić inne operatory działające na zbiorach (UNION, INTERSECT, EXCEPT). Samo wyrażenie właściwe też może być bardziej skomplikowane. Dość dużą dowolność mamy też pisząc samo wyrażenie CTE. Nie zamierzam tutaj dokładnie wszystkiego tłumaczyć i powielać dokumentacji. Całość będzie wytłumaczona na konkretnych przykładach. Pierwszym przykładem niech będzie wybranie pierwszych 10 liczb naturalnych. Przykładowy kod zaprezentowany jest poniżej:

Przejdźmy teraz do wyjaśnienia poszczególnych elementów tej układanki. Całość zaczyna się od słowa kluczowego WITH, po którym znajduje się nazwa naszego CTE, a następnie słowo kluczowe AS. Nie ma tu żadnej filozofii. Jedyne, z czym tutaj możemy poszaleć jest nazwa. Najważniejsze są linijki numer 2 oraz 4. Linijka druga, żródło rekurencji, to taka wartość, od której rozpoczynamy wyliczanie całej reszty. Może to być dowolne zapytanie, znacznie bardziej skomplikowane od podanego w tym przykładzie. W naszym przypadku będzie to pojedynczy rekord o wartości 1. Wynik tej operacji staje się zaczynem dla całej rekurencji. Czwarta linijka to tzw. rekurencja właściwa. Polega ona na pobraniu wartości zwróconych z naszej definicji CTE i dodaniu do niej 1. Warunek służy tylko temu, aby po dziesiątym poziomie zagłębienia wywołania skryptu operacja została przerwana. Całość działa zatem w sposób następujący: pierwsze zapytanie zwraca wartość 1, drugie i każde kolejne pobiera poprzednią wartość CTE (na tym etapie jest to 1) i dodaje do tej wartości 1. Drugie wywołanie zwróci zatem liczbę 2. Trzecie pobierze poprzednią wartość CTE (teraz już 2) i doda jeden. Kolejnym wynikiem będzie zatem 3, potem 4, następnie 5 i tak dalej. Gdy N dojdzie do 10, zapytanie z linijki numer 4 zwróci NULL i cała rekurencja zostanie przerwana. Jeżeli jeszcze nie jest to jasne to spróbujmy napisać nieco inny fragment skryptu. Tym razem pobierzemy kwadraty dziesięciu kolejnych liczb naturalnych. Fragment skryptu pokazany jest poniżej:

Przykład jest minimalnie bardziej skomplikowany. Tym razem ziarnem, postacią początkową jest jeden rekord z dwoma polami. Pierwsze pole to liczba naturalna, druga to jej kwadrat. Są to wartości 1 i 1 (bo 1*1=1). Drugi przebieg pobiera poprzednie wartości i zwraca w pierwszym polu wartość zwiększoną o 1, a w drugim kwadrat wartości zwiększonej o 1. Taka operacja wykonywana jest aż do momentu, gdy wartość w pierwszym polu osiągnie 10.

CTE i generowanie ciągów liczbowych

Czas na nieco bardziej skomplikowany przykład. Tym razem spróbujemy wygenerować 30 kolejnych wartości ciągu Fibonacciego. Nie jest to być może przykład znajdujący szerokie zastosowanie w SQL, ale nadaje się do zademonstrowania rekurencji oraz Common Table Expressions. Przeanalizujmy zatem fragment kodu zaprezentowanego poniżej:

Tym razem wartością początkową jest rekord zawierający kolejno: 1) przedostatni element ciągu, 2) ostatni element ciągu, 3) sumę dwóch poprzednich wartości, 4) łańcuch znaków zawierający kolejne wartości ciągu oddzielone przecinkami oraz 5) stopień zagłębienia rekurencji. Każdy kolejny stopień zagłębienia robi kilka operacji. Po pierwsze, przedostatnim elementem staje się ostani element z poprzedniego wyniku, natomiast ostatnim elementem staje się suma z poprzedniego wyniku. Nową sumą jest suma nowego przedostatniego elementu i nowego ostatniego elementu. Do łańcucha znaków doklejany jest przecinek i nowa suma. Poziom zagłębienia, czyli ostatnia kolumna to wartość o jeden większa niż poprzednio. Warto zwrócić uwagę na funkcję CAST. Jest ona tutaj po to, aby zachować zgodność typów. Taką samą funkcję pełni przezywanie kolumn - też po to, aby zachować zgodność z wartością początkową. Rekurencja kończy się na 30 elemencie, bo wtedy druga część wyrażenia CTE zwróci NULL.

Wyrażenia Common Table Expressions mają znacznie szersze zastosowanie. Te, które zostały tutaj zaprezentowane mają na celu raczej wyjaśnienie zasady działania. Skrypty mające szersze zastosowanie są nieco bardziej skomplikowane i nie nadają się do nauki. Ciekawsze zastosowania zostaną przedstawione wkrótce.

Polecam poeksperymentować na prostych przykładach zanim rzucimy się na przykłady trudniejsze.

Kategoria:Common Table ExpressionSQL Server