AVG (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス

この関数はグループ内の値の平均を返します。 null 値は無視されます。

Transact-SQL 構文表記規則

構文

AVG ( [ ALL | DISTINCT ] expression )  
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

ALL
すべての値にこの集計関数を適用します。 ALL が既定値です。

DISTINCT
値の出現回数にかかわらず、各値の一意の 1 つのインスタンスだけに AVG を適用することを指定します。

式 (expression)
bit データ型を除く、真数または概数データ型カテゴリのです。 集計関数とサブクエリは使用できません。

OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause は、FROM 句で生成された結果セットをパーティションに分割します。このパーティションに関数が適用されます。 指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのグループとして扱います。 order_by_clause 操作が実行される論理的順序を決定します。 order_by_clause が必要です。 詳細については、OVER 句 (Transact-SQL) に関する記事を参照してください。

戻り値の型

expression の評価結果によって、戻り値の型が決まります。

式の結果 の戻り値の型 :
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 句と共に使用される場合は、非決定的関数です。 詳細については、「 決定的関数と非決定的関数」を参照してください。

A. SUM 関数と AVG 関数を使用して計算する

次の例では、Adventure Works Cycles の副社長が利用した休暇時間の平均および病気休暇時間の合計が計算されます。 これらの集計関数はそれぞれ、取得されたすべての行の集計値を 1 つ返します。 この例では、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 句と共に使用した場合、テーブル全体をカバーする 1 つの値ではなく、各グループをカバーする 1 つの値が返されます。 次の例では、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 年の売上については、その年の 2 人の営業担当者の売上を表す 2 行があります。 これら 2 行の平均売上が計算された後、2006 年の売上を表す 3 番目の行が計算に組み込まれます。

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)  

関連項目

集計関数 (Transact-SQL)
OVER 句 (Transact-SQL)