AVG (Transact-SQL)
Gibt den Mittelwert der Werte in einer Gruppe zurück. NULL-Werte werden ignoriert. Darauf folgt möglicherweise die OVER-Klausel.
Transact-SQL-Syntaxkonventionen
Syntax
AVG ( [ ALL | DISTINCT ] expression )
Argumente
ALL
Wendet die Aggregatfunktion auf alle Werte an. ALL ist die Standardeinstellung.DISTINCT
Gibt an, dass AVG nur für jede eindeutige Instanz eines Werts ausgeführt werden soll, unabhängig davon, wie oft der Wert vorkommt.expression
Ist ein Ausdruck der exakten numerischen oder ungefähren numerischen Datentypkategorie, mit Ausnahme des bit-Datentyps. Aggregatfunktionen und Unterabfragen sind nicht zulässig.
Rückgabetypen
Der Rückgabetyp wird durch den Typ des ausgewerteten Ergebnisses von expression bestimmt.
Ausdrucksergebnis |
Rückgabetyp |
---|---|
tinyint |
int |
smallint |
int |
int |
int |
bigint |
bigint |
decimal-Kategorie (p, s) |
decimal(38, s) dividiert durch decimal(10, 0) |
Kategorien money und smallmoney |
money |
Kategorien float und real |
float |
Hinweise
Wenn der Datentyp von expression ein Aliasdatentyp ist, ist der Rückgabetyp ebenfalls ein Aliasdatentyp. Wird der Basisdatentyp des Aliasdatentyps jedoch heraufgestuft, beispielsweise von tinyint zu int, handelt es sich beim Rückgabetyp um den heraufgestuften Datentyp und nicht um den Aliasdatentyp.
AVG () berechnet den Durchschnitt einer Menge von Werten, indem die Summer dieser Werte durch die Anzahl der Nicht-Nullwerte dividiert wird. Wenn die Summe größer als der vom Datentyp des Rückgabewerts unterstützte Höchstwert ist, wird ein Fehler zurückgegeben.
Beispiele
A.Verwenden der Funktionen SUM und AVG für Berechnungen
Im folgenden Beispiel werden die durchschnittlichen Urlaubstage und die Summe der Krankheitstage (in Stunden) der stellvertretenden Direktoren von Adventure Works Cycles berechnet. Beide Aggregatfunktionen erzeugen jeweils einen zusammenfassenden Wert für alle abgerufenen Zeilen.
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%';
Dies ist das Resultset.
Average vacation hours Total sick leave hours
---------------------- ----------------------
25 97
(1 row(s) affected)
B.Verwenden der Funktionen SUM und AVG mit einer GROUP BY-Klausel
In Verbindung mit einer GROUP BY-Klausel erzeugt jede Aggregatfunktion einen einzelnen Wert für jede Gruppe anstelle eines Werts für die gesamte Tabelle. Im folgenden Beispiel werden zusammenfassende Werte für die einzelnen Vertriebsregionen erstellt. In der Zusammenfassung werden die durchschnittlichen Bonusleistungen von Vertriebsmitarbeitern der einzelnen Regionen und die Summe der Verkaufszahlen für das laufende Jahr in den Regionen aufgelistet.
USE AdventureWorks2012;
GO
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
Dies ist das Resultset.
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.Verwenden von AVG mit DISTINCT
In der folgenden Anweisung wird der durchschnittliche Listenpreis von Produkten zurückgegeben. Durch Angabe von DISTINCT werden nur eindeutige Werte in der Berechnung berücksichtigt.
USE AdventureWorks2012;
GO
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
Dies ist das Resultset.
------------------------------
437.4042
(1 row(s) affected)
D.Verwenden von AVG ohne DISTINCT
Ohne DISTINCT wird mithilfe der AVG-Funktion nach dem durchschnittlichen Listenpreis aller Produkte in der Product-Tabelle gesucht, einschließlich aller doppelten Werte.
USE AdventureWorks2012;
GO
SELECT AVG(ListPrice)
FROM Production.Product;
Dies ist das Resultset.
------------------------------
438.6662
(1 row(s) affected)
E.Verwenden der OVER-Klausel
Im folgenden Beispiel wird die AVG-Funktion mit der OVER-Klausel verwendet, um einen gleitenden Durchschnitt des Jahresumsatzes für jedes Gebiet in der Tabelle Sales.SalesPerson bereitzustellen. Die Daten werden nach TerritoryID partitioniert und logisch nach SalesYTD sortiert. Folglich wird die AVG-Funktion auf Grundlage des Verkaufsjahres für jedes Gebiet berechnet. Beachten Sie, dass für TerritoryID 1 zwei Zeilen für das Verkaufsjahr 2005 vorhanden sind, die die zwei Vertriebsmitarbeiter mit dem Umsatz aus diesem Jahr darstellen. Der durchschnittliche Umsatz für diese zwei Zeilen wird berechnet, und anschließend wird die dritte Zeile, die den Umsatz für das Jahr 2006 darstellt, in die Berechnung einbezogen.
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;
Dies ist das Resultset.
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 diesem Beispiel ist PARTITION BY nicht in der OVER-Klausel enthalten. Folglich wird die Funktion für alle von der Abfrage zurückgegebenen Zeilen angewendet. Die in der OVER-Klausel angegebene ORDER BY-Klausel bestimmt die logische Reihenfolge, für die die AVG-Funktion übernommen wird. Die Abfrage gibt einen gleitenden Durchschnitt der Verkäufe nach Jahr für alle Vertriebsgebiete zurück, die in der WHERE-Klausel angegeben sind. Die in der SELECT-Anweisung angegebene ORDER BY-Klausel bestimmt die Reihenfolge, in der die Zeilen der Abfrage angezeigt werden.
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;
Dies ist das Resultset.
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)