Guida per principianti alle funzioni aggregate di SQL

Le funzioni aggregate sono potenti SQL che eseguono calcoli numerici sui dati, consentendo alla query di restituire informazioni sintetiche su una determinata colonna o su un insieme di risultati. Queste funzioni possono essere utilizzate insieme all'istruzione GROUP BY. Vediamo come funzionano con alcuni semplici esempi. esempi.

Funzioni aggregate SQL

Supponiamo di avere utenti residenti in una città e di memorizzare le loro informazioni in due tabelle. Queste tabelle e la loro relazione sono mostrate di seguito:

Inseriamo alcuni dati in questo modello:

INSERT INTO `cities` VALUES (1,'Miami'),(2,'Orlando'),
(3,'Las Vegas'),(4,'Coyote Springs');
INSERT INTO `users` VALUES (1,1,'Jhon','Doe',22),
(2,1,'Albert','Thomson',15),(3,2,'Robert','Ford',65),(4,3,'Samantha','Simpson',9),(5,2,'Carlos','Bennet',42),
(6,2,'Mirtha','Lebrand',81),(7,3,'Alex','Gomez',31);

Ora abbiamo abbastanza materiale per spiegare l'uso di base delle funzioni aggregate di SQL. Iniziamo con una semplice funzione.

MIN

Restituisce il valore più piccolo di una determinata colonna. Per esempio, otteniamo l'età minima del nostro gruppo di utenti:

SELECT MIN(age) FROM users;

Restituisce un "9".

È possibile utilizzare questa funzione anche per trovare informazioni alfabetiche. Proviamo con la colonna "last_name":

SELECT MIN(last_name) FROM users;

Questo restituisce "Bennet", che è il primo in ordine alfabetico.

Nota: L'ordinamento utilizzato per i dati influisce sui risultati di questa query. Ad esempio, nell'ordinamento danese, la "A" viene trattata come "A", l'ultima lettera dell'alfabeto. L'ordinamento latino, invece, considera la "A" come la prima lettera dell'alfabeto.

MAX

In modo simile ma opposto a MIN, MAX restituisce il valore più grande di una colonna. Otteniamo l'età massima dal nostro elenco di utenti:

SELECT MAX(age) FROM users;

Questo restituirà "81".

Proviamo a fare lo stesso con la colonna del cognome:

SELECT MAX(last_name) FROM users;

Il risultato è "Thomson", che è l'ultimo in ordine alfabetico. Ricordate che questo dato può cambiare a seconda della collazione che state utilizzando.

SOMMA

Questa funzione calcola la somma di tutti i valori numerici di una colonna. Utilizziamola per ottenere la somma di tutte le età presenti nella tabella:

SELECT SUM(age) FROM users;

Il risultato sarà "265".

AVG

Serve a calcolare il valore medio di una colonna. Vediamolo in azione, recuperando l'età media dei nostri utenti:

SELECT AVG(age) FROM users;

Restituisce "27,75".

CONTA (colonna)

Restituisce il numero di valori non NULL in una determinata colonna. Se volessimo sapere quanti utenti ci hanno detto la loro età, scriveremmo:

SELECT COUNT(age) FROM users;

Restituirà un "7". Tutti i record della tabella "users" hanno un valore di età. Se un record non avesse un valore di età, sarebbe NULL (e non sarebbe incluso nel risultato di COUNT). Se si desidera contare il numero effettivo di righe della tabella, indipendentemente dal valore della colonna, è necessario utilizzare la funzione COUNT(*). Invece di specificare il nome di una colonna come argomento della funzione, si usa un asterisco:

SELECT COUNT(*) FROM users;

Nel nostro caso, il risultato è "7" perché la tabella ha sette record.

I valori NULL possono creare confusione, ma non preoccupatevi. Mostreremo come gestire i valori NULL nelle funzioni aggregate di SQL più avanti in questo articolo.

Ora che sapete cosa fanno queste funzioni aggregate, vediamo come renderle ancora più utili.

Volete saperne di più su SQL JOINs? Guardate un episodio della nostra serie We Learn SQL su Youtube. Verificate se sapete già tutto sui diversi tipi di JOIN.

Utilizzo di GROUP BY con le funzioni aggregate

L'istruzione GROUP BY consente di eseguire aggregazioni su un gruppo di valori in base a determinate colonne. L'uso di GROUP BY con le funzioni aggregate è frequente, quindi questi esempi saranno un po' più complicati e realistici di quelli semplici usati in precedenza.

Concettualmente, "GROUP BY (column_x)" significa "metti tutti i record che condividono lo stesso valore in "column_x" in un gruppo". Vediamo come funziona con ciascuna delle funzioni già discusse.

MIN + GROUP BY

Supponiamo di voler conoscere l'età del nostro utente più giovane in ogni città. Possiamo vedere che la tabella "users" ha una colonna chiamata "city_id" che identifica la città in cui vive ogni utente. Possiamo usare questa colonna con un'istruzione GROUP BY per vedere l'età più giovane in ogni città:

SELECT 
    city_id, MIN(age)
FROM
    users
GROUP BY city_id;

Per capire meglio cosa sta succedendo, osserviamo i dati grezzi nella tabella "users":

L'uso di MIN() con GROUP BY raggruppa i record in base ai valori della colonna "city_id" prima di calcolare l'aggregato per ciascun gruppo. Se si potessero vedere i valori raggruppati, l'aspetto sarebbe simile a questo:

Quindi la query prenderà le età più basse all'interno di ciascun gruppo. Se potessimo vedere questo passaggio in azione, l'aspetto sarebbe questo:

I valori evidenziati rappresentano i valori calcolati da MIN() per ciascun gruppo.

Infine, la query mostrerà i seguenti risultati:

MAX + GROUP BY

Come avrete già intuito, l'uso di MAX combinato con GROUP BY funziona allo stesso modo di MIN. Restituisce semplicemente il valore più grande per ogni gruppo. Si potrebbe calcolare l'età massima per ogni città in modo analogo:

SELECT 
    city_id, MAX(age)
FROM
    users
GROUP BY city_id;

Questa query raggrupperà gli utenti in base al loro campo "city_id" e poi prenderà il valore massimo dell'età per ogni gruppo. I risultati sono i seguenti:

SOMMA + GRUPPO BY

Possiamo anche calcolare la somma delle età degli utenti in ogni città. Per questo, possiamo eseguire la seguente query...

SELECT 
    city_id, SUM(age)
FROM
    users
GROUP BY city_id;

... che restituirà:

CONTA + GRUPPO PER

Potremmo anche voler calcolare il numero di utenti in ogni città. È facile farlo usando la funzione COUNT:

SELECT 
    city_id, COUNT(age)
FROM
    users
GROUP BY city_id;

Sulla base degli ultimi due esempi, possiamo calcolare l'età media di ogni città dividendo la somma di tutte le età di un gruppo di città per il numero di utenti di quella città. Ecco come fare:

SELECT 
    city_id,
    SUM(age),
    COUNT(age),
    SUM(age) / COUNT(age) as average
FROM
    users
GROUP BY city_id;

Nota: avremmo potuto usare anche COUNT(*) in questo caso, poiché non ci sono record con valori NULL nella colonna "age". In questo caso, COUNT(age) funziona come COUNT(*). Altrimenti il valore sarebbe diverso, come spiegheremo più avanti nella sezione "Gestire i NULL".

AVG + GROUP BY

Nell'esempio precedente, abbiamo calcolato "manualmente" l'età media per ogni città. Possiamo usare la funzione AVG() per eseguire questa operazione, come mostrato di seguito:

SELECT 
    city_id,
    AVG(age)
FROM
    users
GROUP BY city_id;

Il valore restituito da AVG(age) è identico al risultato dell'operazione matematica eseguita in precedenza.

Filtrare i risultati raggruppati

A volte è necessario filtrare ulteriormente i risultati in base alle condizioni generate dai risultati aggregati. L'aggiunta di qualsiasi condizione in WHERE fallirebbe. Non mi credete? Guardate cosa succede quando cerchiamo di recuperare il numero di utenti solo nelle città in cui l'età media degli utenti è superiore a 20 anni:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE AVG(age) >= 20
GROUP BY city_id;

Il motore (nel mio caso, MySQL) si lamenterà. Verrà visualizzato qualcosa di simile a questo:

Codice di errore: 1111. Uso non valido della funzione gruppo

Per filtrare i risultati in questo modo, dobbiamo usare la clausola HAVING. HAVING filtrerà i risultati riassunti di GROUP BY; la clausola WHERE si applica solo ai singoli record. Se un gruppo non soddisfa i criteri della clausola HAVING, non verrà restituito.

Quindi, se si volesse ottenere COUNT per ogni città con un'età media di almeno 20 anni, il modo corretto di gestire il filtraggio sarebbe il seguente:

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
GROUP BY city_id
HAVING AVG(age) >= 20

Nota: il gruppo con city_id = 1 viene scartato, poiché il suo AVG(age) è 18,5.

Infine, WHERE e HAVING possono essere usati contemporaneamente senza alcun problema (se la query ha senso, ovviamente). Notate come funziona nel prossimo esempio. Eseguiamo nuovamente il calcolo, ma questa volta escludiamo tutti gli utenti il cui cognome è "Simpson":

SELECT 
    city_id, COUNT(age), AVG(age)
FROM
    users
WHERE last_name <> 'Simpson'
GROUP BY city_id
HAVING AVG(age) >= 20

Si noti che il calcolo di COUNT e AVG è diverso per city_id = 3. Esiste un utente con il cognome "Simpson" per city_id = 3, e quel record è stato scartato a causa della condizione WHERE last_name <> 'Simpson’.

Gestire i NULL

Finora tutte le colonne sono state riempite di dati, ma non è sempre così. Inseriamo alcuni record con età NULL per spiegare alcuni casi limite. I due INSERTseguenti aggiungono due nuovi utenti, con ID rispettivamente 8 e 9, che hanno un NULL nella colonna età:

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`)
VALUES ('8', '2', 'Frederic', 'Scott',NULL);

INSERT INTO `users` (`id`, `city_id`, `first_name`, `last_name`, `age`) 
VALUES ('9', '4', 'Stacy', 'Roberts',NULL);

I record con valori nulli non vengono considerati in MIN, MAX, SUM, AVG e COUNT(column). Questi record vengono semplicemente ignorati nel calcolo. Una regola generale è che se non si prevede di avere valori nulli o se si prevede di trattare i NULL come "0", allora si dovrebbero impostare le colonne come NOT NULL e impostare le colonne NULL a "0" o a qualsiasi valore che abbia senso per il caso d'uso.

CONTE(colonna) vs CONTE(*)

La funzione COUNT(column) non conterà i record con valori di età NULL. Vediamolo in azione:

SELECT 
    COUNT(age)
FROM
    users;

Questa query restituisce di nuovo un "7"; i due record che abbiamo aggiunto hanno valori NULL nella colonna "age", quindi vengono ignorati. Se si volesse contare tutti i record, indipendentemente dal loro valore, si potrebbe utilizzare il metodo COUNT(*) function:


SELECT 
    COUNT(*)
FROM
    users;

Questo restituisce il risultato atteso di "9".

Vediamo come i NULL influiscono sulla funzione AVG().

AVG + NULL

Poiché AVG ignora i record con NULL nella colonna specificata, il valore risultante potrebbe non avere senso. Ecco perché.

La funzione AVG(age) aggiungerà solo gli utenti con un valore non nullo nella colonna "age" e dividerà questo numero per il numero COUNT di utenti che hanno anche un valore non nullo "age". Nel nostro esempio il numero è 7. Se si considera che i due utenti appena aggiunti con età NULL devono essere considerati per il calcolo dell'età media, il valore ottenuto con AVG(age) sarà sbagliato. La query seguente mostra la differenza nei calcoli:

SELECT 
    SUM(age),
    COUNT(age),
    AVG(age),
    SUM(age) / COUNT(age),
    COUNT(*),
    SUM(age) / COUNT(*)
FROM
    users;

Si noti come il valore di AVG(age) corrisponda al valore calcolato con SUM(age)/COUNT(age); entrambe le funzioni non considerano i record con valori NULL. Ma guardate come cambia il valore medio quando si usa COUNT(*), che include tutti i record.

Abbiamo visto che i valori NULL non vengono calcolati nelle funzioni MIN, MAX, AVG, SUM e COUNT(column_name). Se si intende utilizzare queste funzioni e si prevedono campi NULL, assicurarsi di impostare i NULL come un valore specifico.

Inoltre, impostare i tipi di colonna in modo coerente con il valore che si sta memorizzando. Ad esempio, i numeri memorizzati in una colonna VARCHAR non saranno trattati come numerici e potrebbero causare risultati indesiderati nei calcoli delle funzioni aggregate.

Infine, vorrei sottolineare l'importanza di inserire le condizioni di filtraggio nei punti giusti delle query. Utilizzate HAVING se avete bisogno di condizioni basate su valori aggregati.

Cosa ne pensate delle funzioni aggregate SQL? Avete esperienze o esempi sul loro utilizzo? Condividete i vostri pensieri con la nostra comunità. E non perdetevi la seconda parte di questo articolo, in cui spiegherò come integrare le funzioni aggregate di SQL con le JOIN. La pubblicheremo presto sul blog di Vertabelo!