Come utilizzare le funzioni aggregate nella clausola WHERE

Filtrare i dati in base al risultato di una funzione aggregata è un'attività comune di analisi dei dati. Come si usano gli aggregati nella clausola DOVE nella clausola WHERE? Dedicheremo l'intero articolo a rispondere a questa domanda.

La combinazione di funzioni aggregate e il filtraggio in base ai loro risultati sono spesso utilizzati nell'analisi dei dati, ad esempio per mostrare le filiali con vendite totali superiori a X, i paesi in cui il numero di post è inferiore a Y, gli studenti con un punteggio medio inferiore a Z e così via.

I nuovi utenti di SQL spesso cercano di effettuare questo tipo di filtraggio nella clausola WHERE. Ma come vedremo in questo articolo, questa non è una soluzione praticabile.

Vediamo quindi come ottenere i risultati desiderati. Prima, però, diamo un'occhiata al nostro set di dati. In questo caso, l'esempio si basa sui dati dello European Climate Assessment & Dataset.

Esempio di dati

Il nostro set di dati è costituito da una sola tabella: temperature_data. Essa contiene informazioni sulla temperatura massima giornaliera registrata in diverse città. Le colonne sono:

• id - L'ID della temperatura registrata.
• city - La città in cui è stata registrata la temperatura.
• date - La data di registrazione della temperatura.
• temperature - La temperatura più alta registrata (in gradi Celsius).

Per essere più grafici, ecco tutti i dati della tabella.

Una funzione aggregata nella clausola WHERE?

Quello che voglio fare con questi dati è trovare la temperatura media più alta per città e mostrare solo le città con una media superiore a 16°C.

I nuovi analisti potrebbero scrivere la query utilizzando la funzione aggregata AVG() nella clausola WHERE:

SELECT city,
	 AVG(temperature) AS average_max_temperature
FROM temperature_data
WHERE AVG(temperature) > 16
GROUP BY city;

Per prima cosa, troviamo la temperatura media più alta per città. Poi usiamo questo risultato nella clausola WHERE; mi sembra corretto. Ma questa query lancia un errore!

Questo errore risponde in modo molto esplicito alla domanda su come si usano le funzioni aggregate nella clausola WHERE: non si usano!

Le funzioni aggregate non sono consentite perché la clausola WHERE è usata per filtrare i dati prima dell' aggregazione. Quindi, anche se WHERE non serve per l'aggregazione, ha altri usi.

Per filtrare i dati in base ai risultati di una funzione aggregata, è necessario utilizzare la clausola HAVING ...la clausola.

Utilizzare le funzioni aggregate nella clausola HAVING

È semplice correggere la query precedente: basta sostituire WHERE con HAVING. La clausola HAVING viene utilizzata per filtrare i dati, proprio come WHERE. La differenza principale tra WHERE e HAVING è che la clausola HAVING HAVING filtra i dati dopo l' aggregazione. Quindi, quando si scrive il codice, bisogna assicurarsi che la clausola HAVING venga dopo la GROUP BY. Questo ha a che fare con l'ordine delle operazioni in SQL.

Riscriviamo la nostra query precedente, eliminando WHERE e aggiungendo HAVING (dopo GROUP BY, ovviamente!):

SELECT city,
	 AVG(temperature) AS average_highest_daily_temperature
FROM temperature_data
GROUP BY city
HAVING AVG(temperature) > 16;

Ancora una volta, selezioniamo la città e troviamo la temperatura media usando la funzione AVG(). Ora, però, i dati sono raggruppati per città e filtrati utilizzando la clausola HAVING per mostrare solo le medie superiori a 16.

Dai dati originali, rimangono le città di Stettino e Berlino perché la loro temperatura media giornaliera più elevata è superiore a 16°C.

Funzioni aggregate e filtri vanno di pari passo, ma non nella clausola WHERE

Sapere che non si possono usare le funzioni aggregate nella clausola WHERE è solo metà della risposta. Naturalmente, è necessario un modo per filtrare l'output delle funzioni aggregate. La clausola HAVING è quel modo!

Entrambe le clausole sono abbastanza simili, soprattutto perché il modo in cui si imposta una condizione è lo stesso. Tuttavia, la cosa importante è sapere che le funzioni aggregate possono essere utilizzate in HAVING, ma non in WHERE.

Affinché questa differenza vi venga naturale, dovrete scrivere molto codice. E per farlo, avrete bisogno di molti esempi, ed è proprio questo l'obiettivo del nostro corso. SQL Practice corso.