Modulul 7 · Lecția 4 din 428/57 în curs~14 min

Lecții din modul (4/4)

CTE recursive: ierarhii de profunzime arbitrară

Un self-JOIN (Modulul 5) găsește doar un nivel al unei ierarhii: copil → părinte. Dar ce se întâmplă dacă ierarhia are 3, 5 sau n niveluri adânci, si nu stii dinainte cate? Aveți nevoie de CTE recursive: a CTE care se referă la sine pentru a „extinde” rezultatele în mod iterativ.

În setul de date ecommerce, tabelul categories are 3 rădăcini (Elettronica, Libri, Casa) și câte 2 copii — un copac cu 2 niveluri. Este o caz simplu, dar suficient pentru a învăța modelul.

Sintaxa

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;

Cum funcționează în practică:

PostgreSQL execută termenul de bază o dată.
Execută termenul recursiv folosind rândurile tocmai produse ca „input” (nu întregul CTE: doar incrementul).
Se repetă pasul 2 până când termenul recursiv produce 0 rânduri noi.
Rezultatul final este UNION ALL tuturor pașilor.

Exemplul 1: copac cu adâncime și cale

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;

Prima iterație produce 3 rânduri (rădăcinile de la nivelul 1). Al doilea iterația găsește cei 6 copii (nivelul 2). A treia iterație constată nimic → făcut.

Exemplul 2: dat un nod, găsiți rădăcina acestuia

O variantă „de sus în jos → de jos în sus”: propagam id-ul rădăcinii în jos copacul.

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;

Încearcă

Exercițiu#sql.m7.l4.e1

Încercări: 0Se încarcă…

Utilizați un CTE recursiv numit „arbore” pentru a aplatiza ierarhia categoriilor. Afișați id-ul, numele, adâncimea (1 pentru rădăcini, 2 pentru copii etc.) și calea (de exemplu, „Elettronica > Audio”). Ordonați după cale.

Se încarcă editorul...

Afișează indiciu

JOIN din termenul recursiv leagă c.parent_id la rândul deja prezent în arbore (t.id).

Soluție disponibilă după 3 încercări

Exercițiu de revizuire

Exercițiu#sql.m7.l4.e2