0Pricing
SQL Academy · Lezione

Percorrere un albero di categorie

Espanda completamente gli alberi padre-figlio

Percorrere un albero di categorie è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è un albero di categorie?

Molti dataset reali presentano una relazione padre-figlio. Un catalogo di prodotti può avere categorie come Elettronica → Telefoni → Smartphone. Ogni nodo ha un padre e forma una struttura ad albero.

In SQL, questa struttura viene generalmente memorizzata in una tabella autoreferenziale: ogni riga ha un id e un parent_id che punta a un'altra riga della stessa tabella.

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

Dati di esempio delle categorie

Popoliamo un piccolo albero di categorie. Il nodo radice ha parent_id = NULL perché non ha un padre. Ogni altro nodo punta al proprio padre tramite un parent_id non nullo.

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

Il problema delle query semplici

Una semplice SELECT può recuperare un solo livello alla volta. Per raggiungere una profondità di tre livelli servirebbero tre query separate o tre self join, una soluzione che diventa ingestibile man mano che l'albero cresce.

WITH RECURSIVE risolve il problema permettendo a una query di fare riferimento al proprio output e di percorrere l'albero livello per livello, finché non vengono trovate nuove righe.

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

Anatomia di WITH RECURSIVE

Una CTE ricorsiva è composta da due parti separate da UNION ALL:

1. Membro ancora — una SELECT normale che fornisce le righe iniziali.

2. Membro ricorsivo — una SELECT che ricollega la CTE a sé stessa, producendo il livello successivo a ogni iterazione.

Il motore ripete il membro ricorsivo finché questo non restituisce più righe.

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

Percorrere l'intero albero dalla radice

Si parte dalla radice (dove parent_id IS NULL) e si scende fino a ogni discendente. Il membro ricorsivo ricollega ogni riga accumulata a categories in base alla relazione genitore-figlio.

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

Tenere traccia del percorso

È utile registrare il percorso completo dalla radice a ogni nodo. Possiamo costruire una stringa path concatenando i nomi degli antenati man mano che scendiamo nella ricorsione.

In questo modo è facile visualizzare percorsi breadcrumb come Elettronica / Telefoni / Smartphone.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

Partire da un nodo specifico

Non è necessario partire dalla radice. Modificando la clausola WHERE dell'ancora, è possibile percorrere il sottoalbero di qualsiasi nodo. Qui partiamo da Telefoni (id = 2) e recuperiamo tutti i suoi discendenti.

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

Percorrere l'albero verso l'alto: trovare tutti gli antenati

È anche possibile percorrere l'albero al contrario, risalendo da una foglia fino alla radice. Basta invertire il JOIN in modo da seguire parent_id verso l'alto anziché verso il basso. È utile quando serve il breadcrumb completo di un nodo foglia noto.

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

Aggiungere una visualizzazione rientrata

Un modello comune nelle interfacce utente consiste nell'applicare visivamente un rientro ai nodi figli. Possiamo usare REPEAT (o LPAD) insieme alla colonna depth per anteporre spazi a ogni nome e produrre una visualizzazione dell'albero basata sul testo.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

Prevenire i cicli infiniti

Se i dati contengono un ciclo (A è il genitore di B e B è il genitore di A), la ricorsione continuerà all'infinito causando un arresto anomalo. È possibile prevenirlo tenendo traccia degli ID già visitati in un array e fermandosi quando l'ID corrente è già presente.

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

Contare i discendenti per ogni nodo

Una volta ottenuto l'albero completo, è possibile aggregare i dati. Qui contiamo quanti discendenti ha ogni nodo raggruppando nuovamente le righe figlie in base all'elenco degli antenati. È utile per visualizzare il numero di elementi accanto ai nomi delle categorie in un menu di navigazione.

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

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

Verifica rapida

Verifichi la Sua comprensione delle query ricorsive per gli alberi di categorie.

Riepilogo della lezione

In questa lezione ha imparato a percorrere una tabella di categorie autoreferenziale usando WITH RECURSIVE.

Punti chiave:

- Il membro ancora seleziona i nodi iniziali (di solito la radice).

- Il membro ricorsivo ricollega la CTE alla tabella di base per trovare il livello successivo.

- Aggiunga una colonna depth per tenere traccia del numero di livelli di profondità di ogni nodo.

- Costruisca una stringa path per generare percorsi breadcrumb.

- Percorra l'albero verso l'alto seguendo parent_id al contrario per trovare tutti gli antenati.

- Usi un array visited per proteggersi dai cicli presenti in dati non validi.

Domande Frequenti

La lezione «Percorrere un albero di categorie» è gratuita?

Sì — il testo completo di «Percorrere un albero di categorie» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Percorrere un albero di categorie»?

Espanda completamente gli alberi padre-figlio Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Percorrere un albero di categorie»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Come funzionano le CTE ricorsive
  2. Percorrere un albero di categorie
  3. Generare serie e sequenze
  4. Evitare i cicli infiniti
← Torna a SQL Academy