Nie taki znowu zwykły SQL Server cz. 2 – CTE w modelach hierarchicznych cd.

Nadszedł czas na kontynuację naszej przygody z CTE. W poprzednim odcinku pokazałem jak bardzo wspólne wyrażenia tablicowe zbliżone są do podzapytań oraz jak można z nich korzystać. Wspomniałem też na samym początku, że pozwalają na obsługę modeli hierarchicznych z poziomu języka SQL, zatem postaram się nieco przybliżyć to zagadnienie.

Czym w ogóle jest model hierarchiczny ?

W dużym uproszczeniu mamy z nim do czynienia wtedy kiedy modelujemy jakąś hierarchię (ach to masło maślane). Najprościej jest zwizualizować sobie drzewiastą strukturę:

Hierarchical Model

W podobny sposób układają się np. katalogi i pliki w systemie operacyjnym, modelować w ten sposób możemy np. pracowników i przełożonych – każdy pracownik ma swojego przełożonego, aż po prezesa. Istnieją i istniały też dedykowane bazy hierarchiczne, jeszcze przed czasami dominacji modelu relacyjnego. Jedną z pozostałości po takich bazach jest np. rejestr Windows. Co ciekawe, jeśli się przyjrzeć współczesnym formatom, takim jak XML także ta hierarchiczność tam występuje, ale… nie miało być o tym 🙂

Jak wspominałem na rynku dominuje model relacyjny i chcąc odzwierciedlić np. zależność między pracownikami musimy odwołać się do tabeli przechowującej pracownika:

W takim przypadku naszym kluczem obcym będzie:

Tak modelując takie rozwiązanie najsensowniej jest stworzyć relację w ramach tej samej tabeli.
Możemy załadować kilka przykładowych wierszy:

Konsekwencje takiego modelowania

Teraz chcąc np. wybrać wszystkie istoty podległe Iluvatarowi, możemy wywołać zapytanie:

Chyba nie do końca o to nam chodziło… Interesują nas nie tylko bezpośredni podwładni, ale wszyscy. Możemy próbować rozbudowywać to zapytanie o kolejne poziomy, ale wydaje się to drogą ku zatraceniu.

Tutaj każda z baz proponuje swoje rozwiązanie (np. CONNECT BY autorstwa Oracle – swoją drogą całkiem przyjemna składnia), ale znam tylko jedne zgodne ze standardem języka SQL – właśnie użycie CTE. Ok, więc jak do tego podejść? Nie będę zdradzał rozwiązania od razu spróbujmy do tego dotrzeć do niego krok po kroku w naturalny sposób, a wyjdziemy zaczynając od błędnego kodu. Po tym krótkim spacerku mam nadzieję, że już nigdy nie będzie to dla was trudne.

Zauważyliście, że dodałem kolumnę PLACE mówiącą na którym poziomie hierarchii jesteśmy? Druga rzecz, która powinna zwrócić waszą uwagę to to, że z każdym kolejnym wierszem dodajemy jakby jeden poziom. Ostatnie najważniejsze spostrzeżenie jakie się nasuwa to, że każde zapytanie poza pierwszym uwzględnia wynik zapytania wcześniejszego. A czy… zamiast składni WHERE… IN… nie możnaby użyć złączenia z już przygotowanym wyrażeniem tablicowym ?

BINGO! Chciałoby się zakrzyknąć. Czy w takim razie dałoby się zbudować tą hierarchię w druga stronę ? Np. poczynając od Sama przejść, aż po Iluvatara ? Żaden problem:

Ale zaraz! Zapytacie skąd wiedziałem, że trzeba zmienić relację i w jaki sposób ? Spójrzcie proszę na zakomentowany kod. Gdy spróbujecie go wykonać i przeanalizować wyda się wam oczywiste, że relacja musi wyglądać trochę inaczej. Jeżeli jeszcze macie jakieś wątpliwości, polecam wam stworzyć własny model hierarchiczny np. folder – katalog i na nim poćwiczyć te zapytania, a już wkrótce wyda wam się to bardzo naturalne.

Z rzeczy które jeszcze warto dodać… Dla wywołań rekurencyjnych bardzo istotne jest by między jednym, a drugim zapytaniem był UNION ALL, w przeciwnym razie baza wywali błąd. Ponadto pomimo że WITH znajduje się w standardzie różnie bywa ze wsparciem w zależności od dostawcy SZBD. Niektórzy jak np. Postgres wymagają słowa  RECURSIVE (WITH RECURSIVE cte AS… ). Zawsze sprawdzajcie na co pozwala wam dostawca zanim oprzecie o coś swoje rozwiązanie.

To tyle w temacie CTE. Dziękuję wszystkim, którzy dotarli aż tutaj 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *