Quando utilizzare una Common Table Expression (CTE)?

Siete interessati a saperne di più sulle espressioni comuni di tabella o CTE? Volete sapere quando le CTE sono utili? Continuate a leggere: ne parleremo in questo articolo.

Se avete sentito parlare di espressioni di tabella comuni, probabilmente vi sarete chiesti a cosa servono. Anche se non ne avete sentito parlare, è bene che siate qui! Le CTE possono essere molto utili, soprattutto se avete già imparato le basi dell'SQL, come selezionare, ordinare, filtrare i dati e unire le tabelle. Andiamo subito al sodo e vediamo cosa sono le CTE e quando e come usarle.

Che cos'è un'espressione di tabella comune?

L'espressione di tabella comune (CTE) è una funzione di SQL relativamente nuova. È stata introdotta in SQL:1999, la quarta revisione di SQL, con standard ISO pubblicati dal 1999 al 2002 per questa versione di SQL.

Le CTE sono state introdotte per la prima volta in SQL Server nel 2005, poi PostgreSQL le ha rese disponibili a partire dalla versione 8.4 del 2009. MySQL ha aspettato un po' di più e li ha resi disponibili nel 2018, a partire dalla versione 8.0. In poche parole, si tratta di un set di dati temporaneo restituito da una query, che viene poi utilizzato da un'altra query. È temporaneo perché il risultato non viene memorizzato da nessuna parte, ma esiste solo quando viene eseguita la query.

Esistono due tipi di CTE:

• non ricorsivo
• ricorsiva

In questo articolo tratterò solo la CTE non ricorsiva e accennerò alla CTE ricorsiva solo brevemente alla fine.

La sintassi di base della CTE (non ricorsiva) è la seguente:

WITH expression_name AS (CTE definition)

Come si può vedere, viene eseguita utilizzando un'istruzione WITH. Per questo motivo, le CTE sono chiamate anche query WITH. Dopo l'istruzione WITH, si definisce una CTE tra parentesi. Definire una CTE significa semplicemente scrivere una query SELECT che fornisca un risultato da utilizzare all'interno di un'altra query.

Come si può vedere, si utilizza un'istruzione WITH. Per questo motivo, le CTE sono chiamate anche query WITH. Dopo l'istruzione WITH, si definisce una CTE tra parentesi. Definire una CTE significa semplicemente scrivere una query SELECT che fornisca un risultato da utilizzare in un'altra query.

SELECT ... 
FROM expression_name

Si definisce la query SELECT e poi si fa riferimento alla CTE, utilizzandola come si farebbe con qualsiasi altra tabella dopo la clausola FROM.

Se volete saperne di più sulle CTE prima di passare agli esempi, ecco un articolo che le spiega bene.

La sintassi delle CTE

Vediamo ora come funziona in pratica la sintassi CTE. Supponiamo che ci sia un database contenente vari dati dell'università con le seguenti tre tabelle:

• students
• subjects
• exams

La tabella students ha le seguenti colonne:

• id: l'ID dello studente, una chiave primaria
• first_name: il nome dello studente
• last_nameil cognome dello studente

La tabella successiva è la subjects tabella contenente i dati:

• idl'ID del soggetto, una chiave primaria
• subject_nameil nome della materia

La terza tabella è la tabella exams tabella che memorizza i seguenti dati:

• idID dell'esame sostenuto
• exam_date: la data in cui è stato dato l'esame
• subject_id: l'ID del soggetto, una chiave esterna della tabella subjects
• student_id: l'ID dello studente che ha sostenuto l'esame, una chiave esterna della tabella students

Il compito è quello di calcolare la media dei voti degli studenti. Poi, per ogni studente con voto medio superiore a 8,5, è necessario mostrare il nome, il cognome e il voto medio ed etichettarli come studenti "eccezionali". Come si può fare utilizzando un CTE?

Il codice che darà il risultato desiderato può essere scritto in questo modo:

WITH grade_average AS (
SELECT	s.id,
		s.first_name,
		s.last_name,
		AVG (e.grade) AS average_grade 
FROM students s JOIN exams e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
)

SELECT	first_name,
		last_name,
		average_grade,
		'exceptional' AS tag
FROM grade_average
WHERE average_grade>8.5;

Per prima cosa, è necessario definire la CTE. Come si è già appreso, questo si fa con l'istruzione WITH. È seguita dal nome della CTE, che in questo caso è grade_average in questo caso. Una query CTE è definita tra le parentesi. Se la si guarda da sola, non è complicata: è una query SELECT dall'aspetto piuttosto regolare. Seleziona id, first_name, e last_name dalla tabella students. Calcola anche la media dei voti, utilizzando la colonna voto della tabella exams. Il risultato viene mostrato nella nuova colonna average_grade. Le tabelle students e exams sono unite sulla colonna ID studente appropriata di ciascuna tabella. Il risultato è raggruppato dalle colonne id, first_name, e last_name della tabella students. I record sono raggruppati, poiché è necessario ottenere il risultato per studente.

Dopo aver definito la CTE, c'è un'altra query SELECT che utilizza la CTE. Questa query seleziona le colonne first_name, last_name e average_grade dalla CTE, grade_average. Assegna inoltre il valore "eccezionale". Alla fine è presente una clausola WHERE per mostrare solo gli studenti con una media dei voti superiore a 8,5.

Eseguendo la query si otterranno i nomi di tre studenti eccezionali.

Utilizzo di più CTE in una query

È possibile definire e utilizzare più CTE in una query. Lo si fa separando ogni CTE con una virgola e utilizzando l'istruzione WITH solo quando si definisce la prima CTE.

Vi mostrerò un esempio. Con le stesse tabelle dell'esempio precedente, si ha il seguente compito: mostrare il nome delle materie e i rispettivi voti medi e minimi, ma solo per le materie in cui tutti hanno superato l'esame, cioè il loro voto è 5 o superiore.

Per ottenere il risultato desiderato, la query dovrebbe avere questo aspetto:

WITH subject_average AS (
SELECT	su.id,
su.subject_name,
		AVG (e.grade) AS subject_average_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
),

min_grade AS (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS subject_min_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
HAVING MIN (e.grade) > 5
)

SELECT	sa.id,
		sa.subject_name,
		sa.subject_average_grade
FROM subject_average sa JOIN min_grade m ON sa.id =m.id;

Innanzitutto, viene definito un CTE chiamato subject_average viene definita. Essa seleziona le colonne id e subject_name dalla tabella subjects. Successivamente, calcola la media dei voti utilizzando i dati della tabella exams e assegna i risultati alla nuova colonna subject_average_grade. Quindi, raggruppa i dati per ottenere i risultati per materia.

Ora si definisce la seconda CTE. Ricordate quanto detto in precedenza: separate le CTE con le virgole e scrivete la seconda CTE omettendo la dichiarazione WITH. La seconda CTE si chiama min_grade. Anch'essa seleziona id e subject_name dalla tabella subjects quindi calcola i voti minimi, mostrando il risultato nella nuova colonna subject_min_grade. Raggruppa i dati come è stato fatto nella prima CTE. Poiché si ha bisogno del risultato solo per le materie in cui tutti sono passati, si utilizza una clausola HAVING per selezionare solo le materie in cui il voto minimo è 5 o superiore.

Infine, si scrive la query SELECT che mostrerà l'ID della materia, il nome della materia e il voto medio per ogni materia che soddisfa i criteri. Esistono solo due materie:

Una volta apprese le basi delle CTE, è disponibile il corso Recursive Queries con molti altri esempi in cui è possibile esercitarsi a scrivere la sintassi.

Quando usare le CTE

Le CTE consentono di eseguire aggregazioni a più livelli. Cosa sono?

Torniamo alle tabelle utilizzate negli esempi precedenti. Ora si tratta di calcolare la media dei voti minimi e la media dei voti massimi per materia.

Da dove iniziare? Se si ragiona in modo logico, si dovrebbero prima trovare i voti minimi e massimi per materia e poi trovare la media dei risultati per materia. Il codice è semplice e si presenta così:

SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade,
		AVG (MIN (e.grade)) AS avg_min_grade,
		AVG (MAX (e.grade)) AS avg_max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name;

Logicamente, si cerca di calcolare prima il voto minimo e il voto massimo per materia, poi la media di questi valori. Voilà! Ora si esegue il codice e si ottiene un messaggio simile a questo:

Msg 130, Level 15, State 1, Line 16
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Il risultato non è quello sperato? Perché SQL non consente costruzioni come AVG (MIN (e.grade)). I vostri pensieri erano corretti, ma dovete usare una CTE per tradurli in un codice SQL. Ecco come fare:

WITH min_max_grade AS (
SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
)

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM min_max_grade;

La CTE si chiama min_max_grade. In essa è presente un'istruzione SELECT che calcola i voti minimi e massimi per materia, come intendevo fare nella query che ha restituito il messaggio di errore. Il risultato è mostrato nelle nuove colonne min_grade e max_grade. Ora una CTE aiuta a tradurre la logica in codice.

Dopo aver definito la CTE, si scrive un'istruzione SELECT che calcola la media di min_grade e max_grade dalla CTE. Il risultato sarà mostrato nelle nuove colonne avg_min_grade e avg_max_grade. Ora che lo vedete, è facile, vero?

Le CTE sono molto utili anche quando si devono organizzare query lunghe e complesse. L'uso delle CTE migliora la leggibilità del codice, poiché lo suddivide in passi separati. Diventa più facile modificare il codice o correggere gli errori. Se si insiste nel non utilizzare le CTE, il codice potrebbe avere questo aspetto:

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
	FROM subjects su JOIN exams e ON su.id = e.subject_id
	GROUP BY su.id, su.subject_name
) AS min_max;

Rispetto alla soluzione che utilizza una CTE, questa sembra un po' troppo complessa e più difficile da leggere. La lettura delle subquery può essere difficile, perché bisogna prima pensare a cosa fa ogni subquery, poi tornare alla query principale e in qualche modo collegarle tutte nella propria testa. Inoltre, l'uso di subquery di questo tipo è contrario al funzionamento logico della mente e al modo in cui si pensa ai passaggi che portano alla soluzione. Ricordate che avete suddiviso il problema in due fasi: prima calcolate i voti minimi e massimi per ogni materia, poi calcolate la media dei voti minimi e massimi. Il codice CTE rispecchia esattamente questo ordine.

La logica del codice con una sottoquery è l'opposto di come è stata pensata la soluzione. Qui si scrive prima che si vuole una media dei voti, poi si specifica nella subquery che si vuole che le medie siano dei voti minimi e massimi. Quando si usa una sottoquery, il modo in cui si scrive il codice generalmente va contro il modo in cui si pensa alla logica.

E se il codice con una subquery è meno leggibile e più difficile da capire di quello con una CTE in questo semplice esempio, immaginate come sarebbe se doveste scrivere query più complesse! Vi grattereste la testa, cercando di capire cosa fa ogni parte del codice. Le difficoltà di comprensione di un codice possono essere molto frustranti. È qui che le CTE possono aiutarvi.

Probabilmente avrete notato che le CTE sono molto simili alle subquery. Forse vi starete chiedendo perché sto usando le CTE quando tutto ciò che ho fatto potrebbe essere fatto con le subquery. È vero, ma oltre a essere più leggibili, le CTE hanno un grande vantaggio rispetto alle subquery: i risultati di una CTE possono essere utilizzati più di una volta in una query. Se siete interessati ad approfondire questo argomento, vi consiglio di leggere le differenze tra CTE e subquery.

Ho già detto che le CTE possono essere non ricorsive o ricorsive. Finora abbiamo esaminato solo le CTE non ricorsive. Le CTE ricorsive sono CTE che fanno riferimento a se stesse; così facendo, restituiscono il risultato secondario e ripetono il processo fino a restituire il risultato finale. L'uso delle CTE ricorsive permette di sfruttare appieno le potenzialità delle CTE; sono utili quando si elaborano strutture gerarchiche, come alberi e grafi.

Avete capito come si usano le CTE?

In questo articolo abbiamo trattato alcune delle basi delle CTE. Abbiamo imparato cos'è una CTE, ne abbiamo compreso la sintassi e abbiamo esaminato alcuni semplici esempi per capire cosa possono fare le CTE. Ho anche indicato alcuni usi comuni delle CTE per aiutarvi a trovare un modo per utilizzarle nel vostro studio o lavoro. Spero di avervi dato delle buone indicazioni; ora tocca a voi mettere in pratica ciò che avete imparato.

Se avete domande o commenti, fatemelo sapere nella sezione commenti!