Che cos'è una CTE in SQL Server?

Che cos'è una CTE e come si scrive una CTE in SQL Server? Unitevi a noi in un viaggio in cui vedremo tutti gli usi tipici di una CTE in SQL Server.

Le CTE (o Common Table Expressions) sono una funzione di SQL utilizzata per definire un risultato temporaneo con nome. Si può pensare a una tabella temporanea il cui risultato è disponibile solo quando viene eseguita la query principale. Questo è pratico perché il risultato delle CTE non viene memorizzato da nessuna parte, ma può sempre essere consultato all'interno della query come qualsiasi altra tabella. Le CTE sono più comunemente usate nell'istruzione SELECT, ma possono essere usate anche nelle istruzioni INSERT, UPDATE e DELETE.

Le CTE sono una funzione di SQL relativamente nuova. Sono state introdotte nello standard SQL: 1999 (SQL 3). Nel 2005 sono state rese disponibili in SQL Server 2005.

È possibile fare esperienza pratica con le CTE in SQL Server nel nostro corso interattivo Recursive Queries in MS SQL Server. Imparerete la sintassi delle CTE in SQL Server, come utilizzare più di una CTE, come annidarle e come farle funzionare in SELECT, INSERT, UPDATE e DELETE. C'è anche una sezione che spiega la ricorsione e come scrivere un CTE ricorsivo.

Tratteremo tutti questi argomenti in questo articolo. Tuttavia, non possiamo competere con i 112 esercizi interattivi offerti dal corso. Oltre al corso e a questo articolo, esistono anche altri modi per imparare i CTE.

Sintassi CTE di SQL Server

La sintassi di base di SQL Server CTE è la seguente:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Un CTE in SQL Server viene avviato con la parola chiave WITH. Segue il nome del CTE (in questo caso, cte), la parola chiave AS e le parentesi. Le parentesi contengono la definizione della CTE. In parole povere, si tratta di una normale istruzione SELECT, ma sotto forma di CTE.

La parte successiva viene dopo le parentesi e si chiama query principale. La CTE non funziona senza di essa. Questa query principale è, nell'esempio generico, un'istruzione SELECT che fa riferimento alla CTE nella clausola FROM. Come abbiamo già detto, la query principale potrebbe essere un'istruzione INSERT, UPDATE, o DELETE invece di SELECT.

Un altro modo di scrivere una CTE in SQL Server è quello di specificare esplicitamente le colonne:

WITH cte (cte_columns) AS (
  SELECT
    ...	
)

SELECT
  ...
FROM cte;

L'unica differenza è che le colonne CTE vengono definite esplicitamente prima della parola chiave AS. Questo è utile quando le colonne CTE richiedono degli alias (ad esempio quando contengono funzioni); la query è più leggibile con gli alias assegnati in questo modo.

Come si vedrà con gli esempi di questo articolo, l'argomento principale per l'uso di CTE in SQL Server è la migliore leggibilità del codice. Potete anche dare un'occhiata ad altri vantaggi.

Ora che conoscete la sintassi di base delle CTE, utilizzate ciascun approccio in un esempio. Man mano che andremo avanti, mostreremo le piccole modifiche alla sintassi in base all'utilizzo della query.

Esempi di CTE in SQL Server

Prima di scrivere qualsiasi codice, familiarizziamo con il set di dati. La tabella è flight_databaseche contiene i dati storici dei voli. Ha le seguenti colonne:

• id - L'ID del record e la chiave primaria (PK) della tabella.
• flight_id - Il numero di volo secondo gli standard IATA.
• airline - Il nome della compagnia aerea.
• flight_date - La data del volo.
• departure_airport - L'aeroporto da cui il volo è decollato.
• arrival_airport - L'aeroporto in cui il volo è atterrato.
• planned_departure - L'ora in cui era prevista la partenza del volo.
• actual_departure - L'ora della partenza effettiva del volo.
• planned_arrival - L'ora in cui il volo doveva arrivare.
• actual_arrival - L'ora dell'arrivo effettivo del volo.
• airport_distance - La distanza tra gli aeroporti di partenza e di arrivo, in chilometri.

Questi sono dati fittizi per l'aeroporto di Amsterdam Schiphol. Tutti gli orari sono GMT+1, il che rende più facile il confronto tra gli orari di partenza e di arrivo.

Ecco alcune righe di questa tabella:

La colonna id è unica, poiché è la chiave primaria della tabella. La colonna flight_id non è unica, poiché ci sono dati per gli stessi voli in date diverse.

È possibile creare questo set di dati utilizzando il codice riportato nel link. Se è necessario installare SQL Server, ecco le istruzioni per farlo.

Esempio 1: CTE standard in SQL Server

Si tratta di scrivere una CTE per trovare i ritardi più lunghi alla partenza e all'arrivo in base al numero di volo IATA.

Ecco la query:

WITH delay_times AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure) AS departure_delay,
    DATEDIFF(minute, planned_arrival, actual_arrival) AS arrival_delay
  FROM flight_database
)

SELECT 
  flight_id,
  MAX(departure_delay) AS max_departure_delay,
  MAX(arrival_delay) AS max_arrival_delay
FROM delay_times
GROUP BY flight_id;

La CTE inizia scrivendo la parola chiave WITH. Il nome della CTE è delay_times. Dopo la parola chiave AS e la parentesi di apertura, c'è una definizione della CTE sotto forma di istruzione SELECT. Essa calcola la differenza tra la partenza programmata e quella effettiva utilizzando la funzione DATEDIFF(). Lo stesso approccio viene applicato per calcolare la differenza tra l'arrivo previsto e quello effettivo. Entrambi i risultati sono espressi in minuti. Poiché queste colonne utilizzano delle funzioni, ognuna di esse ha un alias.

Dopo aver chiuso le parentesi, è il momento di scrivere la query principale. Si tratta di un'istruzione SELECT che fa riferimento alla CTE delay_times e utilizza due volte le funzioni aggregate di SQL Server MAX() per calcolare il ritardo più lungo alla partenza e all'arrivo per volo.

L'output si legge nel modo seguente. Il ritardo massimo del volo DL 47 alla partenza è stato di 5 minuti. Il ritardo massimo all'arrivo è stato pari a 0; il volo è sempre arrivato in orario.

Esempio 2: CTE con colonne definite esplicitamente

L'esempio seguente è molto simile. L'unica differenza è che vogliamo trovare i ritardi minimi alla partenza e all'arrivo per ogni volo. Inoltre, utilizzeremo colonne definite esplicitamente. Procedere in questo modo:

WITH delay_times (flight_id, flight_date, departure_delay, arrival_delay) AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure),
    DATEDIFF(minute, planned_arrival, actual_arrival)
  FROM flight_database
)

SELECT 
  flight_id,
  MIN(departure_delay) AS min_departure_delay,
  MIN(arrival_delay) AS min_arrival_delay
FROM delay_times
GROUP BY flight_id;

Questa CTE è ancora una volta denominata delay_times. Per definire esplicitamente le colonne della CTE, scriverle tra le parentesi prima della parola chiave AS.

La CTE in sé non è molto diversa dalla precedente: utilizza nuovamente la funzione DATEDIFF() per calcolare le differenze di ritardo. L'unico cambiamento è che gli alias di queste due (e delle altre due) colonne sono definiti prima, con il nome della CTE.

La query principale è quasi identica a quella precedente. La differenza è che ora utilizza la funzione MIN(), poiché l'obiettivo è calcolare i ritardi più piccoli.

Il risultato mostra che il ritardo più breve del volo DL 47 è stato pari a zero. In altre parole, è stato puntuale almeno una volta. Il volo LH 2301 non è mai stato in orario. Ha subito un ritardo di almeno 20 minuti sia alla partenza che all'arrivo.

Esempio 3: una CTE annidata in SQL Server

In SQL Server, una CTE annidata si ha quando ci sono almeno due CTE e la seconda CTE fa riferimento alla prima. Ne avremo bisogno nell'esempio seguente. Si tratta di calcolare la durata media di un volo in minuti e la velocità media di volo in km/h.

Ecco il codice:

WITH flight_duration AS (
  SELECT 
    flight_id,
    DATEDIFF(MINUTE, actual_departure, actual_arrival) AS minutes_of_flight,
    airport_distance
  FROM flight_database
),

average_flight_duration AS (
  SELECT 
    flight_id,
    AVG(minutes_of_flight) AS average_flight_duration,
    airport_distance
  FROM flight_duration
  GROUP BY flight_id, airport_distance
)

SELECT 
  flight_id,
  average_flight_duration,
  airport_distance/(CAST(average_flight_duration AS DECIMAL(10,2))/60) AS average_flight_speed
FROM average_flight_duration;

La prima CTE è scritta come di consueto in SQL Server: WITH, il nome della CTE, AS, e l'istruzione SELECT. Questa query calcola la durata del volo in minuti. Dopo aver chiuso le parentesi, si scrive la seconda CTE. Ma attenzione: deve esserci una virgola a separare le due CTE. Inoltre, quando si inizia a scrivere la seconda CTE, non c'è la parola chiave WITH: si inizia direttamente con il nome della CTE. Tutto il resto è come al solito. Questa seconda CTE fa riferimento alla prima CTE nella clausola FROM per calcolare la durata media dei voli per tutte le date.

Non ci sono differenze nella sintassi della query principale. Questa query fa riferimento alla seconda CTE. Calcola la velocità media del volo dividendo la distanza tra gli aeroporti per la durata media del volo. Il risultato viene convertito in un numero decimale. Viene anche diviso per 60, quindi la velocità media viene mostrata in chilometri all'ora.

L'output mostra che, ad esempio, il tempo medio per raggiungere la destinazione del volo LH 987 è di 54 minuti, con una velocità media di 407,14 km/h.

Se volete fare più pratica, ecco un altro esempio di CTE annidato.

Esempio 4: Una CTE in un'istruzione UPDATE

In questo esempio mostreremo come funzionano le CTE nell'istruzione UPDATE. Per come è mostrata qui sotto, si potrebbe usare anche l'istruzione INSERT.

Il compito è quello di aggiornare la riga flight_database. Per essere più precisi, aggiorneremo la colonna airport_distance. Attualmente contiene dati in chilometri, ma deve essere cambiata in miglia.

Ecco come fare:

WITH distance_in_miles AS (
  SELECT 
    flight_id,
    airport_distance * 0.621371 AS airport_distance_miles
  FROM flight_database
)

UPDATE flight_database
SET airport_distance = airport_distance_miles
FROM distance_in_miles dim 
JOIN flight_database fd ON dim.flight_id = fd.flight_id;

Come sempre, si inizia con la parola chiave WITH. Il CTE distance_in_miles viene utilizzato per convertire i chilometri in miglia. È semplice: moltiplicare i valori per 0,621371.

La query principale è ora UPDATE invece di SELECT. Niente di difficile, basta seguire la sintassi delle istruzioni. Aggiornare la colonna airport_distance con i valori della colonna airport_distance_miles che appare nella CTE. Unite la tabella e la CTE e il gioco è fatto: la tabella è aggiornata.

Ecco alcuni valori prima dell'aggiornamento:

Ed ecco le stesse righe con i valori aggiornati della distanza:

Esempio 5: Una CTE ricorsiva in SQL Server

L'ultimo esempio riguarda la scrittura di una CTE ricorsiva in SQL Server. Si tratta di una CTE che fa riferimento a se stessa. Si usa spesso quando si interrogano dati gerarchici (come l'organizzazione aziendale) o grafici in cui alcune o tutte le parti sono correlate (si pensi a una mappa stradale con le distanze tra le città). L'esempio che vi mostreremo è un po' più semplice. L'importante è che abbiate compreso la ricorsione e come tradurla in una CTE.

Diciamo che l'aeroporto ha un certo numero di slot di volo. C'è anche un prezzo per slot. Ogni anno, l'aeroporto aumenterà il numero di slot di 150 unità; il prezzo degli slot rimarrà invariato. Vogliamo mostrare il numero di slot, il prezzo per slot e il ricavo totale degli slot per l'anno in corso e per i prossimi quattro anni.

Ecco il CTE ricorsivo per farlo:

WITH airport_slots AS (
  SELECT 
    1 AS id,
    400000 AS number_of_slots,
    20574421.00 AS price_per_slot,
    CAST(20574421.00 * 400000 AS DECIMAL) AS slot_revenue
		   
  UNION ALL

  SELECT 
    id + 1,
    number_of_slots + 150,
    price_per_slot,
    CAST(price_per_slot * (number_of_slots + 150) AS DECIMAL)
  FROM airport_slots
  WHERE id <= 4
)

SELECT *
FROM airport_slots;

Ancora una volta, la query ricorsiva inizia con la parola chiave WITH. Il primo SELECT nel CTE è chiamato membro di ancoraggio. Si impostano i valori iniziali dell'ID, del numero di slot e del prezzo per slot. Inoltre, moltiplichiamo questi due valori per ottenere i ricavi degli slot.

Segue UNION ALL, che collega il membro di ancoraggio con il secondo SELECT (chiamato membro ricorsivo). UNION ALL stabilisce che il numero di colonne e i loro tipi di dati devono essere gli stessi in entrambe le istruzioni SELECT del CTE.

Il membro ricorsivo fa riferimento alla CTE stessa. A ogni ricorsione, l'ID viene incrementato di uno e il numero di slot di 150. Il prezzo per slot rimane invariato. Il prezzo per slot rimane invariato. Il ricavo degli slot è dato dall'aumento del numero di slot moltiplicato per il prezzo per slot.

Abbiamo anche usato la clausola WHERE per interrompere la ricorsione quando l'ID è uguale a quattro. La ricorsione lo trasformerà in cinque, e una proiezione di cinque anni è ciò che vogliamo (corrente + prossimi quattro anni).

Dopodiché, la procedura è la stessa di qualsiasi CTE. La query principale è SELECT.

E questo è l'output:

Abbiamo altri esempi di CTE ricorsive sul nostro blog. In questo articolo ci siamo attenuti alla sintassi di SQL Server, ma è possibile imparare a eseguire la ricorsione anche in PostgreSQL e Oracle.

Le CTE di SQL Server rendono migliori!

Letteralmente, lo fanno. La maggior parte delle cose che le CTE fanno in SQL Server possono essere fatte con una subquery. Ma immaginate come sarebbero i codici di cui sopra: non è bello! Uno degli usi tipici delle CTE in SQL Server è quello di aiutare a organizzare le query più lunghe. Le CTE rendono le query più leggibili dando un nome alle parti della query. In questo modo, è possibile suddividere facilmente ogni parte di un calcolo complesso e rendere il calcolo logico.

Sapere questo migliorerà il vostro codice T-SQL. C'è anche una funzione SQL che non è possibile senza le CTE: le query ricorsive. Sono indispensabili quando si interrogano dati gerarchici e grafici. Potete imparare tutti i dettagli nel nostro corso Recursive Queries in MS SQL Server.

E se state facendo un colloquio per una posizione in SQL, assicuratevi di esaminare i nostri cinque esempi di CTE prima del colloquio!