Le CTE di SQL spiegate con esempi

Scoprite come sfruttare la potenza delle Espressioni di tabella comuni (CTE) per migliorare l'organizzazione e la leggibilità delle vostre query SQL.

L'abbreviazione comunemente usata CTE sta per Common Table Expression.

Per imparare a conoscere le Espressioni di Tabella Comune SQL attraverso la pratica, vi consiglio il corso interattivo Recursive Queries su LearnSQL.it. Contiene oltre 100 esercizi pratici sulle CTE ricorsive semplici e complesse.

Che cosa fa una CTE? Perché è opportuno utilizzarne una nel codice SQL? Rispondiamo a queste domande.

Che cos'è un CTE?

Una Common Table Expression è un insieme di risultati temporanei con nome. Si crea una CTE utilizzando una query WITH, quindi si fa riferimento ad essa in un'istruzione SELECT, INSERT, UPDATE o DELETE.

Supponiamo di avere una tabella chiamata schools con le colonne school_id, school_name, district_id e il numero di studenti. È necessario scrivere una query per visualizzare un elenco di scuole insieme all'ID del loro distretto e al numero medio di studenti per scuola in quel distretto.

La logica potrebbe essere la seguente:

1. Creare una tabella con l'elenco dei distretti e il corrispondente numero medio di studenti per scuola.
2. Unire questa tabella all'elenco delle scuole e visualizzare le informazioni richieste.
3. Eliminare la tabella con il numero medio di studenti per scuola per ogni distretto.

Se si utilizza una CTE, non è necessario creare e abbandonare una tabella. Si può semplicemente fare riferimento all'insieme temporaneo di risultati creato dalla query WITH, come si vede qui sotto:

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

Quindi, si inizia definendo l'insieme di risultati temporaneo avg_students nella query WITH. Tra le parentesi c'è un'istruzione SELECT che definisce questo insieme di risultati; contiene un elenco di distretti e il corrispondente numero medio di studenti per scuola. Dopo la parentesi di chiusura, inizia l'istruzione SELECT principale. Si noti che si fa riferimento all'insieme temporaneo di risultati come a una normale tabella, usando il nome assegnato (avg_students). Il risultato includerà il nome della scuola, l'ID del suo distretto e il numero medio di studenti in questo distretto.

Nel senso che non può essere utilizzato in altre query SQL, l'insieme temporaneo di risultati viene "abbandonato". Naturalmente, è possibile definirlo nuovamente se necessario.

Ora che si è acquisita una conoscenza di base delle espressioni di tabella comuni e della loro sintassi, è il momento di vedere come utilizzare le CTE in casi aziendali reali.

Le CTE in azione

Cominciamo con l'esplorare i dati. Supponiamo di essere un analista di dati di una banca al dettaglio e di voler analizzare i bonus assegnati ai dipendenti il mese scorso. La tabella seguente è il punto di partenza:

Bonus_giugno

Ora supponiamo di voler vedere il bonus pagato a ciascun dipendente insieme al bonus medio per la sua posizione. A tal fine, è necessario calcolare il bonus medio per ogni posizione. Questo può essere fatto in un set di risultati temporaneo (un CTE). L'intera query avrà il seguente aspetto:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

Come si può vedere, si inizia definendo l'insieme temporaneo di risultati avg_position. Poi c'è l'istruzione SELECT principale, in cui si unisce la tabella bonus_jan con l'insieme temporaneo di risultati avg_position per visualizzare le informazioni su ogni dipendente, il suo bonus e il bonus medio per quella posizione:

Più CTE in una query

È possibile avere più espressioni di tabella comuni in una query: basta usare una parola chiave WITH e separare le CTE con delle virgole.

Supponiamo di voler confrontare il bonus di ogni dipendente con il bonus medio per quella posizione e il bonus medio per quella regione. A tale scopo, creare due set di risultati temporanei: uno con il bonus medio per ogni posizione e un altro con il bonus medio per ogni regione. Ecco l'intera query:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

Dopo aver definito gli insiemi temporanei di risultati avg_position e avg_region, si scrive l'istruzione SELECT principale per visualizzare i bonus medi di posizione e regione insieme alle informazioni di ciascun dipendente:

CTE annidate

Le espressioni di tabella comuni possono essere anche annidate. Ciò significa avere più CTE nella stessa query , dove almeno una CTE fa riferimento a un'altra CTE. Questo sarà più chiaro dopo aver visto un esempio.

Supponiamo di voler valutare le prestazioni dei diversi punti vendita della rete retail della banca. In particolare, si vuole confrontare il bonus medio dei dipendenti per ogni punto vendita con il bonus medio minimo e massimo dei punti vendita.

La logica potrebbe essere la seguente:

1. Calcolare il bonus medio dei dipendenti per ogni punto vendita (CTE: avg_per_outlet).
2. Trovare il bonus medio minimo tra i punti vendita (CTE: min_bonus_outlet).
3. Trovare il bonus medio massimo tra i punti vendita (CTE: max_bonus_outlet).
4. Creare l'ID di ogni punto vendita e il bonus medio per questo punto vendita e i bonus medi minimi e massimi tra i punti vendita.

Per creare le CTE min_bonus_outlet e max_bonus_outlet, è necessario fare riferimento alla prima CTE, avg_per_outlet. Ecco l'intera query:

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Si noti che ci sono tre diverse Common Table Expressions; due di esse (min_bonus_outlet e max_bonus_outlet) fanno riferimento a un'altra CTE (avg_per_outlet). Si tratta quindi di CTE annidate.

Nell'istruzione SELECT principale, visualizziamo l'ID del punto vendita, il bonus medio per tutti i dipendenti di questo punto vendita e i bonus medi minimi e massimi tra i punti vendita. A tal fine, effettuiamo un cross join di tutti e tre gli insiemi temporanei di risultati. Ecco l'output di questa query:

Per altri esempi di CTE, consultate le guide introduttive di LearnSQL.itsu cos'è una CTE e quando si dovrebbe usare.

Perché usare una CTE?

Si sarà notato che nella maggior parte dei casi si possono usare una o più subquery invece delle CTE. Quindi, perché usare una CTE?

• Le espressioni di tabella comuni organizzano meglio le query lunghe. Le subquery multiple hanno spesso un aspetto disordinato.
• Inoltre, le CTE rendono la query più leggibile, perché hanno un nome per ciascuna delle espressioni di tabella comuni utilizzate nella query.
• Le CTE organizzano la query in modo che rifletta meglio la logica umana. Con le CTE, si inizia definendo l'insieme o gli insiemi temporanei di risultati e poi si fa riferimento ad essi nella query principale. Con le subquery, si inizia con la query principale e poi si inseriscono le subquery al centro della query.
• Infine, esiste anche una categoria specifica di CTE, chiamate CTE ricorsive, che possono fare riferimento a se stesse. Queste CTE possono risolvere problemi che non possono essere affrontati con altre query. Le query ricorsive sono particolarmente utili quando si lavora con dati gerarchici.

Per saperne di più sulle CTE ricorsive, consultate le nostre guide approfondite sulle funzionalità delle query ricorsive SQL e delle query gerarchiche in PostgreSQL e Oracle.

È ora di fare pratica con le espressioni di tabella comuni!

Siete entusiasti di sfruttare la potenza delle CTE nelle vostre query SQL? Se volete scrivere le CTE come un professionista, dovete fare molta pratica.

LearnSQL.it offre un corso completo su Recursive Queries. Include 114 esercizi interattivi su CTE semplici, CTE annidati e CTE ricorsivi. Questo corso è l'occasione perfetta per imparare a gestire le query SQL con le Common Table Expressions, come e quando annidare le CTE e come usare le CTE ricorsive.

Volete saperne di più sulle CTE SQL? Consultate le nostre guide per principianti:

• Cos'è una CTE?
• Quando utilizzare un'espressione di tabella comune (CTE)?
• Come organizzare le query SQL con le CTE

Buon apprendimento!