Immagine con codice SQL contenente una CTE

CTE: come e quando utilizzarle nel tuo codice SQL

Le CTE (Common Table Expression) sono uno strumento indispensabile per programmare correttamente in SQL. In questo articolo vedremo come utilizzare le CTE per risolvere query SQL complesse, dividendo il problema di partenza in tanti task più semplici.

Tramite le istruzioni di base dell’SQL è possibile interrogare un database relazionale per risolvere un’elevata percentuale delle richieste di estrazioni dati che arrivano quotidianamente ad un data analyst o ad uno sviluppatore SQL. Con il termine “istruzioni di base” intendo le parole chiavi Select, From, Join, Where, Group by, Having e le principali funzioni di SQL (a questo link trovi un approfondimento sulle più importanti funzioni SQL da conoscere).

Tuttavia in alcuni casi le richieste di analisi possono presentarsi in una forma molto complessa e difficile da tradurre in una singola query. Generalmente, un neofita come lo ero io quando iniziai a lavorare con l’SQL si approccia a questi task cercando di scrivere un’unica grande query iper-complicata, che risolva in un solo colpo il problema. La mia esperienza condivisa con molti altri sviluppatori SQL mi porta a dire che questo tipo di approccio è sbagliato in quanto porterà alla creazione di codice che, se pur funzionante, risulterà quasi sempre molto complicato e poco manutenibile, tradendo la natura English-like dell’SQL.

 

Un esempio di utilizzo delle CTE

Faccio un esempio pratico per spiegare meglio l’utilizzo delle CTE. Supponiamo di avere la tabella Fatture contenente le colonne IdFattura, DataFattura e Importo e ci viene chiesto di calcolare l’importo annuo medio, vale a dire la media degli importi calcolati per ogni anno. Molti sviluppatori si imbatteranno in un vicolo cieco cercando di scrivere un’unica query che risolva direttamente il problema. Molti tentativi daranno errori di sintassi perché si cercherò di annidare le due funzioni di aggregazione AVG, SUM. Ancor peggio se la query compila, ma fornisce delle informazioni errate perché manca, ad esempio, la clausola GROUP BY.

Un approccio migliore che semplifica la vita dello sviluppatore consiste nel suddividere preliminarmente il problema in due sotto problemi più semplici:

  • calcolare il totale dell’importo per anno
  • calcolare la media dei fatturati precedenti.

I due task precedenti sono risolvibili con due semplicissime query SQL che utilizzano le sole funzionalità di base: per la prima basterà una group by sull’anno e una funzione di aggregazione, mentre per la seconda basterà calcolare la media di una colonna. Il problema che si pone però è il seguente: come faccio a mettere insieme le due query per estrarre il risultato finale? Per rispondere a questa domanda l’SQL ci mette a disposizione più strumenti differenti come le CTE, le subquery, le tabelle temporanee e le viste. In questo articolo ci concentreremo sullo strumento a mio parere più flessibile: le CTE.

 

CTE: Common Table Expression in SQL

Partiamo scrivendo la query per risolvere il primo sotto-problema: calcolare il totale dell’importo per anno. Supponendo di lavorare in ambiente Microsoft Sql Server, ci basterà scrivere una query come

SELECT YEAR(DataFattura) as Anno,
SUM(Importo) as ImportoTotaleAnnuo
FROM Fatture
GROUP BY YEAR(DataFattura);

Sugli altri RDBMS la situazione è molto simile. Osserviamo nella prossima tabella un possibile output della query precedente.

Anno ImportoTotaleAnnuo
2016 105.27
2017 261.10
2018 215.07
2019 210.44

L’output di una query si presenta ai nostri occhi come una comune tabella: ci sono delle colonne con un’intestazione e una serie di righe contenenti le informazioni. Immaginiamo un attimo di avere realmente questa tabella salvata fisicamente nel nostro DB, non solo sullo schermo, e che questa tabella si chiami ad esempio ImportiFattureAnnui. Quanto sarebbe difficile rispondere ora alla seconda domanda “calcolare la media dei fatturati precedenti“? Si tratterebbe di un gioco da ragazzi, basterebbe scrivere

SELECT AVG(ImportoTotaleAnnuo) as ImportoAnnuoMedio
FROM    ImportiFattureAnnui;

Ora è facile capire che non è pensabile creare una vera tabella ed occupare spazio sul disco per ogni sotto-task di ogni richiesta che arriva ad un team di Data Analyst. Ma per questo ci vengono in soccorso le CTE: con un po’ di approssimazione possiamo dire che l’effetto è quello di creare la tabella in memoria solo per il tempo necessario a svolgere l’intera l’analisi. La sintassi è molto semplice:

WITH ImportiFattureAnnui AS (
    SELECT YEAR(DataFattura) as Anno,
    SUM(Importo) as ImportoTotaleAnnuo
    FROM Fatture
    GROUP BY YEAR(DataFattura)
    )
SELECT AVG(ImportoTotaleAnnuo) as ImportoAnnuoMedio
FROM   ImportiFattureAnnui;

Se dopo aver eseguito la query provo a rilanciare le ultime due righe di codice

SELECT AVG(ImportoTotaleAnnuo) as ImportoAnnuoMedio
FROM   ImportiFattureAnnui;

otterrò l’errore “ImportiFattureAnnui non esiste”. Utilizzando la CTE non creiamo nessuna tabella, stiamo solo definendo un’espressione SQL il cui output può essere utilizzato al pari di una tabella all’interno dello stessa esecuzione.

Utilizzare più CTE in una sola query

Non c’è limite al numero di CTE utilizzabili in una query. Potremmo ad esempio scrivere una query che contiene due CTE e poi le utilizzi per eseguire una JOIN. Basta ricordare la sintassi che prevede di scrivere WITH una sola volta e separare le CTE con una virgola:

WITH
CTE1 AS
    (<Query1>),
CTE2 AS
   (<Query2>)
SELECT *
FROM CTE1
JOIN CTE2
ON …..

È importante sottolineare che la query della seconda CTE può contenere, oltre a qualsiasi altra tabella, anche tutte le CTE definite in precedenza nella query! Un utilizzo più avanzato consiste infine nell’utilizzare le CTE ricorsivamente, come descritto in questo articolo.

In questo modo è possibile scrivere del codice modulare, comprensibile e manutenibile, semplificando anche il processo di creazione e ideazione del codice. Solo in un secondo momento, analizzando il piano di esecuzione della query, potremmo valutare di riscrivere la query in modo diverso per migliorarne eventualmente le performance.

 

Articoli correlati

In questa pagina trovi un paragrafo di esercizi di SQL dedicati all’utilizzo delle CTE.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Torna su