Rekurencyjne CTE: hierarchie o dowolnej głębokości

Self-JOIN (Moduł 5) znajduje tylko jeden poziom hierarchii: dziecko → rodzic. Ale co się stanie, jeśli hierarchia będzie głęboka na 3, 5 lub n poziomów, i nie wiesz z góry ile? Potrzebujesz rekursywnych CTE: a CTE, który odnosi się do siebie, aby iteracyjnie „rozszerzać” wyniki.

W zbiorze danych ecommerce tabela categories ma 3 pierwiastki (Elettronica, Libri, Casa) i po 2 dzieci każde — dwupoziomowe drzewo. To jest prosty przypadek, ale wystarczający, aby nauczyć się wzoru.

Składnia

SQL

WITH RECURSIVE nome (col1, col2, …) AS (
  -- (1) Termine BASE: le righe di partenza
  SELECT …
  FROM   …
  WHERE  …

  UNION ALL

  -- (2) Termine RICORSIVO: si appoggia a "nome" stesso per espandere
  SELECT …
  FROM   tabella  t
  JOIN   nome    n  ON …
)
SELECT * FROM nome;

Jak to działa w praktyce:

PostgreSQL wykonuje termin bazowy jeden raz.
Wykonuje termin rekurencyjny, używając wierszy właśnie utworzonych jako „wejście” (nie cały CTE: tylko przyrost).
Powtarza krok 2, aż składnik rekurencyjny wygeneruje 0 nowych wierszy.
Ostatecznym wynikiem jest UNION ALL wszystkich kroków.

Przykład 1: drzewo z głębokością i ścieżką

SQL

WITH RECURSIVE tree AS (
  -- base: le radici (parent_id IS NULL)
  SELECT id,
         name,
         parent_id,
         1                 AS depth,
         name              AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- ricorsivo: per ogni categoria che ha come parent una riga già in tree
  SELECT c.id,
         c.name,
         c.parent_id,
         t.depth + 1,
         t.path || ' > ' || c.name
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, depth, path
FROM   tree
ORDER BY path;

Pierwsza iteracja tworzy 3 wiersze (korzenie na poziomie 1). Drugi iteracja znajduje 6 dzieci (poziom 2). Trzecia iteracja stwierdza nic → zrobione.

Przykład 2: mając węzeł, znajdź jego pierwiastek

Wariant „z góry na dół → z dołu do góry”: propagujemy identyfikator roota w dół drzewo.

SQL

WITH RECURSIVE tree AS (
  SELECT id,
         name,
         parent_id,
         id   AS root_id,
         name AS root_name
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id,
         c.name,
         c.parent_id,
         t.root_id,    -- propago lo stesso id_radice
         t.root_name   -- e nome
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, root_name
FROM   tree
ORDER BY id;

Spróbuj

Ćwiczenie#sql.m7.l4.e1

Próby: 0Ładowanie...

Użyj rekurencyjnego CTE o nazwie „drzewo”, aby spłaszczyć hierarchię kategorii. Pokaż identyfikator, nazwę, głębokość (1 dla korzeni, 2 dla dzieci itp.) i ścieżkę (np. „Elettronica > Audio”). Zamawiaj według ścieżki.

Ładowanie edytora...

Pokaż wskazówkę

JOIN w terminie rekurencyjnym łączy c.parent_id z wierszem już obecnym w drzewie (t.id).

Rozwiązanie dostępne po 3 próbach

Przejrzyj ćwiczenie

Ćwiczenie#sql.m7.l4.e2

Próby: 0Ładowanie...

Dla każdej kategorii pokaż identyfikator, nazwę i nazwę_korzenia (nazwę kategorii głównej na górze drzewa, do której należy). Korzenie mają root_name = name. Zamów według identyfikatora.

Ładowanie edytora...

Pokaż wskazówkę

Propaguj nazwę_katalogu od elementu nadrzędnego (nazwa_katalogu t) do elementu podrzędnego (c).

Rozwiązanie dostępne po 3 próbach