Usare le funzioni di aggregazione
T-SQL offre funzioni di aggregazione come SUM, MAX e AVG per eseguire calcoli che accettano più valori e restituiscono un singolo risultato.
Uso delle funzioni di aggregazione
La maggior parte delle query descritte vengono eseguite su una riga alla volta, usando una clausola WHERE per filtrare le righe. Ogni riga restituita corrisponde a una riga del set di dati originale.
Molte funzioni di aggregazione sono fornite in SQL Server. In questa sezione verranno descritte le funzioni più comuni, ad esempio SUM, MIN, MAX, AVG e COUNT.
Quando si usano le funzioni di aggregazione, è necessario considerare i punti seguenti:
- Le funzioni di aggregazione restituiscono un singolo valore (scalare) e possono essere usate nelle istruzioni SELECT quasi ovunque sia possibile usare un singolo valore. Ad esempio, queste funzioni possono essere usate nelle clausole SELECT, HAVING e ORDER BY. Tuttavia, non possono essere usate nella clausola WHERE.
- Le funzioni di aggregazione ignorano i valori NULL, tranne quando si usa COUNT(*).
- Le funzioni di aggregazione in un elenco SELECT non hanno un'intestazione di colonna a meno che non si specifichi un alias usando AS.
- Le funzioni di aggregazione in un elenco SELECT operano su tutte le righe passate all'operazione SELECT. Se non è presente alcuna clausola GROUP BY, verranno riepilogate tutte le righe che soddisfano qualsiasi filtro nella clausola WHERE. Altre informazioni su GROUP BY sono disponibili nell'argomento successivo.
- A meno che non si usi GROUP BY, non è consigliabile combinare funzioni di aggregazione con colonne non incluse nelle funzioni nello stesso elenco SELECT.
In aggiunta alle funzioni predefinite, SQL Server offre un meccanismo per le funzioni di aggregazione definite dall'utente tramite il supporto CLR (Common Language Runtime) di .NET. Questo argomento non rientra nell'ambito di questo modulo.
Funzioni di aggregazione predefinite
Come accennato, Transact-SQL offre molte funzioni di aggregazione predefinite. Le funzioni di uso comune includono:
Nome della funzione
Sintassi
Descrizione
SUM
SUM(expression)
Somma tutti i valori numerici non NULL in una colonna.
AVG
AVG(expression)
Calcola la media di tutti i valori numerici non NULL in una colonna (sum/count).
MIN
MIN(expression)
Restituisce il numero minore, la data/ora meno recente o la prima stringa (in base alle regole di ordinamento e confronto).
MAX
MAX(expression)
Restituisce il numero maggiore, la data/ora più recente o l'ultima stringa (in base alle regole di ordinamento e confronto).
COUNT o COUNT_BIG
COUNT(*) o COUNT(expression)
Con (*), conta tutte le righe, incluse quelle con valori NULL. Quando una colonna viene specificata come expression, restituisce il conteggio delle righe non NULL per la colonna. COUNT restituisce un valore int; COUNT_BIG restituisce un valore big_int.
Per usare un'aggregazione predefinita in una clausola SELECT, si consideri l'esempio seguente nel database di esempio MyStore:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
I risultati di questa query sono simili ai seguenti:
AveragePrice
MinimumPrice
MaximumPrice
744.5952
2.2900
3578.2700
Si noti che l'esempio precedente riepiloga tutte le righe della tabella Production.Product. È possibile modificare facilmente la query per restituire i prezzi medi, minimi e massimi dei prodotti di una categoria specifica aggiungendo una clausola WHERE, come nell'esempio seguente:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;
Quando si usano aggregazioni in una clausola SELECT, tutte le colonne a cui viene fatto riferimento nell'elenco SELECT devono essere usate come input per una funzione di aggregazione oppure devono essere utilizzate in una clausola GROUP BY.
Si consideri la query seguente che tenta di includere il campo ProductCategoryID nei risultati aggregati:
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Esecuzione dei risultati della query nell'errore seguente
messaggio 8120, livello 16, stato 1, riga 1
La colonna 'Production.ProductCategoryID' non è valida nell'elenco SELECT poiché non è inclusa né in una funzione di aggregazione né nella clausola GROUP BY.
La query considera tutte le righe come singolo gruppo aggregato. Di conseguenza, tutte le colonne devono essere usate come input per le funzioni di aggregazione.
Negli esempi precedenti sono stati aggregati dati numerici come il prezzo e le quantità nell'esempio precedente. Alcune delle funzioni di aggregazione possono essere usate anche per riepilogare i dati di tipo data, ora e carattere. Gli esempi seguenti illustrano l'uso di aggregazioni con date e caratteri:
Questa query restituisce la prima e l'ultima società in base al nome, usando MIN e MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
Questa query restituirà il primo e l'ultimo valore di CompanyName nella sequenza di regole di confronto del database, che in questo caso è in ordine alfabetico:
MinCustomer
MaxCustomer
A Bike Store
Yellow Bicycle Company
Altre funzioni possono essere annidate con funzioni di aggregazione.
Ad esempio, la funzione scalare YEAR viene usata nell'esempio seguente per restituire solo la parte relativa all'anno della data dell'ordine, prima del calcolo di MIN e MAX:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Più vecchio
Più recente
2008
2021
Le funzioni MIN e MAX possono essere usate anche con i dati di tipo data per restituire i valori cronologici più recenti e meno recenti. Tuttavia, AVG e SUM possono essere usate solo per i dati numerici, che includono i tipi di dati integer, money, float e decimal.
Uso di DISTINCT con funzioni di aggregazione
È necessario conoscere l'uso di DISTINCT in una clausola SELECT per rimuovere le righe duplicate. Se usata con una funzione di aggregazione, DISTINCT rimuove i valori duplicati dalla colonna di input prima di calcolare il valore di riepilogo. DISTINCT è utile quando si riepilogano le occorrenze univoche dei valori, ad esempio dei clienti nella tabella Orders.
L'esempio seguente restituisce il numero di clienti che hanno effettuato ordini, indipendentemente dal numero di ordini effettuati:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;
COUNT(<some_column>) conta soltanto il numero di righe che hanno un valore nella colonna. Se non sono presenti valori NULL, COUNT(<some_column>) equivale a COUNT(*). COUNT (DISTINCT <some_column>) conta il numero di valori diversi presenti nella colonna.
Uso delle funzioni di aggregazione con NULL
È importante considerare la possibile presenza di valori NULL nei dati e del modo in cui NULL interagisce con i componenti di query T-SQL che includono la funzione di aggregazione. Tenere presenti le considerazioni seguenti:
- Ad eccezione di COUNT usata con l'opzione (*), le funzioni di aggregazione T-SQL ignorano i valori NULL. Ad esempio, la funzione SUM somma solo valori non NULL. I valori NULL non restituiscono zero. COUNT(*) conta tutte le righe, indipendentemente dal valore o dal non valore in qualsiasi colonna.
- La presenza di valori NULL in una colonna può causare una mancata precisione dei calcoli per AVG che somma solo le righe popolate e divide la somma per il numero di righe non NULL. Potrebbe esserci una differenza nei risultati tra AVG(<column>) e (SUM(<column>)/COUNT(*)).
Ad esempio, si consideri la tabella seguente denominata t1:
C1
C2
1
NULL
2
10
3
20
4
30
5
40
6
50
Questa query illustra la differenza tra il modo in cui AVG gestisce NULL e il modo in cui è possibile calcolare una media con una colonna calcolata SUM/COUNT(*):
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1;
Il risultato sarà:
sum_nonnulls
count_all_rows
count_nonnulls
average
arith_average
150
6
5
30
25
In questo set di risultati la colonna denominata average è l'aggregazione che ottiene internamente la somma di 150 e divide per il totale dei valori non Null nella colonna c2. Il calcolo sarà 150/5 o 30. Poiché la colonna denominata arith_average divide in modo esplicito la somma per il totale di tutte le righe, il calcolo è 150/6 o 25.
Se è necessario riepilogare tutte le righe, di tipo Null o meno, provare a sostituire i valori NULL con un altro valore che non verrà ignorato dalla funzione di aggregazione. A questo scopo, è possibile usare la funzione COALESCE.