6 esempi utili di CTE in SQL Server

Come si possono utilizzare le CTE in SQL Server nella vita professionale di tutti i giorni come professionisti dei dati? Risponderemo a questa domanda fornendovi sei esempi.

CTE è l'abbreviazione di Common Table Expression. È una funzione relativamente nuova di SQL Server, resa disponibile con SQL Server 2005.

Una CTE è un risultato temporaneo denominato. Questo risultato è disponibile solo per la query che lo esegue. Non viene memorizzato, quindi non occupa spazio su disco. Una CTE è in qualche modo simile a una tabella temporanea e può essere utilizzata come qualsiasi altra tabella. Le CTE sono usate più spesso con l'istruzione SELECT, ma possono essere usate anche con INSERT, UPDATE e DELETE.

Le CTE sono uno dei concetti più impegnativi di SQL Server. Per trarne vantaggio, l'approccio all'apprendimento deve essere strutturato con attenzione e non affrettato. Il nostro corso Recursive Queries in MS SQL Server vi mostrerà innanzitutto come scrivere una semplice CTE in SQL Server. Imparerete poi a scrivere più CTE, ad annidarle e a utilizzarle all'interno delle istruzioni SELECT, INSERT, UPDATE e DELETE. Infine, imparerete a conoscere le strutture di dati gerarchiche e a grafo e a utilizzare le CTE ricorsive in SQL Server per interrogare tali dati. Per garantire una sufficiente pratica, il corso prevede 112 esercizi interattivi da completare.

Naturalmente esistono anche altri modi per imparare le CTE, che potete consultare.

Sintassi CTE in SQL Server

In generale, la sintassi delle CTE in SQL Server è come quella dell'esempio seguente:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Le CTE devono sempre iniziare con la parola chiave WITH. Seguono il nome della CTE, la parola chiave AS e le parentesi. La CTE viene definita tra le parentesi. Definirla, come si vedrà nei nostri esempi, significa scrivere l'istruzione SELECT. Per maggiori dettagli, consultate questo articolo che spiega cos'è una CTE.

6 esempi di CTE in SQL Server

1: Trovare la media dei numeri più alti e più bassi dei flussi giornalieri

Nei primi cinque esempi utilizzeremo lo stesso set di dati. Si tratta di dati inventati provenienti da una piattaforma di streaming musicale immaginaria; chiamiamola Terpsichore.

Il dataset è composto da tre tabelle. La prima è artisted ecco la query di creazione della tabella. Questa tabella contiene le seguenti colonne:

• id - L'ID dell'artista e la chiave primaria della tabella.
• artist_name - Il nome dell'artista.

Questa tabella mostra tre artisti.

La tabella successiva è albums. Ecco la query per crearla. Ed ecco le colonne che contiene:

• id - L'ID dell'album e la chiave primaria della tabella.
• artist_id - L'artista (e la chiave esterna della tabella).
• album_title - Il titolo dell'album.
• year_released - L'anno di pubblicazione dell'album.

La tabella contiene dieci album.

L'ultima tabella è streams. Mostra i dati di streaming dei singoli brani. È possibile creare la tabella utilizzando questa query. E le colonne:

• id - L'ID dello streaming e la chiave primaria della tabella.
• artist_id - L'ID dell'artista e una chiave esterna.
• album_id - L'ID dell'album e una chiave esterna.
• song_title - Il nome del brano.
• date - La data dello streaming.
• number_of_streams - Il numero di volte in cui il brano è stato riprodotto in una determinata data.
• pay_per_stream - Valore (in dollari) che Terpsichore paga agli artisti per ogni streaming.

Questa tabella contiene 45 righe. Vi mostreremo solo le prime cinque, per farvi capire la logica della tabella.

Ora, l'esempio! Iniziamo scrivendo un solo CTE in SQL Server. Lo faremo per calcolare il numero medio più alto e più basso di flussi giornalieri.

Ecco il codice; lo spiegheremo di seguito:

WITH daily_streaming AS (
  SELECT date,
	   MIN(number_of_streams) AS minimum_streaming,
	   MAX(number_of_streams) AS maximum_streaming
  FROM streams
  GROUP BY date
)

SELECT AVG(minimum_streaming) AS average_minimum_daily_streaming,
	 AVG(maximum_streaming) AS average_maximum__daily_streaming
FROM daily_streaming;

Una CTE è geniale per scomporre la logica di qualsiasi calcolo in SQL Server.

Ricordate che in un giorno vengono trasmessi più brani in streaming. Lo scopo del nostro CTE è quello di ottenere il numero più basso e più alto di streaming ogni giorno.

Come già detto, in SQL Server una CTE inizia sempre con la parola chiave WITH, seguita dal nome della CTE. La nostra CTE si chiama daily_streaming.

Dopo la parola chiave AS viene la parentesi con un'istruzione SELECT, ovvero la definizione della CTE. La utilizziamo, insieme alle funzioni aggregate MIN() e MAX(), per calcolare il numero più alto e più basso di flussi per data.

La successiva istruzione SELECT utilizza i dati della CTE, facendo riferimento ad essa nella clausola FROM. Come abbiamo detto, una CTE può essere utilizzata come qualsiasi altra tabella. In questa SELECT, si utilizza la funzione aggregata AVG() per ottenere la media dei picchi e dei minimi giornalieri dei flussi.

L'output mostra che la media dei punti più bassi è di 90 flussi. La media dei massimi flussi giornalieri è di 8.367.

2: Calcolo della tariffa totale media pagata per brano

Mettiamo ora in pratica ciò che abbiamo imparato. Scriveremo di nuovo un singolo CTE.

Il problema da risolvere è trovare la tariffa totale media pagata da Terpsichore per ogni canzone.

Ecco la soluzione:

WITH paid_per_song AS (
  SELECT song_title,
	   SUM(number_of_streams * pay_per_stream) AS total_pay
  FROM streams
  GROUP BY id, song_title, pay_per_stream
)

SELECT song_title,
       AVG(total_pay) AS average_total_pay
FROM paid_per_song
GROUP BY song_title
ORDER BY average_total_pay DESC;

Utilizziamo la CTE di SQL Server per calcolare il compenso totale pagato per ogni canzone moltiplicando il numero di stream con il compenso per stream e poi sommando il tutto utilizzando la funzione aggregata SUM().

Non ci sono cambiamenti per quanto riguarda la sintassi CTE: prima viene WITH, poi il nome della CTE e dopo viene AS.

Utilizziamo quindi una SELECT che richiama la CTE per calcolare la retribuzione media per brano. È semplice: si usa AVG(), si fa riferimento alla CTE in FROM e si raggruppa per il titolo del brano.

La query restituisce il seguente risultato:

Vediamo che la canzone "Uptown" ha guadagnato 47,4803330 dollari in totale. La seconda e la terza canzone per guadagno sono 'I Wanna Be Your Lover' e 'Little Red Corvette'. Se siete fan di questo artista, non avete bisogno di SQL per scoprire chi ha scritto queste tre canzoni.

3: Trovare l'album più ascoltato di ogni artista

In questo esercizio si deve trovare l'album più ascoltato di ogni artista. Si deve fornire il nome dell'artista, il titolo dell'album e il numero di streaming per album.

Anche in questo caso, si tratta di una query con una sola CTE. Tuttavia, è un po' più complessa delle due precedenti: ci sono alcune JOIN e una funzione finestra.

WITH album_streaming AS (
  SELECT artist_id,
	   album_id,
	   SUM(number_of_streams) AS streams_by_album,
	   RANK() OVER (PARTITION BY artist_id ORDER BY SUM(number_of_streams) DESC) AS streaming_rank
  FROM streams
  GROUP BY artist_id, album_id
)

SELECT artist_name,
       album_title,
	 streams_by_album
FROM album_streaming alst
JOIN albums al
ON alst.album_id = al.id
JOIN artist ar 
ON al.artist_id = ar.id
WHERE streaming_rank = 1;

Anche in questo caso, la sintassi CTE di SQL Server è familiare. Concentriamoci quindi sulle funzioni di questa CTE. La utilizziamo per classificare gli album in base all'artista. Innanzitutto, selezioniamo gli ID dell'artista e dell'album. Poi utilizziamo SUM() per calcolare il numero di stream per album.

Ora viene la parte cruciale: classificare i risultati utilizzando la funzione RANK() window. Dopo aver invocato la funzione, vediamo la clausola OVER(), una clausola obbligatoria per le funzioni SQL a finestra. Il set di dati viene suddiviso in base all'ID dell'artista e i dati all'interno di ciascuna partizione vengono ordinati in base al numero di stream, in ordine decrescente.

Che cosa significa in pratica? Significa che la funzione finestra classificherà gli album per un artista, poi la classifica ripartirà quando la funzione raggiungerà l'artista successivo e così via. L'album dell'artista con il maggior numero di stream verrà classificato al primo posto nella sua partizione.

Se si esegue solo questa istruzione SELECT all'interno della CTE, si otterrà questo risultato:

Come si può vedere, gli album del primo artista sono classificati dal primo al terzo, in base al numero di stream. Quando si raggiunge il secondo artista, la classifica riparte. Lo stesso vale per il terzo artista.

Vediamo ora cosa fa la seconda istruzione SELECT. In realtà, non è nulla di complicato. Restituisce il nome dell'artista e dell'album e il numero di streaming. Ciò che complica questa query è che dobbiamo unire tre tabelle.

La prima unione è la CTE album_streaming. Poi la uniamo con albums e poi con la tabella artist e poi con la tabella Alla fine, filtriamo i dati utilizzando la clausola WHERE perché ci interessa solo l'album più ascoltato in streaming.

Il risultato è questo:

L'album più ascoltato di Prince è "Dirty Mind" con 13.820 streaming. Per Jimi Hendrix, l'album più ascoltato è "Are You Experienced" e per Santana è "Abraxas".

Questa soluzione utilizza le funzioni finestra, quindi vi ricordiamo come funzionano quando si classificano i dati.

4: Calcolare la media dei flussi per canzone e confrontarla con la media dei flussi per data

Ora le cose si fanno più complicate. Ma non troppo, non preoccupatevi. Ci basiamo su quanto abbiamo imparato finora sulle CTE in SQL Server.

In questo caso, dobbiamo trovare il numero medio di streaming per canzone. Poi dobbiamo calcolare il numero medio di streaming per data.

L'output deve mostrare entrambe le metriche. Inoltre, deve mostrare la differenza tra lo stream medio per brano e la media giornaliera (come differenza percentuale), il titolo del brano e le date.

Finora abbiamo scritto query con una CTE. Questa volta, la soluzione consiste in due CTE. Vediamo come funziona:

WITH streams_per_song AS (
  SELECT song_title,
	   AVG(number_of_streams) AS average_streams_per_song
  FROM streams
  GROUP BY song_title
),

streams_per_date AS (
  SELECT date,
	   AVG(number_of_streams) AS average_streams_per_date
  FROM streams
  GROUP BY date
)

SELECT song_title,
	 average_streams_per_song,
	 date,
	 average_streams_per_date,
	 (average_streams_per_song - average_streams_per_date)/CAST(average_streams_per_date AS DECIMAL(10,2))*100 AS diff_from_daily_average
FROM streams_per_song, streams_per_date;

La prima CTE è scritta come di consueto. La usiamo per calcolare il numero medio di stream per brano con AVG().

Dopo aver chiuso le parentesi, la prima CTE deve essere separata dalla seconda CTE con una virgola.

Poi scriviamo la seconda CTE. Ecco! Non c'è WITH! Proprio così. Quando si scrivono più CTE in una query in SQL Server, si scrive WITH solo davanti alla prima CTE. La seconda (e ogni altra CTE successiva) inizia con il nome della CTE; tutto il resto è uguale.

Questa seconda query serve a calcolare il numero medio di flussi per data. Anche in questo caso si utilizza la funzione AVG().

La terza SELECT utilizza i dati di entrambe le CTE. Restituisce tutte le colonne richieste. L'ultima colonna è diff_from_daily_average. La calcoliamo sottraendo gli stream medi per data dagli stream medi per brano. La differenza viene divisa per la media degli stream per data e moltiplicata per 100 per ottenere la percentuale. Inoltre, abbiamo convertito il risultato in un tipo di dati decimale utilizzando la funzione CAST().

A causa delle dimensioni dell'output, mostreremo solo le prime righe:

I dati mostrano che la media dei flussi giornalieri per il 1° gennaio 2023 è di 1.031. "All Along the Watchtower" è inferiore a tale media del 21,14%. Le due canzoni successive sono inferiori alla media giornaliera del 28,13% e del 43,45%, e così via.

5: Calcolo del compenso medio dell'album più alto e più basso per artista

Spieghiamo cosa intendiamo. Vogliamo innanzitutto trovare la retribuzione media per album e data. Poi dobbiamo trovare il valore di retribuzione più basso e più alto per album. Successivamente, vogliamo aggregare i dati per artista. Insieme al suo nome, dobbiamo mostrare il valore della retribuzione più bassa ottenuta dall'artista per un album. Dobbiamo fare lo stesso con la retribuzione più alta per un album.

La soluzione in SQL Server contiene due CTE. Tuttavia, questa volta si tratta di una CTE annidata. Il secondo CTE fa riferimento al primo CTE. Vediamo come funziona:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),
	
min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Fate attenzione quando leggete la spiegazione del codice! È facile perdersi in tutte queste aggregazioni. Per rendere le cose più facili da seguire, ho copiato ogni parte della query e l'ho seguita con una spiegazione. Inizieremo con la prima CTE:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),

La prima CTE calcola la retribuzione media per album e la data. Questo viene fatto moltiplicando il numero di stream per la retribuzione per stream e utilizzando AVG().

min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

Quando si scrive il secondo CTE in SQL Server, la sintassi è identica a quella dell'esempio precedente: non ci sono WITH aggiuntivi, si inizia con il nome del CTE e si separano i CTE con una virgola. L'unica differenza è che questa volta la seconda CTE fa riferimento alla prima CTE e non al set di dati originale.

Questa CTE nidificata utilizza le funzioni MIN() e MAX() per trovare la retribuzione media più bassa e più alta di ciascun album per tutte le date. Il primo CTE è referenziato nella funzione FROM.

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Infine, c'è SELECT che unisce la seconda CTE con le funzioni albums e artist e le tabelle. Applichiamo nuovamente le funzioni MIN() e MAX() al risultato della seconda CTE. Questo per restituire solo i valori del prezzo più basso e di quello più alto di tutti gli album di ciascun artista.

Ecco cosa otteniamo dopo aver eseguito la query:

6: Trovare il percorso più lungo tra Rotterdam e Amsterdam

Si tratta di una variante del problema del percorso più breve nella teoria dei grafi, ma cercheremo il percorso più lungo.

Un grafo è un tipo di struttura di dati che consiste in nodi o punti collegati da bordi. Poiché sono collegati, è possibile trovare un percorso da un nodo all'altro anche se non sono direttamente collegati.

Pensate a una mappa delle strade. Questo è esattamente l'esempio che utilizzeremo qui. Di seguito è riportata la tabella cities_distanceche mostra le città e la distanza tra di esse. Per creare la tabella, utilizzare questa query che contiene le seguenti colonne:

• città_da - La città di origine.
• città_a - La città di arrivo.
• distanza - La distanza tra le due città, in chilometri.

Ecco i dati:

Dobbiamo trovare il percorso più lungo da Rotterdam ad Amsterdam. Il percorso deve includere il nome di tutte le città lungo il percorso, separate da "/". Inoltre, dobbiamo indicare la lunghezza del percorso più lungo.

Quando diciamo "il percorso più lungo", vogliamo escludere i percorsi circolari (dove si possono fare giri infiniti e aumentare la distanza). Vogliamo che il percorso più lungo attraversi una città in particolare solo una volta.

Per risolvere questo problema, utilizzeremo una CTE ricorsiva. Si tratta di una query che fa riferimento a se stessa finché non raggiunge la fine dei dati. Questa caratteristica è ideale per interrogare i dati di un grafo, dove più percorsi possono portare alla stessa meta.

Vediamo come funziona questa CTE ricorsiva:

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
        cd.distance AS distance
   FROM cities_distance cd
   WHERE cd.city_from = 'Rotterdam'

UNION ALL

  SELECT cd.city_to,
         CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
  FROM longest_path lp 
  INNER JOIN cities_distance cd
  ON cd.city_from = lp.city_to
  WHERE lp.city_to <> 'Amsterdam'
  AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

La sintassi è la stessa di prima: anche la query ricorsiva inizia con WITH in SQL Server.

Come al solito, tra le parentesi c'è un'istruzione SELECT. Per essere più precisi, ce ne sono due. Vediamo cosa fa la prima.

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         cd.distance AS distance
  FROM cities_distance cd
  WHERE cd.city_from = 'Rotterdam'

Il primo SELECT nella ricorsione è chiamato membro di ancoraggio. Viene utilizzato per selezionare il punto di partenza della ricorsione. Il punto di partenza sarà Rotterdam, che si ottiene filtrando questa città in WHERE. La colonna city_to viene utilizzata per mostrare tutte le destinazioni finali che possono essere raggiunte direttamente da Rotterdam. La colonna path elenca tutte le città di origine e di destinazione. La lunghezza del percorso è indicata nella colonna distance.

Segue UNION ALL, che collegherà i risultati dell'ancora e del membro ricorsivo, cioè il secondo SELECT. L'unione di queste due query è necessaria perché la ricorsione funzioni.

Nota: in alcuni altri dialetti di SQL è possibile utilizzare anche UNION. Tuttavia, SQL Server consente solo UNION ALL.

Passiamo ora al membro ricorsivo. Esso fa riferimento alla CTE stessa in FROM e la unisce alla tabella cities_distance. Affinché le query siano unificate, entrambe devono avere lo stesso numero di colonne dello stesso tipo di dati. Le prime due colonne sono le stesse del membro di ancoraggio. La colonna longest_path somma tutte le distanze per raggiungere tutte le città da Rotterdam.

SELECT cd.city_to,
       CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
       CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
FROM longest_path lp 
INNER JOIN cities_distance cd
ON cd.city_from = lp.city_to
WHERE lp.city_to <> 'Amsterdam'
AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

Abbiamo anche aggiunto due condizioni in WHERE. La prima esclude tutte le relazioni in cui Amsterdam è la destinazione finale; stiamo cercando il percorso più lungo, non il più breve, per raggiungere Amsterdam. La seconda condizione assicura che ogni nuova città aggiunta al percorso non sia già inclusa nel percorso. In caso contrario, la query entrerà in una ricorsione infinita. Questo risponde a quanto detto sopra: il percorso più lungo non deve visitare la stessa città più di una volta.

Per capire meglio di cosa stiamo parlando, ecco l'output della CTE ricorsiva:

Si può notare che esistono tre percorsi da Rotterdam ad Amsterdam. Uno è diretto, con una distanza di 78,20 km. Il secondo e il terzo passano attraverso altre città e richiedono rispettivamente 165,90 e 120,50 km.

Attenzione, questo non è il risultato finale! La nostra soluzione ha anche un'istruzione SELECT che fa riferimento alla CTE:

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

Questa SELECT restituisce il percorso e la distanza. Utilizziamo il comando TOP 1 combinato con ORDER BY per ottenere il percorso più lungo da Rotterdam ad Amsterdam. Abbiamo ordinato i dati dalla distanza più alta a quella più breve, quindi la prima riga sarà anche il percorso più lungo.

Ecco il risultato finale:

Questo risultato mostra che il percorso più lungo da Rotterdam ad Amsterdam è di 165,9 km e passa per Leida, Gouda e Utrecht.

Se non ne avete mai abbastanza di questo argomento, ecco altri esempi di CTE.

Quando usare le CTE?

Come si è visto negli esempi, le CTE hanno molti scopi in SQL Server.

Uno è quello di migliorare la leggibilità del codice. Tutte le soluzioni precedenti (eccetto la ricorsione) avrebbero potuto essere scritte con delle subquery. Ma questo renderebbe il codice molto più lungo e meno chiaro.

A pensarci bene, le subquery sono di solito scritte in modo contrario alla logica del problema che si sta cercando di risolvere. Prima c'è la query principale, che utilizza l'output della subquery. Quindi la subquery è di solito il primo passo del calcolo, anche se non è posizionata in questo modo nel codice.

Le CTE, invece, possono essere scritte per seguire la logica del problema. È possibile scrivere diverse CTE separate e unire i loro risultati nelle istruzioni di SELECT. Si può anche fare riferimento all'output di una CTE con la seconda query (o la terza, la quarta...), e l'istruzione finale SELECT è un altro livello di calcolo.

Uno degli esempi mostrava anche la possibilità di classificare i dati in SQL Server utilizzando una funzione finestra e una CTE.

Se si desidera scrivere query ricorsive in SQL Server, non è possibile farlo senza CTE. Una CTE può essere non ricorsiva, ma non esistono query ricorsive senza CTE. Oltre ai grafici, la ricorsione è estremamente utile per interrogare strutture gerarchiche, come l'organizzazione dei dati e gli alberi genealogici.

Per saperne di più su quando usare una CTE, vedere qui.

Le CTE sono la porta per l'uso di Advanced SQL!

Le CTE sono uno dei concetti più avanzati di SQL Server. Se si vuole aprire la porta alle competenze di livello avanzato di SQL Server, le CTE sono un must.

Quando le query in SQL Server diventeranno più complesse, vi renderete presto conto che imparare le CTE è stata una delle decisioni migliori che abbiate mai preso. Sono anche un trampolino di lancio per le query ricorsive, che consentono di interrogare tipi insoliti di strutture di dati in SQL Server, come gerarchie e grafici.

Questo articolo è solo un'anteprima delle conoscenze che potrete trovare nel corso Recursive Queries in MS SQL Server. Quindi non fermatevi qui. C'è molto altro da imparare!