Tutti gli operatori visti negli articoli precedenti ci permettono di operare in modo più o meno complesso sui dati. Abbiamo visto, infatti, come filtrare i dati (vedere SELECT: struttura di una query semplice), unire dati provenienti da più tabelle mediante le condizioni di join (vedere SELECT: query con JOIN e GROUP BY), definire gruppi e filtrarli (vedere SELECT: query con JOIN e GROUP BY), includere e/o escludere dei risultati (vedere SELECT: operatore IN, SELECT: operatore NOT IN e costruttore di tupla) e infine operare su insiemi omogenei (vedere SELECT: operatori insiemistici). In molti casi abbiamo suggerito di dividere il problema in sotto-problemi al fine di semplificare la formulazione e la struttura della query. Questo approccio risulta sempre vincente! A questo punto però sorge alcuni piccoli inconvenienti. Se le sotto-query che scriviamo sono molto simili tra di loro dobbiamo ripetere l’istruzione più volte rischiando di commettere errori. Inoltre, a volte, ci serve usare un risultato anche complesso, magari con aggregazioni, per filtrare altre tabelle e/o risultati temporanei. Come facciamo a superare queste limitazioni? Per questo problema ci vengono in soccorso le tabelle derivate e le Common Table Expression (CTE).
In questo articolo scopriremo le tabelle derivate che aiutano a strutturare meglio le nostre istruzioni SQL, ma, soprattutto, ad aumentare la capacità di analisi dei nostri dati. In un altro articolo affronteremo nel dettaglio l’argomento delle CTE. 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 delle tabelle derivate
Come detto in precedenza, in alcuni scenari vorremmo salvare il risultato di una query anche complessa per filtrare i dati di altre tabelle. In altri casi potrebbero servirci i risultati di due query da confrontare. Ovviamente potremmo scrivere un programma nel nostro linguaggio preferito in cui andiamo a sottomettere le query che ci interessano e poi manipoliamo i risultati a nostro piacimento. Questa soluzione, se pur percorribile, non è efficiente. Infatti, il tempo totale di tutte le operazioni sarebbe influenzato sia dalla latenza di rete per ciascuna richiesta al database oltre ovviamente alla nostra capacità di scrivere codice efficiente per manipolare correttamente i dati.
Possiamo chiedere al database di eseguire lui tutte le query e di combinarle in modo opportuno? Ovviamente sì. Possiamo usare una prima soluzione chiamata tabella derivata. Ma cosa sono le tabelle derivate? Non sono nient’altro che tabelle temporanee definite mediante istruzioni SELECT all’interno della clausola FROM. Una volta definite queste possono essere usate come tutte le altre tabelle presenti all’interno del database.
Vantaggi e svantaggi delle tabelle derivate
Il principale vantaggio è la possibilità di dividere il problema in sotto-problemi come affrontato già con le query nidificate. A differenza delle query nidificate i cui risultati possono essere usati solo con gli operatori IN, NOT IN ed eventualmente operatori di uguaglianza/disuguaglianza (qualora restituiscano un solo risultato), le tabelle derivate possono essere usate come vere e proprie tabelle su cui effettuare operazioni di join, filtraggio e raggruppamento.
Un altro vantaggio è la possibilità di scrivere istruzioni SQL complesse restituendo una struttura dati anche molto grande. Non siamo quindi vincolati a selezionare qualche attributo o una funzione di aggregazione, ma possiamo definire uno schema che include diverse informazioni. Questo ci permette, ad esempio, di calcolare statistiche aggregando i dati a livelli diversi di granularità.
Ovviamente ci sono dei risvolti negativi. Le tabelle derivate sono tabelle temporanee che vengono “create” durante l’esecuzione della query principale. Ciò implica che il loro contenuto non viene salvato a database e bisogna ridefinirle ogni volta che ne abbiamo bisogno. Un altro aspetto negativo è che non possono usare la stessa definizione anche nelle query nidificate, ma sono obbligato a ridefinire la tabella derivata nella clausola FROM. Ciò può risultare sia tedioso in fase di scrittura, ma, soprattutto, rischioso in quanto può comportare errori. Infine, l’istruzione finale risulta talvolta molto difficile da leggere.
Esempi di utilizzo delle tabelle derivate
Fino ad ora abbiamo solo parlato a livello teorico delle tabelle derivate. Vediamo nel concreto come si definiscono e come ci possono essere d’aiuto.
Doppia funzione di aggregazione
Supponiamo di voler 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
Il primo problema viene risolto semplicemente raggruppando per il codice del cinema la tabella SALA e calcolando la media dell’attributo Capienza. La query SQL risultante sarà:
SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC;
A questo punto vorremmo calcolare il massimo della misura CapienzaMediaCinema. Negli anni ho visto molti che provano a scrivere qualcosa del genere sperando il database sia così intelligente da comprendere ciò che vorremmo fare.
SELECT MAX(AVG(Capienza)) AS CapienzaMediaMax
FROM SALA
GROUP BY CodC;
Attenzione
Questa soluzione è SBAGLIATISSIMA!!! Non è possibile usare due funzioni di aggregazione annidate a meno di costrutti particolari che vengono definiti nell’extended SQL.
Per superare questa limitazione dobbiamo usare la query del passo 1 come tabella derivata, ossia dobbiamo inserirla nella clausola FROM di un’altra query. A questo punto possiamo applicare la funzione di aggregazione MAX sull’attributo CapienzaMediaCinema. La query corretta è la seguente
SELECT MAX(CapienzaMediaCinema)
FROM ( SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC) AS MEDIE;
Questo è un esempio semplice per capire come definire una tabella derivata e usarla per calcolare un doppio aggregato.
Calcolo di aggregati a due livelli
Vediamo ora come usare la stessa tabella derivata dell’esempio precedente insieme ad altre tabelle. Supponiamo di voler calcolare per ogni città, la media massima della capienza dei cinema. La richiesta è molto simile a quella precedente, ma, in questo caso, il massimo deve essere calcolato in base alla città del cinema. I passi da seguire saranno, pertanto:
- trovare la capienza media di ciascun cinema
- raggruppare i cinema in base alla città e calcolare il valore massimo delle medie
Il primo passo lo abbiamo già risolto precedentemente, pertanto vediamo come risolvere il secondo. La tabella derivata ci restituisce solo il codice del cinema. Per ricavare la città dobbiamo usare la tabella CINEMA e metterla in join con la tabella derivata MEDIE. A quel punto potremmo definire dei gruppi per ogni città e calcolare il massimo dell’attributo CapienzaMediaCinema. La soluzione sarà:
SELECT Città, MAX(CapienzaMediaCinema)
FROM CINEMA C,
( SELECT CodC, AVG(Capienza) AS CapienzaMediaCinema
FROM SALA
GROUP BY CodC) AS MEDIE
WHERE C.CodC=MEDIE.CodC
GROUP BY Città;
Utilizzo di più tabelle derivate
Affrontiamo ora uno scenario molto 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
I primi due passi sono molto simili tra loro ad eccezione del filtraggio sulla data. Infatti, è necessario filtrare la tabella PROIEZIONE per l’intervallo di data desiderato, raggruppare per codice del film e infine calcolare il numero di tuple presenti in ciascun gruppo. La query relativa al mese di giugno 2023 sarà:
SELECT CodF, COUNT(*) NFilmGiugno
FROM PROIEZIONE
WHERE Data >= ‘2023-06-01’ AND Data <= ‘2023-06-30’
GROUP BY CodF;
L’istruzione SQL per il mese maggio 2023 sarà invece:
SELECT CodF, COUNT(*) NFilmMaggio
FROM PROIEZIONE
WHERE Data >= ‘2023-05-01’ AND Data <= ‘2023-05-31’
GROUP BY CodF;
A questo punto confrontiamo i risultati ottenuti per individuare i codici dei film che rispettano il vincolo richiesto. Usiamo le query precedenti come tabelle derivate e le uniamo mediante il codice del film. Infine, filtriamo i risultati imponendo che l’attributo NFilmGiugno sia maggiore o uguale al doppio del valore dell’attributo NFilmMaggio. La query SQL sarà:
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;
Ora non ci resta che recuperare le informazioni dei film individuati, raggruppare per genere e calcolare quanti film appartengono a ciascun gruppo. Per recuperare le informazioni dei film possiamo eseguire questa operazione in due modi:
- effettuando una join con la tabella FILM
- usare l’operatore IN per filtrare la tabella FILM
Optiamo per l’utilizzo dell’operatore IN, ma entrambe le soluzioni sono equivalenti. Infine, nella query esterna andremo a raggruppare per Genere e nella clausola SELECT inseriremo anche la funzione di aggregazione COUNT. La query finale sarà:
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;
Come si può notare la query è molto articolata e difficile da leggere. Nonostante ciò, esegue sia un confronto dei risultati di due query differenti, sia un doppio livello di aggregazione.
