Istruzione CASE in SQL spiegata

Se hai lavorato con i database, avrai notato che i dati raramente sono perfetti e spesso richiedono manipolazioni per ottenere insight significativi.

Per soddisfare questa esigenza, SQL offre una potente costruzione, chiamata istruzione CASE, che ti consente di creare nuove colonne o trasformare i dati in base a condizioni applicate a colonne esistenti.

In questo articolo spieghiamo cos’è un’istruzione CASE, perché è essenziale e vediamo vari casi d’uso per aiutarti a sfruttarne il potenziale. Partiamo!

Che cos’è un’istruzione CASE?

L’istruzione CASE in SQL è un’espressione condizionale che introduce una logica decisionale nella query. Funziona in modo simile a un if-elif-else in Python, permettendoti di valutare più condizioni e restituire risultati specifici in base a tali condizioni. 

La sintassi generale è la seguente:

CASE
	WHEN boolean_condition1 THEN result1
	WHEN boolean_condition2 THEN result2
	ELSE result3
END

Funziona così:

• La parola chiave CASE segna l’inizio dell’espressione, mentre END ne indica la conclusione, racchiudendo l’istruzione condizionale come in una cornice.
• Ogni clausola WHEN valuta una condizione. Se la condizione è vera, la corrispondente clausola THEN restituisce un risultato specificato.
• Se nessuna delle condizioni nelle clausole WHEN è soddisfatta, la clausola ELSE fornisce un risultato predefinito.

Perché l’istruzione CASE è importante?

L’istruzione CASE WHEN è uno strumento prezioso nelle query SQL, con diversi vantaggi chiave:

• Trasformazione dei dati: ti consente di creare nuove colonne basate sui valori di colonne esistenti.
• Aggregazione condizionale: ti permette di calcolare valori aggregati (ad es. somme, conteggi, massimi) per sottoinsiemi specifici di dati in base a condizioni.
• Filtraggio dei dati: puoi includere o escludere righe in base a criteri specifici.
• Semplificazione e leggibilità delle query: aiuta a consolidare la logica in un’unica query, riducendo la necessità di più query, tabelle temporanee o join complessi.
• Ottimizzazione delle prestazioni: riducendo la complessità delle query SQL, l’istruzione CASE può contribuire a ottimizzare le prestazioni e minimizzare i tempi di esecuzione, soprattutto quando evita calcoli e trasformazioni aggiuntivi.

Istruzione CASE semplice

In questo articolo useremo la tabella film del database cinema. Supponiamo di voler creare una nuova colonna categoriale in base ai valori dell’ROI (rapporto incassi/budget):

SELECT 
	title,
	gross,
	budget,
	gross / budget AS ROI,
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Dati incassi e budget, abbiamo calcolato l’ROI, una metrica di redditività molto usata che misura il ritorno generato da un investimento.

Oltre al campo ROI, abbiamo anche ottenuto la sua categorizzazione per comunicare meglio:

• Per i film con ROI inferiore a 1, il valore è “low ROI”.
• I film con ROI tra 1 e 2 appartengono alla categoria “medium ROI”.
• “High ROI” è l’etichetta assegnata ai film con ROI maggiore o uguale a 2.

Abbiamo anche filtrato i film con budget o incassi pari a zero o negativi. Inoltre, abbiamo limitato i risultati ai film usciti nel 2015.

Uso di CASE nella clausola GROUP BY

Nel primo esempio abbiamo creato un nuovo campo, che può essere applicato per aggregare i dati e ottenere insight aggiuntivi.

Per esempio, possiamo calcolare l’incasso medio e il budget medio per ciascun gruppo di ROI:

SELECT 
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;

Per raggruppare i dati, inseriamo semplicemente l’istruzione CASE nella clausola GROUP BY. Per avere altre informazioni, abbiamo ordinato le righe in ordine decrescente in base all’incasso medio. In questo modo notiamo che l’incasso medio più alto è associato all’alto ROI e viceversa per il basso ROI.

Uso di CASE nella clausola WHERE

Un altro vantaggio dell’istruzione CASE è che consente di filtrare le righe in base a condizioni specifiche. Ad esempio, possiamo applicare soglie di ROI differenti in base alla lingua del film. È noto che i film in inglese hanno ROI costantemente elevati. Quindi, la soglia dovrebbe avere un valore più alto, mentre supponiamo che le altre lingue abbiano ROI più bassi.

SELECT 
    title,
    gross,
    budget,
    language,
    gross / budget AS ROI
FROM cinema.films
WHERE 
    CASE 
        WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
        WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
        ELSE gross / budget > 1.3 -- Default for other languages
    END;

Calcolo delle aggregazioni

Possiamo anche essere interessati a calcolare il numero di film con ROI alto rispetto a ROI basso usciti nel 2015:

SELECT 
    SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
    SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
    SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

All’interno della funzione SUM, è come se creassimo un nuovo campo con valore 1 quando l’ROI raggiunge quella soglia, 0 altrimenti. Se sommiamo questa nuova variabile binaria, otteniamo il numero di film che rispettano quel criterio.

Dall’output possiamo notare che la maggior parte dei film ha un ROI inferiore a 1, mentre pochi film sono altamente redditizi.

Istruzione CASE annidata

Uno dei casi d’uso più avanzati è l’istruzione CASE annidata, cioè un CASE dentro un altro CASE. È molto utile quando devi suddividere i dati in sottogruppi e applicare una logica diversa per ciascun sottogruppo. 

In modo simile al primo esempio, vogliamo creare una nuova colonna che dipenda dai valori dell’ROI. Come detto in precedenza, l’ROI dei film in lingua inglese è più alto a livello globale rispetto ai film in altre lingue. 

Per questo motivo, è meglio categorizzare i film in gruppi di ROI considerando sia l’ROI sia la lingua

SELECT 
    title,
    language,
    gross / budget AS ROI,
    CASE
        WHEN language = 'English' THEN 
            CASE 
                WHEN gross / budget < 2 THEN 'low ROI'
                WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
                ELSE 'high ROI'
            END
        ELSE 
            CASE
                WHEN gross / budget < 1 THEN 'low ROI'
                WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
                ELSE 'high ROI'
            END
    END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;

Nella query, l’istruzione CASE esterna verifica se la lingua è l’inglese o meno. A seconda della lingua, il CASE interno valuta l’ROI: le soglie sono più alte per i film in inglese e più basse per i film non in inglese. Questa condizione multilivello consente di migliorare la separazione tra film a ROI basso, medio e alto.

Conclusione

L’istruzione CASE è una costruzione SQL indispensabile per estrarre insight significativi dai tuoi dati. La teoria si alterna a esempi pratici per aiutarti a comprendere a fondo questa potente espressione condizionale. Se vuoi approfondire, dai un’occhiata al corso di manipolazione dei dati in SQL. Copre l’istruzione CASE, oltre ad altri aspetti chiave di SQL, come le sottoquery e le funzioni finestra. Infine, dai un’occhiata a SQL Fundamentals per avere una panoramica dei corsi che aiutano a padroneggiare SQL.