Module 7 · Les 4 van 428/57 in de cursus~14 min

Modulelessen (4/4)

Recursieve CTE's: hiërarchieën met willekeurige diepte

Een self-JOIN (Module 5) vindt slechts één niveau van een hiërarchie: kind → ouder. Maar wat gebeurt er als de hiërarchie 3, 5 of n niveaus diep is? En je weet niet op voorhand hoeveel? U heeft recursieve CTE's nodig: CTE die naar zichzelf verwijst om de resultaten iteratief te "uitbreiden".

In de gegevensset ecommerce heeft de tabel categories 3 wortels (Elettronica, Libri, Casa) en elk 2 kinderen - een boom met 2 niveaus. Het is een eenvoudig geval maar genoeg om het patroon te leren.

De syntaxis

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;

Hoe het in de praktijk werkt:

PostgreSQL voert de basisterm één keer uit.
Het voert de recursieve term uit met behulp van de rijen die zojuist zijn geproduceerd als "invoer" (niet de hele CTE: alleen de verhoging).
Het herhaalt stap 2 totdat de recursieve term 0 nieuwe rijen oplevert.
Het eindresultaat is de UNION ALL van alle stappen.

Voorbeeld 1: boom met diepte en pad

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;

De eerste iteratie produceert 3 rijen (de wortels op niveau 1). De tweede iteratie vindt de 6 kinderen (niveau 2). De derde iteratie vindt niets → gedaan.

Voorbeeld 2: gegeven een knooppunt, vind de wortel ervan

Een "top-down → bottom-up" variant: we propageren de id van de root naar beneden de boom.

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;

Probeer het

Oefening#sql.m7.l4.e1

Pogingen: 0Laden…

Gebruik een recursieve CTE met de naam 'boom' om de categoriehiërarchie plat te maken. Toon id, naam, diepte (1 voor wortels, 2 voor kinderen, etc.) en pad (bijvoorbeeld 'Elettronica > Audio'). Bestel per pad.

Editor laden…

Toon hint

De JOIN in de recursieve term koppelt c.parent_id aan de rij die al aanwezig is in de boom (t.id).

Oplossing beschikbaar na 3 pogingen

Oefening beoordelen

Oefening#sql.m7.l4.e2