0Pricing
SQL Academy · Lekcja

Jak działają rekurencyjne CTE

Przypadek bazowy i krok rekurencyjny

Jak działają rekurencyjne CTE to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest rekurencyjne CTE?

Rekurencyjne CTE to wspólne wyrażenie tabelowe, które odwołuje się do samego siebie. Umożliwia tworzenie zapytań powtarzających pewien krok aż do spełnienia warunku — podobnie jak pętla, ale wyrażona za pomocą czystego SQL.

Rekurencyjne CTE definiuje się za pomocą słów kluczowych WITH RECURSIVE. Doskonale nadają się do przechodzenia przez dane hierarchiczne lub przypominające grafy, takie jak schematy organizacyjne, drzewa folderów i struktury zestawień materiałowych.

Struktura składająca się z dwóch części

Każde rekurencyjne CTE składa się dokładnie z dwóch części oddzielonych za pomocą UNION ALL:

1. Przypadek bazowy — nierekurencyjne zapytanie SELECT zwracające wiersze początkowe.

2. Krok rekurencyjny — zapytanie SELECT, które ponownie złącza CTE z nim samym i generuje wiersze kolejnego poziomu.

Silnik nadal wykonuje krok rekurencyjny i gromadzi wyniki, aż nie zostaną wygenerowane żadne nowe wiersze.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Liczenie od 1 do 5

Najprostsze rekurencyjne CTE służy do liczenia. Przypadek bazowy ustawia wartość początkową 1. Krok rekurencyjny dodaje 1 w każdej iteracji. Klauzula WHERE znajdująca się w kroku rekurencyjnym pełni funkcję warunku zakończenia — bez niej zapytanie wykonywałoby się bez końca.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Wykonywanie krok po kroku

Oto sposób, w jaki silnik przetwarza CTE będące licznikiem w kolejnych iteracjach:

Iteracja 0 (przypadek bazowy): zwraca {1}.

Iteracja 1: stosuje krok rekurencyjny do {1} i zwraca {2}.

Iteracja 2: stosuje krok rekurencyjny do {2} i zwraca {3}.

Iteracje 3 i 4: zwracają kolejno {4} i {5}.

Iteracja 5: warunek WHERE n < 5 jest fałszywy dla n=5, więc nie zostają zwrócone żadne wiersze. Zapytanie się kończy.

Wszystkie zgromadzone wiersze — 1, 2, 3, 4, 5 — tworzą końcowy wynik.

Konfigurowanie tabeli hierarchii

Rekurencyjne CTE doskonale sprawdzają się w tabelach odwołujących się do samych siebie. Utwórzmy tabelę employees, w której każdy pracownik ma opcjonalną kolumnę manager_id odwołującą się do tej samej tabeli.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Przechodzenie przez hierarchię

Możemy teraz przejść przez cały łańcuch podległości, zaczynając od dyrektora generalnego (Alice, id=1). Przypadek bazowy wybiera Alice, a krok rekurencyjny wyszukuje wszystkich pracowników, których manager_id odpowiada identyfikatorowi znajdującemu się już w CTE.

Wynik zawiera każdego pracownika osiągalnego z poziomu Alice, niezależnie od głębokości drzewa.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Śledzenie ścieżki

Popularnym rozszerzeniem jest zbudowanie ciągu ścieżki pokazującego pełny łańcuch od korzenia do każdego węzła. Podczas przechodzenia na kolejne poziomy łączymy nazwy, oddzielając je za pomocą ' -> '.

Ułatwia to wyświetlanie nawigacji w formie okruszków nawigacyjnych oraz diagnozowanie głębokich hierarchii.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Ograniczanie głębokości rekurencji

Głębokie lub cykliczne dane mogą sprawić, że rekurencyjne CTE będzie wykonywane przez bardzo długi czas. Dwie bezpieczne praktyki:

1. Śledzenie głębokości i dodanie klauzuli WHERE — WHERE depth < 10 gwarantuje, że nie zostanie przekroczonych 10 poziomów.

2. Użycie kolumny wykrywającej cykle — niektóre bazy danych (PostgreSQL 14+) oferują składnię CYCLE, która automatycznie wykrywa ponowne odwiedziny węzłów.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION a UNION ALL w rekurencyjnych CTE

Krok rekurencyjny niemal zawsze korzysta z UNION ALL, a nie z UNION. Oto dlaczego:

UNION usuwa duplikaty po każdej iteracji, porównując cały zbiór wyników — jest to niezwykle kosztowne i może zmienić znaczenie w przypadku grafów, w których ten sam węzeł jest prawidłowo osiągany za pomocą wielu ścieżek.

UNION ALL zachowuje wszystkie wiersze bez usuwania duplikatów, co jest zarówno szybsze, jak i poprawne podczas przechodzenia przez drzewo. Z UNION należy korzystać tylko wtedy, gdy istnieje konkretna potrzeba usunięcia duplikatów i rozumieją Państwo związany z tym koszt wydajnościowy.

Generowanie szeregu dat

Rekurencyjne CTE są również przydatne do generowania sekwencji dat. Ten przykład tworzy każdy dzień określonego tygodnia — jest to wzorzec często używany do budowania raportów kalendarzowych lub uzupełniania luk w danych szeregów czasowych.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Znajdowanie wszystkich podwładnych jednego przełożonego

Przypadek bazowy można zainicjować dowolnym konkretnym węzłem, a nie tylko korzeniem. W tym przykładzie zaczynamy od Boba (id=2) i znajdujemy wszystkie osoby podlegające mu bezpośrednio lub pośrednio.

Ten wzorzec przydaje się podczas sprawdzania uprawnień, agregowania danych poddrzewa lub ograniczania zakresu pulpitów nawigacyjnych do jednego działu.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Szybki sprawdzian

Proszę sprawdzić swoją wiedzę na temat działania rekurencyjnych CTE.

Podsumowanie lekcji

W tej lekcji poznali Państwo sposób działania rekurencyjnych CTE:

Struktura: każde rekurencyjne CTE składa się z przypadku bazowego (wierszy początkowych) połączonego z krokiem rekurencyjnym (odwołującym się do samego siebie zapytaniem SELECT) za pomocą UNION ALL.

Zakończenie: silnik powtarza krok rekurencyjny i gromadzi wyniki, aż krok zwróci zero wierszy.

Typowe zastosowania: przechodzenie przez schematy organizacyjne i drzewa folderów, generowanie sekwencji liczb lub dat, obliczanie ścieżek oraz znajdowanie wszystkich węzłów w poddrzewie.

Wskazówki dotyczące bezpieczeństwa: należy zawsze uwzględnić warunek zakończenia (ograniczenie głębokości lub zabezpieczenie przed cyklami) i ze względów wydajności preferować UNION ALL zamiast UNION.

Często zadawane pytania

Czy lekcja „Jak działają rekurencyjne CTE” jest bezpłatna?

Tak — pełny tekst „Jak działają rekurencyjne CTE” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Jak działają rekurencyjne CTE”?

Przypadek bazowy i krok rekurencyjny Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.

Ile czasu zajmuje lekcja „Jak działają rekurencyjne CTE”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Jak działają rekurencyjne CTE
  2. Przechodzenie drzewa kategorii
  3. Generowanie serii i sekwencji
  4. Unikanie nieskończonych pętli
← Powrót do SQL Academy