AVG (Transact-SQL)
Gilt für: 
SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric
Diese Funktion gibt den Mittelwert der Werte in einer Gruppe zurück. Sie ignoriert NULL-Werte.
Transact-SQL-Syntaxkonventionen
Syntax
AVG ( [ ALL | DISTINCT ] expression )
[ OVER ( [ partition_by_clause ] order_by_clause ) ]
Argumente
ALL
Wendet die Aggregatfunktion auf alle Werte an. ALL ist die Standardeinstellung.
DISTINCT
Gibt an, dass AVG nur eine eindeutige Instanz eines Werts verwendet, unabhängig davon, wie oft der Wert vorkommt.
expression
Ein Ausdruck der genauen numerischen oder ungefähren numerischen Datentypkategorie, mit Ausnahme des bit-Datentyps. Aggregatfunktionen und Unterabfragen sind nicht zulässig.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause unterteilt das von der FROM-Klausel erzeugte Resultset in Partitionen, auf die die Funktion angewendet wird. Wird dies nicht angegeben, verarbeitet die Funktion alle Zeilen des Abfrageresultsets als einzelne Gruppe. order_by_clause bestimmt die logische Reihenfolge, in der der Vorgang ausgeführt wird. order_by_clause ist erforderlich. Weitere Informationen finden Sie unter OVER-Klausel (Transact-SQL).
Rückgabetypen
Der Rückgabetyp wird durch das ausgewertete Ergebnis von expression bestimmt.
| Ausdrucksergebnis | Rückgabetyp |
|---|---|
| tinyint | int |
| smallint | int |
| int | int |
| bigint | bigint |
| decimal-Kategorie (p, s) | decimal(38, max(s,6)) |
| money- und smallmoney-Kategorie | money |
| float- und real-Kategorie | float |
Hinweise
Wenn der Datentyp von expression ein Aliasdatentyp ist, ist der Rückgabetyp ebenfalls ein Aliasdatentyp. Wenn der Basisdatentyp des Aliasdatentyps höhergestuft wird, z. B. von tinyint int, übernimmt der Rückgabewert den höhergestuften Datentyp und nicht den Aliasdatentyp.
AVG () berechnet den Mittelwert einer Gruppe von Werten, indem die Summe dieser Werte durch die Anzahl von Nicht-Null-Werten dividiert wird. Wenn die Summe den Maximalwert für den Datentyp des Rückgabewerts überschreitet, gibt AVG() einen Fehler zurück.
AVG ist eine deterministische Funktion, wenn sie ohne die OVER- und ORDER BY-Klauseln angegeben wird. Sie ist nicht deterministisch, wenn sie mit den KLAUSELn OVER und ORDER BY angegeben wird. Weitere Informationen finden Sie unter Deterministic and Nondeterministic Functions. Außerdem verhält sich AVG möglicherweise als nichtdeterministische Funktion, wenn Sie sie mit float- und realen Datentypen verwenden. Der zugrunde liegende Grund ist jedoch die ungefähre Art dieser Datentypen.
Beispiele
A. Verwenden der Funktionen SUM und AVG für Berechnungen
In diesem Beispiel werden die durchschnittlichen Urlaubstage und die Summe der Krankheitstage der stellvertretenden Direktoren von Adventure Works Cycles (in Stunden) berechnet. Beide Aggregatfunktionen erzeugen jeweils einen zusammenfassenden Wert aller abgerufenen Zeilen. In dem Beispiel wird die AdventureWorks2022-Datenbank verwendet.
SELECT AVG(VacationHours)AS 'Average vacation hours',
SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';
Hier sehen Sie das Ergebnis.
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 gemeinsamen Werts für die gesamte Tabelle. Im folgenden Beispiel werden für alle Vertriebsgebiete in der AdventureWorks2022-Datenbank Zusammenfassungswerte erzeugt. In der Zusammenfassung werden die durchschnittlichen Bonusleistungen von Vertriebsmitarbeitern der einzelnen Regionen und die Summe der Verkaufszahlen für das laufende Jahr pro Region aufgelistet.
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
Hier sehen Sie das Ergebnis.
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
Mit dieser Anweisung wird der durchschnittliche Listenpreis der Produkte in der AdventureWorks2022-Datenbank zurückgegeben. Mithilfe von DISTINCT berücksichtigt die Berechnung nur eindeutige Werte.
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
Hier sehen Sie das Ergebnis.
------------------------------
437.4042
(1 row(s) affected)
D: Verwenden von AVG ohne DISTINCT
Ohne DISTINCT wird mithilfe der AVG-Funktion der durchschnittliche Listenpreis aller Produkte in der Product-Tabelle der AdventureWorks2022-Datenbank gesucht, einschließlich aller doppelten Werte.
SELECT AVG(ListPrice)
FROM Production.Product;
Hier sehen Sie das Ergebnis.
------------------------------
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 Sales.SalesPerson-Tabelle der AdventureWorks2022-Datenbank 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. Für TerritoryID 1 gibt es zwei Zeilen für das Umsatzjahr 2005, die die beiden Vertriebsmitarbeiter mit dem Umsatz in 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.
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;
Hier sehen Sie das Ergebnis.
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 enthält die OVER-Klausel keine PARTITION BY. Dies bedeutet, dass die Funktion für alle Zeilen gilt, die von der Abfrage zurückgegeben werden. Die in der OVER-Klausel angegebene ORDER BY-Klausel bestimmt die logische Reihenfolge, in der die AVG-Funktion angewendet wird. Die Abfrage gibt einen gleitenden Durchschnitt der Jahresumsätze 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 diese Anweisung die Zeilen der Abfrage anzeigt.
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;
Hier sehen Sie das Ergebnis.
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)