AVG (Transact-SQL)
Restituisce la media dei valori di un gruppo. I valori Null vengono ignorati. Può precedere la clausola OVER.
.gif "Icona di collegamento a un argomento")
Convenzioni della sintassi Transact-SQL
Sintassi
AVG ( [ ALL | DISTINCT ] expression )
Argomenti
ALL
Applica la funzione di aggregazione a tutti i valori. Il valore predefinito è ALL.DISTINCT
Indica che la funzione AVG deve essere eseguita solo in ogni istanza univoca di un valore, indipendentemente dal numero di occorrenze del valore.expression
Espressione di un tipo di dati della categoria numerici esatti o numerici approssimati, con l'eccezione del tipo di dati bit. Non sono consentite funzioni di aggregazione e sottoquery.
Tipi restituiti
Il tipo restituito dipende dal tipo del risultato di expression.
Risultato dell'espressione |
Tipo restituito |
|---|---|
tinyint |
int |
smallint |
int |
int |
int |
bigint |
bigint |
Categoria decimal (p, s) |
decimal(38, s) diviso per decimal(10, 0) |
Categorie money e smallmoney |
money |
Categorie float e real |
float |
Osservazioni
Se expression è di un tipo di dati alias, anche il tipo restituito è di tipo alias. Tuttavia, se il tipo di dati di base del tipo alias viene alzato di livello, ad esempio da tinyint a int, il valore restituito è del tipo di dati promosso e non del tipo alias.
AVG () calcola la media di un set di valori dividendo la somma di tali valori per il numero di valori non Null. Se la somma supera il valore massimo per il tipo di dati del valore restituito, verrà restituito un errore.
Esempi
A.Utilizzo delle funzioni SUM e AVG per l'esecuzione di calcoli
Nell'esempio seguente viene calcolata la media delle ore di ferie e la somma delle ore di malattia utilizzate dai vicepresidenti Adventure Works Cycles. Ogni funzione di aggregazione restituisce un singolo valore di riepilogo per tutte le righe recuperate.
USE AdventureWorks2012;
GO
SELECT AVG(VacationHours)AS 'Average vacation hours',
SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';
Set di risultati:
Average vacation hours Total sick leave hours
---------------------- ----------------------
25 97
(1 row(s) affected)
B.Utilizzo delle funzioni SUM e AVG con una clausola GROUP BY
Ogni funzione di aggregazione, se utilizzata con la clausola GROUP BY, restituisce un singolo valore per ogni gruppo anziché per l'intera tabella. Nell'esempio seguente vengono restituiti i valori di riepilogo per ogni area di vendita. Nel riepilogo viene elencata la media dei premi di produttività ricevuti dai venditori in ogni area e la somma delle vendite da inizio anno per ogni area.
USE AdventureWorks2012;
GO
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
Set di risultati:
TerritoryID Average Bonus YTD Sales
----------- --------------------- ---------------------
NULL 0.00 1252127.9471
1 4133.3333 4502152.2674
2 4100.00 3763178.1787
3 2500.00 3189418.3662
4 2775.00 6709904.1666
5 6700.00 2315185.611
6 2750.00 4058260.1825
7 985.00 3121616.3202
8 75.00 1827066.7118
9 5650.00 1421810.9242
10 5150.00 4116871.2277
(11 row(s) affected)
C.Utilizzo di AVG con DISTINCT
Nell'istruzione seguente viene restituito il prezzo medio di listino dei prodotti. Se si specifica DISTINCT, nel calcolo vengono considerati solo i valori univoci.
USE AdventureWorks2012;
GO
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
Set di risultati:
------------------------------
437.4042
(1 row(s) affected)
D.Utilizzo della funzione AVG senza DISTINCT
Senza DISTINCT, la funzione AVG trova il prezzo medio di listino di tutti i prodotti nella tabella Product, inclusi i valori duplicati.
USE AdventureWorks2012;
GO
SELECT AVG(ListPrice)
FROM Production.Product;
Set di risultati:
------------------------------
438.6662
(1 row(s) affected)
E.Utilizzo della clausola OVER
Nell'esempio seguente viene utilizzata la funzione AVG con la clausola OVER per fornire una media mobile delle vendite annuali per ogni area presente nella tabella Sales.SalesPerson. I dati vengono partizionati in base a TerritoryID e ordinati logicamente in base a SalesYTD. La funzione AVG viene pertanto calcolata per ogni area in base all'anno di vendita. Si noti che per TerritoryID 1, sono presenti due righe per l'anno di vendita 2005, a indicare due venditori con vendite in tale anno. Viene calcolata la media delle vendite delle due righe e nel calcolo viene quindi inclusa la terza riga che rappresenta le vendite per l'anno 2006.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;
Set di risultati:
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
(10 row(s) affected)
In questo esempio la clausola OVER non include PARTITION BY. La funzione verrà pertanto applicata a tutte le righe restituite dalla query. La clausola ORDER BY specificata nella clausola OVER determina l'ordine logico in base al quale viene applicata la funzione AVG. La query restituisce una media mobile delle vendite annuali per tutte le aree di vendita specificate nella clausola WHERE. La clausola ORDER BY specificata nell'istruzione SELECT determina l'ordine in cui vengono visualizzate le righe della query.
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;
Set di risultati:
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
(10 row(s) affected)