Lesson 5

Lezione 5: CTE — Organizzare le Tue Query come un Posh Tea Party Inglese!

Bentornato, appassionato di dati! Oggi ci tufferemo nel mondo delle Common Table Expressions (CTE) — il modo elegante per mantenere le tue query SQL ordinate e pulite. Immagina i CTE come dei maggiordomi di SQL: preparano tutto con cura, fanno in modo che i tuoi dati scorrono senza intoppi e con un tocco di classe.

Cos’è un CTE e Perché Ti Serve?

I CTE sono come set di risultati temporanei, con un nome proprio, che esistono solo per la durata della tua query. Immagina di chiamare un maggiordomo per servire il tè del pomeriggio. Prepara tutto (i tuoi dati), lo dispone sul tavolo (il tuo CTE) e poi scompare silenziosamente appena finisci. Il tavolo rimane in ordine, e tu puoi gustarti il tè senza muovere un dito!

I CTE sono perfetti per:

Scomporre query complesse in parti leggibili.
Riutilizzare la stessa query più volte senza duplicarla.
Rendere il tuo SQL elegante come una tazza di Earl Grey!

Creare un CTE: La Clausola `WITH`

Un CTE viene definito usando la parola chiave WITH, seguita da un nome (proprio come assegnare un nome al tuo maggiordomo). Poi specifichi la query che crea il CTE e lo utilizzi come se fosse una tabella separata:

WITH missioni_cavalieri AS (
  SELECT nome, regno, COUNT(missione) AS totale_missioni
  FROM missioni
  GROUP BY nome, regno
)
SELECT * FROM missioni_cavalieri;

In questo esempio, il CTE missioni_cavalieri conteggia il numero di missioni per cavaliere e regno. Una volta definito, puoi usarlo nella tua query principale, rendendola molto più facile da leggere.

Più CTE: Quando Vuoi Invitare Altri Maggiordomi alla Festa

Puoi definire più CTE concatenandoli con virgole. Supponiamo che tu voglia vedere quali cavalieri sono veterani (più di 3 missioni) e quali regni dominano per coraggio:

WITH missioni_cavalieri AS (
  SELECT nome, regno, COUNT(missione) AS totale_missioni
  FROM missioni
  GROUP BY nome, regno
),
cavalieri_veterani AS (
  SELECT nome, regno
  FROM missioni_cavalieri
  WHERE totale_missioni > 3
)
SELECT cavalieri_veterani.nome, cavalieri_veterani.regno
FROM cavalieri_veterani
JOIN missioni_cavalieri ON cavalieri_veterani.nome = missioni_cavalieri.nome;

Qui, missioni_cavalieri è il nostro primo CTE, e cavalieri_veterani è costruito sopra di esso. È come chiamare un secondo maggiordomo per mantenere tutto in ordine!

CTE Ricorsivi: Quando Un Maggiordomo Non Basta

I CTE diventano ancora più potenti quando li usi in modo ricorsivo. Immagina di voler creare un albero genealogico dei cavalieri: Sir Arthur ha addestrato Sir Lancillotto, che a sua volta ha allenato Sir Galahad, e così via. Con un CTE ricorsivo puoi scendere e risalire questo albero famigliare:

WITH RECURSIVE genealogia_cavalieri AS (
  SELECT nome, mentore, 1 AS generazione
  FROM cavalieri
  WHERE mentore IS NULL
  
  UNION ALL
  
  SELECT c.nome, c.mentore, g.generazione + 1
  FROM cavalieri c
  JOIN genealogia_cavalieri g ON c.mentore = g.nome
)
SELECT * FROM genealogia_cavalieri;

Questa query costruisce una genealogia, partendo dal cavaliere più alto in grado (senza mentore) e scendendo attraverso le generazioni. È come avere un’intera squadra di maggiordomi, ognuno responsabile della propria parte della famiglia!

Perché I CTE Sono Meglio dei Sottoquery

Anche se i sottoquery sono utili, possono diventare disordinati se usati troppo spesso. I CTE, invece, mantengono il tuo SQL chiaro e ordinato:

Leggibilità: I CTE suddividono le query complesse in sezioni comprensibili.
Riutilizzabilità: Definisci una volta, usa quante volte vuoi.
Debugging: Più facile capire dove le cose vanno storte.

Quindi, la prossima volta che lotti con una query mostruosa, chiama un CTE-maggiordomo per mantenere il tuo SQL bello e ordinato.

Mettiamo Tutto Insieme: Un Grandioso Ballo dei CTE

Ecco un esempio finale che riassume tutto:

Vuoi elencare tutti i cavalieri che hanno più di 3 missioni e scoprire quale regno ha la più alta percentuale di cavalieri veterani. Spezziamo la query con alcuni CTE ben piazzati:

WITH missioni_cavalieri AS (
  SELECT nome, regno, COUNT(missione) AS totale_missioni
  FROM missioni
  GROUP BY nome, regno
),
cavalieri_veterani AS (
  SELECT nome, regno
  FROM missioni_cavalieri
  WHERE totale_missioni > 3
),
coraggio_regno AS (
  SELECT regno, COUNT(*) AS numero_veterani, 
         (COUNT(*)::float / (SELECT COUNT(*) FROM cavalieri WHERE cavalieri.regno = coraggio_regno.regno)) * 100 AS percentuale_veterani
  FROM cavalieri_veterani
  GROUP BY regno
)
SELECT regno, percentuale_veterani
FROM coraggio_regno
ORDER BY percentuale_veterani DESC;

Questa elegante configurazione mostra come i CTE possano rendere gestibili anche le richieste di dati più complesse.

Cosa Abbiamo Imparato Oggi?

Oggi ci siamo addentrati nel mondo dei CTE:

CTE di Base: Organizza le tue query in sezioni leggibili.
CTE Multipli: Concatenali insieme per una maggiore chiarezza.
CTE Ricorsivi: Perfetti per dati gerarchici.

Cosa Ci Aspetta?

Nella prossima lezione, sveleremo i misteri delle Window Functions — strumenti che possono elevare la tua analisi dei dati a un livello regale!

Allora, sei pronto a servire un po’ di eleganza nei tuoi dati con i CTE? Metti il tuo miglior vestito SQL e preparati per il prossimo capitolo!