AVG (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲
此函數傳回群組中值的平均值。 它會忽略 Null 值。
語法
AVG ( [ ALL | DISTINCT ] expression )
[ OVER ( [ partition_by_clause ] order_by_clause ) ]
引數
ALL
將彙總函式套用至所有值。 ALL 是預設值。
DISTINCT
指定 AVG 只在每個值的唯一一個執行個體上執行，不論該值出現多少次。
expression
精確數值或近似數值資料型別類別的運算式，但 bit 資料型別除外。 不允許聚合函數和子查詢。
OVER （ [ partition_by_clause ] order_by_clause）
partition_by_clause 會將 FROM 子句產生的結果集分割成函數所要套用的分割區。 如未指定，此函數會將查詢結果集的所有資料列視為單一群組。 order_by_clause 決定執行作業的邏輯順序。 order_by_clause 為必要項目。 如需詳細資訊，請參閱 OVER 子句 (Transact-SQL)。
傳回類型
運算式的評估結果決定傳回型別。
| 運算式結果 | 傳回類型 |
|---|---|
| tinyint | int |
| smallint | int |
| int | int |
| bigint | bigint |
| decimal 類別 (p, s) | decimal(38, max(s,6)) |
| money 和 smallmoney 類別 | money |
| float 和 real 類別 | float |
備註
如果 expression 的資料類型是一個別名資料類型，傳回型別也會是別名資料類型。 不過，如果升級別名數據類型的基底數據類型，例如從 tinyint 升級為 int，則傳回值會採用升級的數據類型，而不是別名數據類型。
AVG （） 將一組值的平均值除以非 Null 值的計數來計算這些值的總和。 如果總和超過傳回值之數據類型的最大值，AVG（） 會傳回錯誤。
AVG 未搭配 OVER 和 ORDER BY 子句使用時，是具決定性函數。 使用 OVER 和 ORDER BY 子句指定時，這是不具決定性的。 如需詳細資訊，請參閱 決定性與非決定性函數。 此外，當您搭配 float 和 real 數據類型使用 AVG 時，AVG 可能會顯示為非決定性函式。 但根本原因是這些數據類型的近似本質。
範例
A. 使用 SUM 與 AVG 函數計算
此範例計算 Adventure Works Cycles 的副總裁所用的平均假期時數及病假時數總和。 這些彙總函式的每一個都會產生所有擷取之資料列的單一摘要值。 此範例使用 AdventureWorks2022 資料庫。
SELECT AVG(VacationHours)AS 'Average vacation hours',
SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';
結果集如下所示。
Average vacation hours Total sick leave hours
---------------------- ----------------------
25 97
(1 row(s) affected)
B. 搭配 GROUP BY 子句使用 SUM 與 AVG 函數
當搭配 GROUP BY 子句使用時，每個彙總函數都會產生一個涵蓋每個群組的單一值，而不是涵蓋整個資料表的單一值。 下列範例會產生 AdventureWorks2022 資料庫中每個銷售領域的摘要值。 摘要會列出每個領域的銷售人員所收到的平均獎金，以及每個領域年初至今的銷售總和。
SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO
結果集如下所示。
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. 搭配 DISTINCT 使用 AVG
此語句會傳回 AdventureWorks2022 資料庫中產品的平均清單價格。 藉由使用 DISTINCT，計算只會考慮唯一的值。
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
結果集如下所示。
------------------------------
437.4042
(1 row(s) affected)
D. 使用不含 DISTINCT 的 AVG
如果沒有 DISTINCT，函 AVG 式會尋找 AdventureWorks2022 資料庫中數據表中所有產品 Product 的平均清單價格，包括任何重複的值。
SELECT AVG(ListPrice)
FROM Production.Product;
結果集如下所示。
------------------------------
438.6662
(1 row(s) affected)
E. 使用 OVER 子句
下列範例會使用AVG函式搭配OVER子句，為AdventureWorks2022資料庫中數據表中的每個 Sales.SalesPerson 地區提供每年銷售額的移動平均。 TerritoryID 負責分割資料，而 SalesYTD 會進行邏輯性地排序。 這表示，將會根據銷售年度來針對每一個領域計算 AVG 函數。 針對 TerritoryID 1,2005 年銷售年度有兩個數據列，代表當年銷售的兩位銷售人員。 系統計算這兩個資料列的平均銷售額，然後將表示 2006 年度銷售額的第三個資料列納入計算。
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;
結果集如下所示。
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)
在此範例中，OVER 子句不包含 PARTITION BY。 這表示函式會套用至查詢傳回的所有數據列。 OVER 子句中指定的 ORDER BY 子句決定套用 AVG 函數的邏輯順序。 此查詢會依照 WHERE 子句中指定的所有銷售領域傳回銷售量移動平均值 (依年度)。 SELECT 陳述式中指定的 ORDER BY 子句決定 SELECT 陳述式顯示所查詢資料列的順序。
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;
結果集如下所示。
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)