Tutorial (SQL): quando utilizzare GROUP BY, WHERE e HAVING

Questa breve guida è stata creata per offrire un’introduzione chiara e intuitiva a tre clausole SQL fondamentali per aggregare le righe di un set di dati.

In particolare, se vi state preparando per un colloquio di lavoro nel campo della data analysis o dello sviluppo software, è probabile che vi venga chiesto di dimostrare le vostre competenze in SQL, e le clausole più frequentemente discusse sono `GROUP BY`, `WHERE` e `HAVING`; non solo per la loro importanza nell’aggregazione dei dati, ma anche perché sono spesso fonte di confusione, quindi utilizzate dai reclutatori per valutare le capacità analitiche dei candidati.

Introduzione

L’aggregazione è un concetto fondamentale quando si lavora con grandi set di dati, specialmente in settori come l’analisi ambientale o la sostenibilità. Ad esempio, nell’analisi di dati relativi al cambiamento climatico, potrebbe essere più informativo esaminare le tendenze per diversi paesi o gruppi socioeconomici piuttosto che focalizzarsi su una media globale. Questo tutorial si concentrerà su come utilizzare le clausole SQL `GROUP BY`, `WHERE` e `HAVING` per realizzare queste tipologie di analisi dettagliate, un argomento frequentemente esplorato durante i colloqui di lavoro in vari settori.

Per le esercitazioni pratiche di questo tutorial, faremo uso di un database incentrato sull’analisi del clima. Questo database contiene una vasta gamma di metriche socio-economiche e climatiche relative a vari paesi del mondo nel corso degli anni. La tabella principale, denominata `climate`, include dati chiave come l’adattamento a tecnologie idriche, il volume di commercio, i giorni necessari per registrare una proprietà, il PIL pro capite, la percentuale di occupazione lavorativa, il tasso di iscrizione scolastica e l’indice di stress idrico. Questo set di dati offre quindi una panoramica completa e dettagliata delle sfide globali associate al cambiamento climatico.

Utilizzo di GROUP BY

La clausola `GROUP BY` è utilizzata per aggregare dati in base a determinate colonne. Il processo di aggregazione avviene in tre fasi:

• Dividere: i dati vengono suddivisi in gruppi in base ai valori unici nella(e) colonna(e) specificata(e).
• Applicare: per ciascun gruppo, si esegue una funzione aggregata come media (`AVG`), minimo (`MIN`), o massimo (`MAX`).
• Combinare: i risultati aggregati sono poi combinati in una tabella unica.

Ad esempio, per identificare i primi cinque paesi con il massimo stress idrico medio nel corso degli anni:

SELECT 
    country, 
    AVG(water_stress_index) AS avg_water_stress
FROM climate
GROUP BY country
ORDER BY avg_water_stress DESC
LIMIT 5;

In questa query:

• Dividiamo i dati in gruppi in base ai valori unici nella colonna `country`.
• Applichiamo la funzione aggregata media (`AVG`) alla colonna `water_stress_index` per ogni gruppo.
• Combiniamo i risultati in una tabella unica, ordinando i paesi in base all’indice medio di stress idrico in ordine decrescente.
• Limitiamo i risultati ai primi 5 paesi.

Invece, per calcolare il PIL pro capite medio, il numero totale di anni registrati e l’indice medio di stress idrico per ciascun paese:

SELECT 
    country,
    AVG(gdp_per_capita) AS avg_gdp_per_capita,
    COUNT(year) AS total_years,
    AVG(water_stress_index) AS avg_water_stress
FROM climate
GROUP BY country
ORDER BY avg_gdp_per_capita DESC;

In questa query:

• Dividiamo i dati in gruppi in base ai valori unici nella colonna `country`.
• Applichiamo diverse funzioni aggregate: la media (`AVG`) per le colonne `gdp_per_capita` e `water_stress_index`, e il conteggio (`COUNT`) per la colonna `year`.
• Combiniamo i risultati in una tabella unica, ordinando i paesi in base al PIL pro capite medio in ordine decrescente.

Le limitazioni di WHERE

Come abbiamo potuto notare, i risultati precedenti presentano dei valori nulli che potrebbero rendere l’analisi non corretta come nel caso della prima query. Per rimuoverli è necessario procedere con il filtraggio dei dati.

La clausola `WHERE` è spesso usata per filtrare i dati. Tuttavia, non è possibile utilizzare funzioni aggregate in combinazione con `WHERE`.

SELECT 
    country, AVG(water_stress_index)
FROM climate
WHERE AVG(water_stress_index) > 0.5
GROUP BY country;

Questa query restituirà il seguente errore:

L’errore è dovuto all’impossibilità di passare funzioni aggregate nella clausola `WHERE`. Questo perché, nell’ordine di esecuzione delle clausole SQL, `WHERE` viene processato prima dell’aggregazione dei dati, rendendo impossibile l’utilizzo di funzioni aggregate a questo stadio.

Per risolvere questo problema è necessario ricorrere ad un’altra clausola SQL: `HAVING`.

Utilizzo di HAVING

`HAVING` supera le limitazioni di `WHERE` permettendo di filtrare i dati già aggregati.
Ad esempio, per aggregare i dati in base ai paesi e filtrare quelli con una media di indice di stress idrico superiore a un certo valore:

SELECT 
    country,
    AVG(water_stress_index) AS avg_stress_index,
    SUM(gdp_per_capita) AS total_gdp,
    AVG(water_related_adaptation_tech) AS avg_adaptation
FROM climate
GROUP BY country
HAVING AVG(water_stress_index) > 0.5
ORDER BY avg_stress_index DESC;

In questo caso, la query non genera alcun errore. È importante notare che, a differenza della clausola `WHERE`, `HAVING` permette l’uso di funzioni aggregate per il filtraggio dei dati.

Per capirne di più, consideriamo un altro esempio nel quale vogliamo identificare i paesi con una media del PIL pro capite superiore a 20.000 e con un indice di stress idrico inferiore a 0.4.

SELECT 
    country,
    AVG(gdp_per_capita) AS avg_gdp,
    AVG(water_stress_index) AS avg_stress
FROM climate
GROUP BY country
HAVING AVG(gdp_per_capita) > 20000 AND AVG(water_stress_index) < 0.4
ORDER BY avg_gdp;

Anche in questo caso, la query non genera errori, dimostrando ulteriormente la flessibilità della clausola `HAVING` nel lavorare con dati aggregati.

Ricordiamo che l’uso di alias nelle funzioni aggregate all’interno della clausola `HAVING` può generare errori, poiché la definizione degli alias avviene solo dopo l’esecuzione di `HAVING` durante la fase `SELECT`.

SELECT 
    country, 
    AVG(gdp_per_capita) AS avg_gdp
FROM climate
GROUP BY country
HAVING avg_gdp > 20000
ORDER BY avg_gdp;

La query restituisce un errore dovuto all’utilizzo dell’alias ‘avg_gdp’ nella clausola `HAVING`. L’alias viene generato con la clausola `SELECT` che però viene eseguita successivamente a `HAVING`, da qui l’origine dell’errore. In pratica, quando viene eseguito `HAVING`, il nome della nuova colonna “avg_gdp” non esiste ancora; quindi, non è possibile procedere con il filtraggio della query. Il problema si risolve modificando la query togliendo il riferimento all’alias:

SELECT 
    country, 
    AVG(gdp_per_capita) AS avg_gdp
FROM climate
GROUP BY country
HAVING AVG(gdp_per_capita) > 20000
ORDER BY avg_gdp;

Questa volta la query non ha restituito alcun errore e abbiamo potuto filtrare correttamente i dati.
Quindi, bisogna sempre ricordarsi che gli alias delle colonne non possono essere utilizzati in `HAVING`, perché la loro generazione avviene in una fase successiva con `SELECT`.

Infine, tornando alla prima query dell’esercizio, con l’aggiunta della clausola `HAVING AVG(water_stress_index) IS NOT NULL` possiamo escludere dall’output finale i risultati con un valore medio di stress idrico nullo, ottenendo l’elenco corretto:

Ordine di esecuzione in SQL

Comprendere l’ordine di esecuzione delle clausole SQL può aiutarvi a evitare errori comuni, come quelli discussi precedentemente, ed è un dettaglio che potrebbe tornarvi utile anche durante un colloquio di lavoro.

L’ordine effettivo di esecuzione è il seguente:

1. `FROM`,
2. `WHERE`,
3. `GROUP BY`,
4. `HAVING`,
5. `SELECT`,
6. `ORDER BY`,
7. `LIMIT`.

Tuttavia, quando scriviamo una query, tendiamo a utilizzare il seguente ordinamento, che differisce da quello effettivo:

1. `SELECT`,
2. `FROM`,
3. `WHERE`,
4. `GROUP BY`,
5. `HAVING`,
6. `ORDER BY` ,
7. `LIMIT`.

L’esecuzione della query inizia con `FROM`, che indica le tabelle da utilizzare, e `SELECT` viene eseguito solo dopo `HAVING`. Questo spiega perché `HAVING` non può utilizzare alias, mentre `ORDER BY` può farlo: `ORDER BY` viene eseguito dopo `SELECT,` quando gli alias sono già stati definiti. Questa comprensione dell’ordine di esecuzione spiega anche perché `HAVING` è utilizzato insieme a `GROUP BY` per applicare condizioni sui dati aggregati, mentre `WHERE` non può farlo.

Conclusione

`WHERE` e `HAVING` differiscono nel momento in cui filtrano i dati: `WHERE` lo fa prima dell’aggregazione, mentre `HAVING` lo fa dopo. Questa distinzione, insieme alla comprensione dell’ordine di esecuzione delle clausole SQL in una query, è particolarmente apprezzata nei colloqui di lavoro. Questo tipo di conoscenza è spesso un punto a favore per le posizioni “entry-level”, mentre per le posizioni “senior”, una comprensione solida di questi concetti è generalmente considerata un requisito base e errori in questo ambito non sono tollerati.

Nota Importante: tutte le query SQL presentate in questo tutorial sono state ottimizzate e testate utilizzando PostgreSQL. Se state utilizzando un sistema di gestione del database differente, come MySQL o SQL Server, potrebbero essere necessarie alcune modifiche per garantire la compatibilità e il funzionamento corretto delle query. Ad esempio, SQL Server dalla versione 2005 permette l’uso di alias nella clausola `HAVING` , mentre MySQL e SQL Server non sono sensibili al maiuscolo/minuscolo (case-sensitive) nei nomi delle tabelle e delle colonne.

Video

Se siete interessati ad approfondire ulteriormente l’argomento o avete domande specifiche da porre, non esitate a contattarmi utilizzando i riferimenti presenti nella mia pagina contatti. Sarò felice di rispondere alle vostre domande e di fornirvi ulteriori informazioni sulla mia attività di Data Analyst. Grazie per aver visitato il mio sito e per l’interesse dimostrato nei confronti del mio lavoro.