Dopo aver visto vari costrutti del linguaggio SQL, abbiamo quasi tutti gli strumenti per scrivere interrogazioni sia semplici ma anche molto complesse. Siamo, infatti, partiti dalla struttura base di una query per filtrare i dati in base ad alcune condizioni (vedere SELECT: struttura di una query semplice), per passare a unire informazioni salvate in tabelle diverse e definire gruppi di dati per calcolare alcune statistiche (vedere SELECT: query con JOIN e GROUP BY). Non dimentichiamo anche gli operatori di inclusione (SELECT: operatore IN), esclusione ( SELECT: operatore NOT IN e costruttore di tupla ) e insiemistici ( SELECT: operatori insiemistici ). Infine, abbiamo introdotto il concetto delle tabelle derivate ( SQL: tabelle derivate ) per salvare un risultato temporaneo e usarlo come una nuova tabella del nostro database. Proprio quest’ultimo costrutto ci ha permesso di aumentare il potere espressivo e computazionale delle nostre query. Nonostante ciò, le query che usano questo costrutto risultano soprattutto difficili da leggere oltre ad altre limitazioni già discusse. Per superare questi svantaggi sono state introdotte le Common Table Expression o più brevemente le CTE.
In questo articolo affronteremo, pertanto le CTE descrivendo prima la sintassi e le sue caratteristiche, per poi comprendere il loro funzionamento e i vantaggi mediante esempi reali. Come negli articoli precedenti, useremo lo schema relativo alle proiezioni dei film nei cinema. Lo riportiamo qui sotto per chi non avesse letto gli altri articoli.
FILM (CodF, Titolo, Data_uscita, Genere, DurataMinuti)
CINEMA (CodC, Nome, Indirizzo, Città, SitoWeb*)
SALA (CodC, NumeroSala, Capienza)
PROIEZIONE (CodC, NumeroSala, Data, OraInizio, OraFine, CodF)
Vi ricordo che la notazione usata per descrivere lo schema usa la sottolineatura per indicare i campi che compongono la chiave primaria e l’asterisco per segnalare la possibilità di assegnare valori NULL al campo. Infine, per semplificare, le chiavi esterne hanno lo stesso nome delle chiavi primarie a cui si riferiscono. Procediamo quindi a vedere i vari operatori.
Definizione CTE
In modo analogo alle tabelle derivate, le CTE permettono di salvare temporaneamente il risultato di una query per poi riutilizzarlo all’interno di un’altra interrogazione come se fosse una tabella. Per definirle si usa il costrutto WITH come di seguito:
Come si evince dalla figura, dopo il comando WITH è necessario assegnare un nome a ciascuna query che si andrà a scrivere come CTE. Si può opzionalmente specificare i nomi dei campi. Se non vengono specificati verranno utilizzati quelli definiti nella clausola SELECT della query associata alla CTE. Ovviamente è possibile definire un numero arbitrario di CTE. Le CTE così definite possono essere poi utilizzate nella query principale come semplici tabelle insieme a quelle già presenti nel database.
Vantaggi e svantaggi delle CTE
Come per le tabelle derivate e le query nidificate, le CTE permettono di dividere il problema in sotto-problemi. Questo vantaggio ci ha già permesso negli articoli precedenti di risolvere interrogazioni molto complesse in maniera incrementale. Infatti, possiamo scrivere query SQL anche molto complesse per farci restituire tabelle con uno schema a-hoc per le nostre esigenze.
Il principale vantaggio delle CTE, però, è che queste “nuove” tabelle possono essere in ogni punto della query superando quindi le limitazioni degli altri costrutti. Infatti, una CTE può essere usata non solo nella query principale, ma anche dalle altre CTE a valla della sua definizione. Questo aspetto ci permette di calcolare, ad esempio, diversi livelli di aggregazione sui dati senza dover ricorrere a costrutti dedicati ai data warehouse. Non solo! Vedremo in un altro articolo come usare il riferimento incrociato tra CTE per eseguire la ricorsione.
Le CTE presentano anche altri vantaggi rispetto alle loro “sorelle”, le tabelle derivate. Il primo vantaggio è la leggibilità. Poiché le query associate a ciascuna CTE sono definite all’inizio e non all’interno delle clausole FROM il codice risulta più pulito e ordinato. Lo noteremo successivamente mediante gli esempi. Un altro vantaggio è la riduzione degli errori. Avendo un solo punto in cui una CTE viene definita, indipendentemente da quante volte venga successivamente usata, è più facile debuggare la singola CTE e aggiornarne la definizione senza imbattersi in errori di copiatura in più punti della query.
Anche le CTE hanno uno svantaggio, seppur trascurabile a nostro avviso: il loro contenuto non viene salvato su disco. Per questo motivo, sarà necessario ridefinire le CTE comuni per ogni query frequente che scriveremo.
Esempi di utilizzo delle CTE
Per comprendere al meglio l’uso delle CTE e gli enormi vantaggi che introducono analizziamo gli stessi esempi che abbiamo visto nell’articolo XXX. Riporteremo per ciascun esempio anche la soluzione con le tabelle derivate per mostrare le differenze a livello sintattico.
Doppia funzione di aggregazione
Partiamo con l’esempio che ci richiede di trovare la media massima della capienza dei cinema. Per far ciò dobbiamo dividere il problema in due task:
- trovare la capienza media di ciascun cinema
- calcolare il valore massimo delle medie calcolate precedentemente
La soluzione con le tabelle derivate richiedeva di definire nella clausola FROM una query che calcolasse per ciascun cinema la capienza media. A quel punto era sufficiente calcolare il massimo sul valore aggregato. La soluzione era la seguente:
SELECT MAX(CapienzaMediaCinema)
FROM ( SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC) AS MEDIE;
Come già discusso la soluzione non è facile da comprendere leggendo solamente l’SQL. Proviamo a riscrivere il tutto usando le CTE!
Per prima cosa dobbiamo definire la nostra CTE che corrisponderà alla query della tabella derivata.
WITH
MEDIE AS (
SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC)
Come potete notare già ad un primo impatto l’SQL risulta molto più leggibile e pulito. Possiamo, infatti capire subito cosa stiamo calcolando senza doverci preoccupare di come verrà usato questo risultato.
A questo punto dobbiamo solo usare la CTE nella query principale come se fosse una semplice tabella del nostro database. La query finale sarà quindi
WITH
MEDIE AS (
SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC)
SELECT MAX(CapienzaMediaCinema)
FROM MEDIE;
Il risultato sarà lo stesso di quello ottenuto con la tabella derivata, ma in fase di scrittura e anche di validazione della sintassi risulta molto semplice. La divisione in sotto-problemi equivale a scrivere tante CTE quante sono necessarie.
Calcolo di aggregati a due livelli
Riprendiamo ora l’esempio che calcola per ogni città, la media massima della capienza dei cinema. I passi da seguire saranno:
- trovare la capienza media di ciascun cinema
- raggruppare i cinema in base alla città e calcolare il valore massimo delle medie
Possiamo riutilizzare semplicemente la CTE dell’esempio precedente ed effettuare poi una join con la tabella CINEMA nella query principale.
WITH
MEDIE AS (
SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC)
SELECT Città, MAX(CapienzaMediaCinema)
FROM CINEMA C, MEDIE M
WHERE C.CodC=MEDIE.CodC
GROUP BY Città;
Abbiamo però anche un’altra soluzione! All’interno della CTE non selezioniamo solamente il codice del cinema e la media della capienza delle sue sale, ma riportiamo anche la città del cinema. In questo modo la query principale dovrà solo effettuare un raggruppamento su una singola tabella, demandando il join alla CTE. Attenzione però ad inserire nella GROUP BY della CTE anche il campo Città. La query finale sarà la seguente:
WITH
MEDIE AS (
SELECT C.CodC, Città, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA S, CINEMA C
WHERE C.CodC=S.CodC
GROUP BY C.CodC, Città)
SELECT Città, MAX(CapienzaMediaCinema)
FROM MEDIE M
GROUP BY Città;
Utilizzo di più CTE
Affrontiamo ora lo scenario più complesso. Vogliamo calcolare per ogni genere dei film, il numero di film che sono stati proiettati nel mese di giugno 2023 un numero di volte uguale o superiore al doppio rispetto al mese precedente. Scomponiamo il problema come sempre. I passi da effettuare sono:
- calcolare il numero di proiezioni per ciascun film nel mese di giugno 2023
- calcolare il numero di proiezioni per ciascun film nel mese di maggio 2023
- confrontare i valori e selezionare solo i film che rispettano il vincolo richiesto
- raggruppare per genere e calcolare il numero di elementi appartenenti a ciascun gruppo
Se vi ricordate la soluzione con le tabelle derivate era molto difficile sia da scrivere che da leggere (a patto di riuscire a finirla di scrivere correttamente!). Ve la riportiamo di seguito per semplicità.
SELECT Genere, COUNT(*)
FROM FILM
WHERE CodF IN (
SELECT Giugno.CodF
FROM (SELECT CodF, COUNT(*) NFilmGiugno
FROM PROIEZIONE
WHERE Data >= ‘2023-06-01’ AND Data <= ‘2023-06-30’
GROUP BY CodF) AS Giugno
(SELECT CodF, COUNT(*) NFilmMaggio
FROM PROIEZIONE
WHERE Data >= ‘2023-05-01’ AND Data <= ‘2023-05-31’
GROUP BY CodF) AS Maggio
WHERE Giugno.CodF=Maggio.CodF
AND NFilmGiugno >= 2* NFilmMaggio)
GROUP BY Genere;
Vediamo ora di affrontare lo stesso problema con le CTE. Come detto in precedenza, sarà sufficiente inserire una CTE per ogni sotto-problema che dobbiamo risolvere. Partiamo, quindi, con il calcolo del numero di proiezioni per ciascun film nei mesi di interesse. Le CTE corrispondente saranno:
WITH
Giugno AS (
SELECT CodF, COUNT(*) NFilmGiugno
FROM PROIEZIONE
WHERE Data >= ‘2023-06-01’ AND Data <= ‘2023-06-30’
GROUP BY CodF),
Maggio AS
(SELECT CodF, COUNT(*) NFilmMaggio
FROM PROIEZIONE
WHERE Data >= ‘2023-05-01’ AND Data <= ‘2023-05-31’
GROUP BY CodF)
Ora possiamo identificare i film che rispettano il vincolo richiesto. Per farlo usiamo una CTE, chiamata FILM_MIGLIORI, che sfrutta quelle appena definite. Riportiamo solo la sua definizione di seguito
WITH
…,
FILM_MIGLIORI AS
(SELECT Giugno.CodF
FROM Giugno G, Maggio M
WHERE G.CodF=M.CodF
AND NFilmGiugno >= 2* NFilmMaggio)
Ora non ci resta che recuperare le informazioni dei film individuati, raggruppare per genere e calcolare quanti film appartengono a ciascun gruppo. Come per la soluzione con le tabelle derivate, possiamo recuperare le informazioni dei film in due modi:
- effettuando una join con la tabella FILM
- usare l’operatore IN per filtrare la tabella FILM
Vediamo in prima istanza la soluzione con la join nella query principale. La soluzione finale sarà
WITH
Giugno AS (
SELECT CodF, COUNT(*) NFilmGiugno
FROM PROIEZIONE
WHERE Data >= ‘2023-06-01’ AND Data <= ‘2023-06-30’
GROUP BY CodF),
Maggio AS
(SELECT CodF, COUNT(*) NFilmMaggio
FROM PROIEZIONE
WHERE Data >= ‘2023-05-01’ AND Data <= ‘2023-05-31’
GROUP BY CodF),
FILM_MIGLIORI AS
(SELECT Giugno.CodF
FROM Giugno G, Maggio M
WHERE G.CodF=M.CodF
AND NFilmGiugno >= 2* NFilmMaggio)
SELECT Genere, COUNT(*)
FROM FILM, FILM_MIGLIORI FM
WHERE F.CodF=FM.CodF
GROUP BY Genere;
Ovviamente potevano usare la soluzione con la IN che risulterà:
WITH
Giugno AS (
SELECT CodF, COUNT(*) NFilmGiugno
FROM PROIEZIONE
WHERE Data >= ‘2023-06-01’ AND Data <= ‘2023-06-30’
GROUP BY CodF),
Maggio AS
(SELECT CodF, COUNT(*) NFilmMaggio
FROM PROIEZIONE
WHERE Data >= ‘2023-05-01’ AND Data <= ‘2023-05-31’
GROUP BY CodF),
FILM_MIGLIORI AS
(SELECT Giugno.CodF
FROM Giugno G, Maggio M
WHERE G.CodF=M.CodF
AND NFilmGiugno >= 2* NFilmMaggio)
SELECT Genere, COUNT(*)
FROM FILM
WHERE CodF IN (SELECT CodF
FROM FILM_MIGLIORI FM)
GROUP BY Genere;
Entrambe le soluzioni risultano molto leggibili, oltre che facili da scrivere!
Una risposta