Che cos'è una CTE?

Dopo aver imparato a conoscere istruzioni come SELECT, DELETE, INSERT e GROUP BY, si potrebbe cercare un modo per migliorare la manutenibilità, la riproducibilità e la leggibilità del codice. A questo punto, probabilmente inizierete a conoscere i concetti moderni di SQL, introdotti nei primi anni 2000. Una di queste tecniche SQL è la CTE? (Common Table Expression), un insieme di risultati temporanei. In questo articolo scoprirete cos'è una CTE e come utilizzarla per migliorare la manutenzione e la leggibilità del vostro codice.

CTE: definizione e sintassi di base

Un'espressione di tabella comune, o CTE, è un insieme di risultati temporanei con nome creato da una semplice istruzione SQL che può essere utilizzata nelle successive istruzioni SELECT, DELETE, INSERT, o UPDATE.

Cominciamo con un esempio. Consideriamo le due tabelle seguenti:

• job_offers: una tabella che contiene i ruoli della scienza dei dati, con gli stipendi per livello e per località nel Regno Unito.

  role	location	level	salary
  data scientist	London	entry	45000
  data scientist	outside London	entry	34000
  data scientist	London	mid	65000
  data scientist	outside London	mid	60000
  data scientist	London	tech lead	95000
  data scientist	outside London	tech lead	73000
  data scientist	London	director	140000
  data scientist	outside London	director	101000
  quantitative analyst	London	entry	55000
  quantitative analyst	outside London	entry	43000
  quantitative analyst	London	mid	83000
  quantitative analyst	outside London	mid	66000
  quantitative analyst	London	tech lead	100000
  quantitative analyst	outside London	tech lead	72000
  quantitative analyst	London	director	155000
  quantitative analyst	outside London	director	120000
  machine learning engineer	London	entry	44000
  machine learning engineer	outside London	entry	36000
  machine learning engineer	London	mid	67000
  machine learning engineer	outside London	mid	58000
  machine learning engineer	London	tech lead	95000
  machine learning engineer	outside London	tech lead	84000

• employee_occupation: una tabella che contiene 5 dipendenti dell'azienda X, con le rispettive professioni e sedi di lavoro.

  name	role	location
  Tim Smith	data scientist	London
  Joana Lo	quantitative analyst	outside London
  Ed Merith	machine learning engineer	London
  Maria Soldini	machine learning engineer	outside London
  Tina Morito	quantitative analyst	outside London

Immaginate di voler elencare tutti i dipendenti con lo stipendio medio corrispondente al loro ruolo specifico (colonna role). Ciò che si deve fare è:

1. Calcolate il salario medio per ogni ruolo. A tale scopo, è possibile utilizzare la tabella job_offers tabella.
2. Unire i valori aggregati con la tabella employee_occupation tabella (cioè unirli).

Ecco la CTE per calcolare e visualizzare l'output desiderato:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
)
SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN average_salary b 
ON a.role = b.role;

Ecco cosa succede nel codice:

1. La definizione della CTE inizia con la parola chiave speciale WITH.
2. Dopo WITH, alla CTE viene assegnato un nome. Nel nostro esempio, il nome della CTE è average_salary.
3. Il nome della CTE è seguito da una parola chiave speciale AS.
4. All'interno delle parentesi si trova l'istruzione SELECT, il cui insieme di risultati viene memorizzato come CTE. Nel nostro esempio, il set di risultati temporaneo average_salary viene creato con l'istruzione SELECT.
5. Ora che si dispone di una CTE, è possibile utilizzarla in un'istruzione SQL successiva facendovi riferimento come per qualsiasi altra tabella. In questo caso, utilizziamo il nostro insieme temporaneo di risultati in un'istruzione JOIN. Segue la query principale, in cui viene utilizzata la CTE average_salary viene utilizzata.
6. L'insieme temporaneo di risultati average_salary è presente solo nell'ambito dell'istruzione immediatamente successiva alla clausola WITH. Nel nostro esempio, ciò significa che average_salary scompare automaticamente dopo l'esecuzione della query principale e non può essere utilizzato in nessun altro punto del codice. È come se si creasse una tabella, la si utilizzasse in un'istruzione SQL e poi la si cancellasse o eliminasse dal database.

Di conseguenza, SQL restituisce per ogni dipendente il salario medio per il suo ruolo:

La sintassi di base per l'utilizzo delle CTE è la seguente:

Come si può vedere dall'immagine, definiamo un insieme di risultati temporaneo (nel nostro esempio, average_salary) e poi lo utilizziamo nella stessa istruzione (nel nostro esempio, average_salary viene utilizzato nell'istruzione JOIN ). Esiste un ottimo corso, Recursive queries, offerto da LearnSQL.it Se volete saperne di più sulla sintassi CTE, vi suggerisco di iniziare da questo.

Subquery e CTE

Avrete notato che il nostro ultimo esempio può essere scritto con una subquery come questa:

SELECT a.*, b.avg_salary  
FROM employee_occupation a 
LEFT JOIN 
  (SELECT role, avg(salary) AS avg_salary 
   FROM job_offers 
   GROUP BY role) b
ON a.role = b.role;

Anche se scritto in modo diverso, restituirà lo stesso identico risultato del codice CTE di prima. Ecco cosa fa questo codice con una subquery:

• All'interno dell'istruzione JOIN, abbiamo una subquery tra parentesi: "select role, avg(salary) as avg_salary from job_offers group by role" è una sottoquery.
• Il motore SQL esegue prima questa subquery, quindi esegue la join con il risultato restituito dalla subquery.

Anche se una subquery restituisce lo stesso risultato di un'istruzione che utilizza una CTE, in casi come questo consiglio sempre le CTE rispetto alle subquery. Perché? Perché rendono il codice più leggibile e comprensibile. Query lunghe e complicate possono avere molte, molte subquery che diventano rapidamente difficili da leggere e da seguire.

Più CTE in una dichiarazione

Finora abbiamo utilizzato un solo insieme di risultati temporanei in una clausola WITH. Cosa succede se abbiamo bisogno di creare più CTE in una singola istruzione? Questo può accadere se si deve riscrivere un'istruzione SQL con molte sottoquery. È possibile farlo: è possibile avere più CTE in una singola istruzione SQL.

Facciamo un esempio con due sottoquery. Immaginiamo di notare le differenze di stipendio in base alla sede, oltre alla differenza per ruolo. Si desidera visualizzare ulteriori informazioni nell'output, ovvero la retribuzione media per ruolo e sede, e visualizzarla con le informazioni a livello di dipendente.

In questo caso, oltre alla sottoquery come la seguente che calcola lo stipendio medio per ruolo:

SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role

è necessario scrivere un'altra sottoquery come questa:

SELECT role, location, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role, location

Utilizzando queste sottoquery, il sito SELECT finale avrà il seguente aspetto:

SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT join 
  (SELECT role, avg(salary) as avg_salary 
   FROM job_offers 
   GROUP BY role) b 
ON a.role = b.role
LEFT JOIN 
  (SELECT role, location, avg(salary) AS avg_salary_additional 
   FROM job_offers 
   GROUP BY role, location) c
ON a.role = c.role AND a.location = c.location;

La query finale appare ora molto più complicata e difficile da seguire.

Ecco come appare la stessa query utilizzando le CTE:

WITH average_salary AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
average_salary_additional AS (
  SELECT 
    role, location, avg(salary) AS avg_salary_additional 
  FROM job_offers 
  GROUP BY role, location
)
SELECT a.*, b.avg_salary, c.avg_salary_additional 
FROM employee_occupation a
LEFT JOIN average_salary b 
  ON a.role = b.role
LEFT JOIN average_salary_additional c 
  ON a.role = c.role and a.location = c.location;

Sembra migliore, vero? Qui abbiamo definito due CTE. Ecco cosa succede in questo codice:

• Due CTE sono definite e utilizzate all'interno di una singola clausola WITH: average_salary e average_salary_additional. Sono separate da una virgola all'interno della stessa istruzione WITH. Ogni sottoquery è denominata separatamente, rendendo più leggibile il sito SELECT finale.
• La prima CTE SQL (average_salary) memorizza il salario medio per ruolo. La seconda CTE (average_salaries_additional) memorizza il salario medio per ogni combinazione di ruolo e sede nella tabella job_offers.

• Una volta definite, entrambe le CTE vengono utilizzate nella query principale in un'unica istruzione. Il risultato mostra tutte le medie, la media per ruolo e la media per ogni combinazione di ruolo e sede, con i dettagli di ciascun dipendente:

  name	role	location	avg_salary	avg_salary_additional
  Tim Smith	data scientist	London	76625	86250
  Ed Merith	machine learning engineer	London	64000	68666.6667
  Maria Soldini	machine learning engineer	outside London	64000	59333.3333
  Joana Lo	quantitative analyst	outside London	86750	75250
  Tina Morito	quantitative analyst	outside London	86750	75250

• Una volta eseguita questa singola istruzione SQL e visualizzato il risultato sullo schermo, entrambe le CTE vengono eliminate e non sono più disponibili per ulteriori utilizzi nel codice.

Lo stesso risultato può essere ottenuto con funzioni SQL a finestra e una sola CTE, ma in questo caso abbiamo utilizzato più espressioni di tabella comuni. Questo è un buon esempio di come si possano usare due CTE in una query.

Si sarà notato che ogni CTE del nostro esempio legge una tabella SQL denominata job_offers. Naturalmente, ogni CTE può leggere tabelle diverse dal database o anche leggere un'altra CTE definita nella stessa istruzione. In altre parole, l'annidamento è consentito quando si lavora con le CTE: una CTE può leggere o fare riferimento a un'altra CTE. L'unico requisito è che le CTE siano definite nella stessa query WITH.

Ecco un esempio con le CTE annidate:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

In questo codice:

• Abbiamo definito due CTE: avg_salary_per_role e min_avg_salary. La prima CTE (avg_salary_per_role) calcola il salario medio per ruolo. La seconda CTE (min_avg_salary) legge la prima CTE (avg_salary_per_role) e calcola il minimo dei salari medi a livello di ruolo. Le retribuzioni medie sono state calcolate dalla prima CTE.

• La query principale utilizza entrambe le CTE in un'istruzione JOIN e restituisce il ruolo con la retribuzione media più bassa tra tutti i ruoli:

  role	avg_salary	min_avg_salary
  machine learning engineer	64000	64000

• Una volta eseguita l'istruzione SQL e visualizzato il risultato sullo schermo, entrambe le CTE vengono eliminate e non sono più disponibili per ulteriori utilizzi nel codice.

Creazione di tabelle e CTE

Abbiamo visto come l'uso delle CTE sia un'ottima alternativa alle subquery. È anche un'ottima alternativa alla creazione di una vera tabella.

Spesso gli sviluppatori creano tabelle temporanee nel database, le usano nella query successiva e poi le abbandonano. Questo approccio può essere sostituito da uno che utilizza le CTE.

Perché ne parlo? Prima di iniziare a lavorare con le CTE, facevo esattamente quello che ho descritto sopra.

Consideriamo una query dell'ultimo esempio, che ha questo aspetto:

WITH avg_salary_per_role AS (
  SELECT role, avg(salary) AS avg_salary 
  FROM job_offers 
  GROUP BY role
),
min_avg_salary AS (
  SELECT min(avg_salary) AS min_avg_salary 
  FROM avg_salary_per_role
)
SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

Io l'avrei scritta così:

CREATE TABLE avg_salary_per_role AS
SELECT role, avg(salary) AS avg_salary 
FROM job_offers 
GROUP BY role;

CREATE TABLE min_avg_salary AS
SELECT min(avg_salary) AS min_avg_salary 
FROM avg_salary_per_role;

SELECT * 
FROM avg_salary_per_role a 
INNER JOIN min_avg_salary b 
ON a.avg_salary = b.min_avg_salary;

DROP TABLE avg_salary_per_role;
DROP TABLE min_avg_salary;

Sebbene entrambe le soluzioni diano lo stesso risultato, è meglio usare le CTE per non doversi preoccupare dei metadati? Non è necessario ricordare i drop delle tabelle, perché il motore SQL se ne occupa per voi con le CTE. Bello, vero?

CTE non ricorsive e ricorsive

Finora abbiamo parlato delle cosiddette CTE "non ricorsive". In generale, tuttavia, i CTE si dividono in due tipi principali:

• CTE non ricorsivi
• CTE ricorsivi

Un CTE ricorsivo è una forma speciale di CTE annidato. Una CTE ricorsiva è una CTE che fa riferimento a se stessa all'interno della stessa espressione di tabella comune.

Ecco come si presenta una CTE ricorsiva:

WITH recursive_cte (column_list)
AS
(
   -----(in this query we are referencing recursive_cte)----
)

SELECT *
FROM recursive_cte

Le CTE ricorsive funzionano su dati gerarchici, quindi i dati devono essere definiti correttamente. Il codice viene eseguito fino all'esaurimento di tutti i livelli gerarchici.

Ricordo quando scrissi la mia prima CTE ricorsiva. Dovevo estrarre informazioni di base sulla struttura organizzativa di una banca. Una CTE ricorsiva è appropriata in questo caso, perché la struttura organizzativa di una banca è gerarchica (cioè ha una struttura ad albero):

• Esiste un'unità principale a cui appartengono tutte le unità (la cosiddetta "ancora"). In una banca, questa è il consiglio di vigilanza.
• Ci sono unità organizzative per le quali è definita una chiara gerarchia. Ad esempio, il gruppo di analisi dei dati fa capo al dipartimento IT insieme ad altre unità, il dipartimento IT fa parte dell'unità di supporto al business e l'unità di supporto al business fa capo al consiglio di vigilanza, che è l'unità principale (ancora).

Poiché avevo bisogno del numero di dipendenti per ogni unità (ancora, genitore, figlio), la CTE ricorsiva era la soluzione giusta. Ricordo quanto fui felice quando il mio script funzionò: aveva una sola istruzione SQL! Avrei potuto scrivere una query separata per ogni reparto per il quale avevo bisogno di informazioni. Tuttavia, poiché ho utilizzato una CTE ricorsiva, ho ottenuto i risultati senza dover scrivere tante query.

In questa sede non ci addentreremo in modo approfondito nelle CTE ricorsive; per maggiori dettagli, consultate il nostro corso online interattivo su LearnSQL.it che tratta questo argomento. Se volete vedere le CTE in un esempio aziendale reale, vi suggerisco Creating Basic SQL Reports-Qui, l'aggregazione a più livelli, spesso richiesta nei report aziendali, viene dimostrata con l'uso delle CTE. Inoltre, ci sono diversi ottimi articoli su vari argomenti relativi alle CTE con esempi; potete trovarli qui e qui.

Leggibilità del codice e CTE

Per riassumere, ecco alcuni motivi per cui le CTE sono importanti e perché e quando si dovrebbero usare:

• Per evitare le subquery nelle istruzioni SQL. Questo si riferisce in particolare a situazioni in cui si uniscono diverse tabelle in una query principale e una o più di queste tabelle sono una sottoquery. In questo caso, una CTE è un'ottima alternativa che migliora la leggibilità del codice.
• Per evitare di creare inutilmente tabelle e viste nel database. Le CTE aiutano a evitare metadati non necessari. È comune creare tabelle da usare nelle join, in modo da non dover scrivere subquery. Tuttavia, piuttosto che generare una tabella "reale" i cui metadati sono memorizzati in un database, è possibile utilizzare una CTE come alternativa. Poiché si tratta di un insieme di risultati temporaneo con nome, non viene memorizzato in un database né utilizzato in seguito nel codice e il codice sarà leggibile come quello che crea tabelle reali.
• Per facilitare la comprensione e la manutenzione del codice. Le CTE sono particolarmente utili nelle query lunghe. Sono un modo molto efficace per mantenere le query più complicate. Ogni CTE ha un nome e i nomi intuitivi possono aiutare enormemente la leggibilità e la manutenzione del codice. È molto più facile capire cosa succede nel codice con nomi intuitivi per i pezzi di codice, perché dal nome si capisce subito di cosa si tratta.

Abbiamo illustrato la sintassi di base e diversi esempi di come e quando è possibile definire e utilizzare le CTE. Per capire meglio e fare pratica, consiglio un corso di LearnSQL.it denominato Recursive Queries. In questo corso si insegna in modo interattivo come utilizzare le CTE (ricorsive e non ricorsive) nel lavoro quotidiano. Potete trovare altre CTE in pratica in un corso interattivo chiamato Creazione di report SQL di base, in cui potete imparare a usare le CTE nelle aggregazioni a più livelli (come nella creazione di report aziendali). Una volta acquisita la padronanza delle CTE, sarete soddisfatti dell'aspetto migliore dei vostri script SQL!