21st Jul 2022 Tempo di lettura: 13 minuti

Cosa sono le funzioni aggregate in SQL e come si usano?

Indice

Come funzionano le funzioni aggregate
Il ruolo di HAVING e GROUP BY con le funzioni aggregate
Facciamo pratica!
Le funzioni aggregate di SQL come strumento essenziale per la scienza dei dati

I dati sono la vostra fonte di conoscenza. E grazie alle funzioni aggregate SQL, potete estrarre dai dati la conoscenza precisa di cui avete bisogno in modo efficiente. Leggete per saperne di più.

Le principali funzioni aggregate di SQL sono le seguenti:

COUNT(column_name | *) restituisce il numero di righe di una tabella.
SUM(column_name) restituisce la somma dei valori di una colonna numerica.
AVG(column_name) restituisce il valore medio di una colonna numerica.
MIN(column_name) restituisce il valore minimo di una colonna selezionata.
MAX(column_name) restituisce il valore massimo di una colonna selezionata.

In questo articolo discuteremo di ciascuno di questi elementi con degli esempi. Scoprirete cosa succede a NULLs e ai duplicati quando sono sottoposti a funzioni aggregate. Inoltre, spiegheremo *, la parola chiave DISTINCT e l'istruzione CASE.

Iniziamo!

Come funzionano le funzioni aggregate

Le funzioni aggregate di SQL accumulano i dati di più righe in un'unica riga di riepilogo. Il valore accumulato si basa sui valori della colonna passata come argomento. Possiamo raggruppare le righe usando una clausola GROUP BY e filtrarle ulteriormente usando una clausola HAVING.

Un esempio standard è la ricerca del numero di righe di una tabella. In questo caso, aggreghiamo tutte le righe della tabella Libri in un'unica riga.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	27.00

SELECT COUNT(*) AS NumberOfBooks
FROM Books;

NumberOfBooks
4

Facile, no? Utilizzando COUNT(*), è possibile contare il numero di tutte le righe.

Potete anche dare un'occhiata agli altri articoli sulle funzioni aggregate di SQL qui e qui.

L'argomento * delle funzioni aggregate

Se mi chiedete cosa significa * in SQL, vi risponderò che sta per tutti. Viene comunemente utilizzato con l'istruzione SELECT per interrogare tutte le colonne di una determinata tabella. Prendiamo, ad esempio, SELECT * FROM Books, come sopra.

L'argomento * può essere utilizzato anche con la funzione aggregata COUNT(). Essa conta tutte le righe di una tabella. Si consideri, ad esempio, SELECT COUNT(*) as NumberOfBooks FROM Books, come sopra.

È possibile raggruppare i dati in base a qualche colonna o anche in base a molte colonne. Si veda l'esempio seguente:

SELECT Author, COUNT(*) AS NumberOfBooks
FROM Books
GROUP BY Author;

Author	NumberOfBooks
Anthony Molinaro	1
Alan Beaulieu	1
Donald Knuth	2

Conta il numero di libri per autore.

L'argomento * si applica solo alla funzione aggregata COUNT(). Per le altre funzioni di aggregazione, è richiesto come argomento una colonna specifica o una combinazione di colonne.

La parola chiave DISTINCT

La parola chiave DISTINCT indica al database che non si vogliono considerare i valori duplicati.

Ad esempio, COUNT(Author) fornisce il numero di tutti gli autori presenti in una tabella. Ma se lo stesso autore compare più volte in una colonna, l'autore viene contato più volte. Date un'occhiata a questo:

SELECT COUNT(Author) AS NumberOfAuthors
FROM books;

NumberOfAuthors
4

Vedete? Conta quattro autori perché Donald Knuth è contato due volte.

Cosa succede se aggiungiamo la parola chiave DISTINCT?

SELECT COUNT(DISTINCT Author) AS NumberOfAuthors
FROM Books;

NumberOfAuthors
3

Questa volta utilizziamo la parola chiave DISTINCT. Ora, Donald Knuth viene contato solo una volta. Quando si usa la parola chiave DISTINCT, COUNT() deve avere come argomento una colonna specifica. Restituisce il numero di valori unici memorizzati in quella colonna.

Analogamente, è possibile utilizzare la parola chiave DISTINCT con gli argomenti delle funzioni aggregate SUM() e AVG(). Di seguito, sono riportati i risultati dell'esecuzione della funzione SUM() con e senza la parola chiave DISTINCT.

SELECT SUM(DISTINCT Price)
AS TotalDistinctPrice
FROM Books;

TotalDistinctPrice
72

	SELECT SUM(Price)
	AS TotalPrice
	FROM Books;

TotalPrice
97

Come si può notare, quando si utilizza la parola chiave DISTINCT, i libri con lo stesso prezzo vengono considerati una sola volta in SUM(). In questo caso, è più sensato utilizzare la funzione SUM() senza la parola chiave DISTINCT.

Allo stesso modo, quando si calcola un prezzo medio, è meglio non usare la parola chiave DISTINCT; dovremmo considerare ogni prezzo tutte le volte che appare nella colonna. Si veda cosa succede con AVG():

SELECT AVG(DISTINCT Price)
AS TotalDistinctAvg
FROM Books;

TotalDistinctAvg
24

	SELECT AVG(Price)
	AS TotalAvg
	FROM Books;

TotalAvg
24.25

Per le funzioni aggregate MIN() e MAX(), la parola chiave DISTINCT non fa alcuna differenza. Ma non causa nemmeno errori. Perché? Consideriamo un insieme di numeri {1, 2, 2, 3, 4, 5, 5, 6}. I suoi valori massimi e minimi sono rispettivamente 6 e 1. Con la parola chiave DISTINCT, questo insieme diventa {1, 2, 3, 4, 5, 6}, quindi i valori massimo e minimo sono ancora gli stessi.

L'istruzione CASE

L'istruzione CASE categorizza e filtra i dati. È come un guardiano per gli argomenti di una funzione aggregata, che decide quali valori far entrare. Vediamo alcuni esempi per illustrare questo concetto.

Nella seguente query, usiamo un'istruzione CASE come argomento per la funzione COUNT(). Conta solo i libri il cui prezzo è superiore a 20,00 dollari.

SELECT COUNT(CASE WHEN Price > 20 THEN Price END)
AS NumberOfExpensiveBooks
FROM Books;

NumberOfExpensiveBooks
3

Un'istruzione CASE può essere utilizzata come argomento anche per altre funzioni aggregate. Nella query seguente, si sommano i prezzi dei libri che costano esattamente 25,00 dollari. L'istruzione CASE all'interno della funzione SUM() consente di includere nella somma solo i libri con un prezzo di 25,00 dollari.

SELECT SUM(CASE WHEN Price = 25 THEN Price END)
AS BooksSum
FROM Books;

BooksSum
50

Ora, nella prossima query, facciamo la media dei prezzi dei libri che costano meno di 26,00 dollari. L'istruzione CASE all'interno della funzione AVG() consente di includere nella media solo i libri con un prezzo inferiore a 26,00 dollari. La funzione AVG() è un argomento della funzione ROUND(), quindi il risultato della funzione AVG() viene arrotondato a due cifre decimali.

SELECT ROUND(AVG(CASE WHEN Price < 26 THEN Price END), 2)
AS BooksAvg
FROM Books;

BooksAvg
23.33

Nella prossima query, troviamo il prezzo minimo dei libri su SQL che costano meno di 26,00 dollari. L'istruzione CASE all'interno della funzione MIN() consente di includere nell'insieme solo i libri con un prezzo inferiore a 26,00 dollari.

SELECT MIN(CASE WHEN Price < 26 AND Title LIKE '%SQL%' THEN Price END)
AS BooksMin
FROM Books;

BooksMin
20

Quindi, troviamo il prezzo massimo dei libri che costano meno di 25,00 dollari. L'istruzione CASE all'interno della funzione MAX() consente di includere nell'insieme solo i libri con un prezzo inferiore a 25,00 dollari.

SELECT MAX(CASE WHEN Price < 25 THEN Price END)
AS BooksMax
FROM Books;

BooksMax
20

Sono certo che si può già immaginare l'esito di queste query!

Cosa succede ai NULL?

La risposta è semplice. Le funzioni aggregate di SQL ignorano i valori NULL.

Consideriamo una tabella aggiornata Books tabella aggiornata. Questa volta abbiamo un prezzo NULL.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	NULL

COUNT(Price) ora restituisce 3, non 4, e SUM(Price) restituisce 70,00. NULLs viene ignorato in entrambi i casi.

È possibile utilizzare le funzioni aggregate anche con le JOIN! Per saperne di più, consultate il nostro articolo sull' uso delle funzioni aggregate SQL con le JOIN.

Il ruolo di HAVING e GROUP BY con le funzioni aggregate

È facile capire cosa fa una clausola HAVING se si ha familiarità con la clausola WHERE. Una clausola HAVING filtra i valori di uscita delle funzioni aggregate. La clausola GROUP BY consente di dividere i dati in gruppi e di trovare un valore aggregato per ciascun gruppo.

Vediamo un esempio.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25
Donald Knuth	26

Raggruppiamo i dati in base alla colonna Autore utilizzando la clausola GROUP BY. Quindi, limitiamo i valori di AVG(Price) a un valore superiore a 20 utilizzando la clausola HAVING.

Si può provare a usare le clausole WHERE e HAVING insieme per vedere la differenza tra loro.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
WHERE Author LIKE 'A%'
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25

La clausola HAVING è spesso confusa con la clausola WHERE. Ricordate che non è possibile utilizzare funzioni aggregate in una clausola WHERE. Assicuratevi di fare abbastanza pratica e consultate il nostro tutorial su SQL HAVING. Inoltre, date un'occhiata al nostro articolo sull' uso di GROUP BY in SQL per avere maggiori informazioni sulla clausola GROUP BY.

Funzioni aggregate SQL o funzioni finestra SQL? O forse entrambe? Date un'occhiata al nostro articolo sulle somiglianze e le differenze tra le due!

Facciamo pratica!

Non basta leggere. L'SQL richiede molta pratica. Vi proponiamo qui alcuni esempi, in modo che possiate continuare da soli!

Prima di addentrarci negli esempi, assicuratevi di avere ben chiari tutti i concetti di SQL Fundamentals e Standard SQL Functions!

Database di esempio

Di seguito è riportato lo schema del database. Utilizzeremo questo database negli esempi che seguono.

Analizziamo il blueprint del database, partendo da sinistra.

La tabella Customers memorizza i dati relativi ai clienti. La sua chiave primaria è la colonna CustomerId. La colonna Customers e Orders sono collegate tramite la colonna CustomerId. La tabella Ordini memorizza la data dell'ordine e l'ID del cliente che ha effettuato l'ordine. La sua chiave primaria è la colonna OrderId. Il collegamento tra le tabelle Customers e Orders definisce la relazione tra le tabelle. Un cliente può avere zero o più ordini, ma un ordine può essere assegnato a un solo cliente.

Le tabelle Orders e OrderDetails sono collegate tramite la colonna OrderId. Le tabelle Products e OrderDetails sono collegate utilizzando la colonna ProductId. La chiave primaria della tabella OrderDetails è costituita dalle colonne OrderId e ProductId.

Un ordine può essere composto da uno o più prodotti. Pertanto, una riga della tabella Orders tabella può essere collegata a una o più righe della tabella OrderDetails tabella. Inoltre, un prodotto può essere presente in zero o più ordini. Di conseguenza, una riga della tabella Products tabella può essere correlata a zero o più righe della tabella. OrderDetails tabella.

Inseriamo ora alcuni dati nelle nostre tabelle.

La Customers tabella:

CustomerId	FirstName	LastName	Street	HouseNo	City	Country	Email	PhoneNo
1	Rachel	Hartley	Bicetown Road	602	New York	USA	rh@email.com	0123456789
2	Caitlyn	Ray	Friedrichstrasse	44	Berlin	Germany	cr@email.com	0987654321
3	Andrew	Duncan	Lairg Road	38	London	NULL	ad@email.com	0567432678
4	Taylor	Jenkins	Park Row	106	Edinburgh	UK	NULL	0876345123
5	Ben	Holland	Williams Avenue	252	Los Angeles	USA	bh@email.com	0987456789

La tabella Orders (la colonna della data è nel formato GG-MM-AAAA):

OrderId	CustomerId	OrderDate
45	1	10-10-2021
46	2	11-12-2020
47	3	05-05-2021
48	4	09-08-2021
49	5	NULL
50	1	02-06-2021
51	2	07-07-2021

La tabella OrderDetails tabella:

OrderId	ProductId	Quantity
45	100	2
45	101	3
46	100	1
47	102	4
48	101	3
48	103	5
49	104	2
50	100	3
51	101	1

La tabella Products tabella:

ProductId	Name	UnitPrice	AvailableInStock
100	Keyboard	30.00	300
101	USB Drive	20.00	450
102	Mouse	20.00	500
103	Screen	100.00	450
104	Laptop	600.00	200

Ora siamo pronti per iniziare gli esempi.

Esempi con COUNT()

Si inizia con la Customers tabella. Scopriamo quanti clienti ci sono per paese.

SELECT Country, COUNT(CustomerId) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	1
USA	2

Abbiamo selezionato tutti i valori distinti della colonna Country, compreso il valore NULL. La colonna NumberOfCustomers memorizza il numero di clienti per ogni valore della colonna Country.

Cosa succede se utilizziamo la colonna Email come argomento della funzione COUNT()?

SELECT Country, COUNT(Email) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	0
USA	2

Il valore della colonna NumberOfCustomers per il Paese "UK" diventa zero. Questo perché la colonna Email nella tabella Customers tabella è NULL per questo cliente.

Vediamo ora un esempio che utilizza le clausole GROUP BY e HAVING.

SELECT Country, COUNT(Email) AS NumberOfCustomersWithEmail
FROM Customers
WHERE Country IS NOT NULL
GROUP BY Country
HAVING COUNT(Email) > 1;

Country	NumberOfCustomersWithEmail
USA	2

Come prima, selezioniamo i valori della colonna Paese e otteniamo il conteggio dei clienti con e-mail per Paese. Nella clausola WHERE, dichiariamo di non considerare i valori di NULL per la colonna Country. Successivamente, raggruppiamo i dati per Country. Infine, limitiamo i valori della colonna NumberOfCustomersWithEmail a essere maggiori di 1 con una clausola HAVING.

Esempi con SUM()

Verifichiamo quanto valgono tutti i prodotti disponibili.

SELECT SUM(UnitPrice * AvailableInStock) AS AllProductsValue
FROM Products;

AllProductsValue
193000

In questo caso, la funzione aggregata SUM() crea un valore di UnitPrice * AvailableInStock per ogni riga e poi somma tutti i valori.

Supponiamo che ogni ordine di valore superiore a 100,00 dollari abbia diritto a uno sconto. Vogliamo scoprire quali ordini hanno diritto allo sconto.

SELECT OrderId,
       CASE WHEN OrderValue > 100 THEN 1 ELSE 0 END
       AS QualifiesForDiscount
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

OrderId	QualifiesForDiscount
45	1
46	0
47	0
48	1
49	1
50	0
51	0

La query interna seleziona tutti i valori della colonna OrderId e calcola il valore di ogni ordine utilizzando la funzione SUM(). La query esterna utilizza un'istruzione CASE per decidere se l'ordine ha diritto allo sconto (1) o meno (0).

Ora, diciamo che tutti i prodotti con un prezzo unitario superiore a 90,00 dollari sono costosi. Scopriamo il valore totale di tutti i prodotti costosi in magazzino.

SELECT
    SUM(CASE WHEN UnitPrice > 90 THEN UnitPrice * AvailableInStock END)
    AS ExpensiveProductsValue
FROM Products;

ExpensiveProductsValue
165000

Abbiamo passato una dichiarazione CASE come argomento alla funzione SUM(). Questo argomento assicura che vengano considerate solo le righe con il valore UnitPrice superiore a 90,00 dollari. A parte questo, l'esempio è abbastanza simile al primo di questa sezione.

Esempi con AVG()

Verifichiamo il prezzo medio di un ordine.

SELECT AVG(OrderValue) AS AvgOrderValue
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

AvgOrderValue
300

La query interna fornisce il valore totale dell'ordine per ogni ordine. La query esterna calcola il valore medio di un ordine.

Possiamo anche scoprire la quantità media ordinata per prodotto.

SELECT ROUND(AVG(Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
2.67

In media, i nostri clienti acquistano da 2 a 3 articoli di un determinato prodotto in un ordine.

Vediamo cosa cambia se consideriamo solo i valori unici della colonna Quantity.

SELECT ROUND(AVG(DISTINCT Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
3

Il valore di output cambia, perché non vengono più considerati i valori duplicati che appaiono nella colonna Quantity della tabella. OrderDetails della tabella.

Esempi con MAX() e MIN()

Ultimo ma non meno importante! Le funzioni MAX() e MIN() sono piuttosto semplici. Scopriamo gli ordini più vecchi e quelli più recenti.

SELECT MIN(OrderDate) AS EarliestOrder,
       MAX(OrderDate) AS LatestOrder
FROM Orders;

EarliestOrder	LatestOrder
11-12-2020	10-10-2021

La funzione MIN() restituisce la data più vecchia e la funzione MAX() quella più recente.

Possiamo anche identificare i prodotti più economici e quelli più costosi. Per farlo, è possibile effettuare una ricerca nella Products tabella per farlo.

SELECT MIN(UnitPrice) AS CheapestProductPrice,
       MAX(UnitPrice) AS MostExpensiveProductPrice
FROM Products;

CheapestProductPrice	MostExpensiveProductPrice
20	600

Scopriamo quanti ordini ci sono per cliente e otteniamo il numero minimo e massimo di ordini per cliente.

SELECT MIN(NumberOfOrders) AS MinNumberOfOrders,
       MAX(NumberOfOrders) AS MaxNumberOfOrders
FROM
     ( SELECT CustomerId,
              COUNT(OrderID) AS NumberOfOrders
       FROM Orders
       GROUP BY CustomerId );

MinNumberOfOrders	MaxNumberOfOrders
1	2

La query interna seleziona la colonna CustomerId e il numero totale di ordini effettuati da un determinato cliente. La funzione COUNT(OrderId) conta il numero di ordini per cliente. Successivamente, si raggruppano i dati in base alla colonna CustomerId utilizzando una clausola GROUP BY. In questo passaggio, la funzione COUNT(OrderId) conta gli ordini per cliente e non per tutti i clienti insieme. La query esterna seleziona i valori minimi e massimi della colonna NumberOfOrders dalla query interna.

Le funzioni aggregate di SQL come strumento essenziale per la scienza dei dati

Utilizzando le funzioni aggregate, è possibile trovare facilmente le risposte a domande specifiche, come il numero di clienti o il prezzo medio di un ordine. Le funzioni aggregate di SQL ci permettono di analizzare i dati in modo efficiente.

Queste funzioni aggregate SQL di base sono molto utili nella scienza dei dati. Con esse è possibile organizzare i dati nel modo desiderato ed estrarre le informazioni necessarie.

Abbiamo esaminato molti esempi con le funzioni aggregate COUNT(), SUM(), AVG(), MIN() e MAX(). Abbiamo anche esaminato esempi di *, della parola chiave DISTINCT e dell'istruzione CASE come argomenti delle funzioni aggregate. Ora siete pronti a creare il vostro database e ad esercitarvi ancora un po'!

Tags: