Rekursive CTEs: Hierarchien mit beliebiger Tiefe

Ein Self-JOIN (Modul 5) findet nur eine Ebene einer Hierarchie: Kind → Elternteil. Aber was passiert, wenn die Hierarchie 3, 5 oder n Ebenen tief ist? und Sie wissen nicht im Voraus, wie viele? Sie benötigen rekursive CTEs: a CTE, der sich auf sich selbst bezieht, um die Ergebnisse iterativ zu „erweitern“.

Im ecommerce-Datensatz hat die categories-Tabelle drei Wurzeln (Elettronica, Libri, Casa) und je 2 Kinder – ein 2-stöckiger Baum. Es ist ein einfacher Fall, aber genug, um das Muster zu lernen.

Die Syntax

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;

So funktioniert es in der Praxis:

1. PostgreSQL führt den Basisterm einmal aus.
2. Es führt den rekursiven Term unter Verwendung der soeben erzeugten Zeilen aus „Eingabe“ (nicht der gesamte CTE: nur das Inkrement).
3. Schritt 2 wird wiederholt, bis der rekursive Term 0 neue Zeilen erzeugt.
4. Das Endergebnis ist der UNION ALL aller Schritte.

Beispiel 1: Baum mit Tiefe und Pfad

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;

Die erste Iteration erzeugt 3 Zeilen (die Wurzeln auf Ebene 1). Der Zweite Iteration findet die 6 Kinder (Ebene 2). Die dritte Iteration findet nichts → erledigt.

Beispiel 2: Finden Sie bei einem gegebenen Knoten dessen Wurzel

Eine Variante „von oben nach unten → von unten nach oben“: Wir verbreiten die ID der Wurzel nach unten der Baum.

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;

Probieren Sie es aus

Wiederholungsübung