cte e query ricorsive

cte base

le cte rendono la query leggibile e modulare.

WITH base AS (
  SELECT id, amount FROM orders WHERE status = 'paid'
)
SELECT * FROM base WHERE amount > 100;

cte ricorsive

usa UNION ALL per attraversare alberi.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

best practice

limita la profondità e usa indici sulle chiavi padre/figlio.

esercizi

calcola il percorso completo per ogni categoria (breadcrumb).

estrai solo i nodi fino a profondità 3.

checklist

• usa cte per query modulari
• indici su parent_id
• limita la profondità
• verifica il piano di esecuzione

panoramica

In questo capitolo su cte e query ricorsive, organizza query lunghe e gestisci gerarchie. L'obiettivo è trasformare i concetti in micro-pattern riutilizzabili con esempi piccoli e verificabili.

Lavora in sequenza: leggi, prova, modifica gli snippet e annota i trade-off principali (performance, leggibilità, manutenzione).

obiettivi

• capire e applicare cte in uno scenario reale
• capire e applicare recursive in uno scenario reale
• capire e applicare best practice in uno scenario reale
• capire e applicare esercizi in uno scenario reale

scheda rapida

SELECT categoria, COUNT(*) AS tot
FROM prodotti
WHERE attivo = TRUE
GROUP BY categoria
ORDER BY tot DESC
LIMIT 5;

Adatta questo scheletro agli esempi della lezione e sostituisci i dati con il tuo dominio.

mini progetto

Esplora gerarchia organizzativa.

• definisci anchor
• aggiungi recursive step
• limita livello
• ordina per profondità

output atteso: uno script o query ripetibile con risultati verificabili.