AVG (Transact-SQL)AVG (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Эта функция возвращает среднее арифметическое группы значений.This function returns the average of the values in a group. Значения NULL она не учитывает.It ignores null values.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

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

АргументыArguments

ALLALL
Применяет агрегатную функцию ко всем значениям.Applies the aggregate function to all values. ALL является параметром по умолчанию.ALL is the default.

DISTINCTDISTINCT
Указывает на то, что функция AVG выполняется только для одного уникального экземпляра каждого значения, независимо от того, сколько раз встречается это значение.Specifies that AVG operates only on one unique instance of each value, regardless of how many times that value occurs.

expressionexpression
Выражение категории точного числового или приблизительного числового типа данных, за исключением типа данных bit.An expression of the exact numeric or approximate numeric data type category, except for the bit data type. Агрегатные функции и вложенные запросы не допускаются.Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция.partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы.If not specified, the function treats all rows of the query result set as a single group. order_by_clause определяет логический порядок, в котором выполняется операция.The order_by_clause determines the logical order in which the operation is performed. Аргумент order_by_clause является обязательным.The order_by_clause is required. Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).For more information, see OVER Clause (Transact-SQL).

Типы возвращаемых данныхReturn types

Тип возвращаемого значения определяется типом вычисленного результата выражения.The evaluated result of expression determines the return type.

Результат выраженияExpression result Возвращаемый типReturn type
tinyinttinyint intint
smallintsmallint intint
intint intint
bigintbigint bigintbigint
Категория decimal (p, s)decimal category (p, s) decimal(38, min(s,6))decimal(38, min(s,6))
Категории money и smallmoneymoney and smallmoney category moneymoney
Категории float и realfloat and real category floatfloat

RemarksRemarks

Если тип данных expression является типом данных-псевдонимом, тип возвращаемого значения также является типом данных-псевдонимом.If the data type of expression is an alias data type, the return type is also of the alias data type. Однако если базовый тип данных типа данных-псевдонима может повышаться, например из tinyint в int, возвращаемое значение будет иметь повышенный тип данных, а не тип данных-псевдоним.However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value will take the promoted data type, and not the alias data type.

Функция AVG () вычисляет среднее арифметическое набора значений, выполняя деление суммы этих значений на число значений, не равных NULL.AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. Если сумма превышает максимальное значение для типа данных возвращаемого значения, AVG() возвратит ошибку.If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error.

AVG — это детерминированная функция, если она используется без предложений OVER и ORDER BY.AVG is a deterministic function when used without the OVER and ORDER BY clauses. Она не детерминирована при использовании с предложениями OVER и ORDER BY.It is nondeterministic when specified with the OVER and ORDER BY clauses. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.For more information, see Deterministic and Nondeterministic Functions.

ПримерыExamples

A.A. Использование функций SUM и AVG для вычисленийUsing the SUM and AVG functions for calculations

В этом примере вычисляется среднее количество часов отпуска и сумма часов отсутствия по болезни, которые использовали вице-президенты компании Компания Adventure Works CyclesAdventure Works Cycles.This example calculates the average vacation hours, and the sum of sick leave hours, that the vice presidents of Компания Adventure Works CyclesAdventure Works Cycles have used. Каждая из этих агрегатных функций создает одно сводное значение для всех извлеченных строк.Each of these aggregate functions produces a single summary value for all the retrieved rows. В этом примере используется база данных AdventureWorks2012AdventureWorks2012.The example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT AVG(VacationHours)AS 'Average vacation hours',   
    SUM(SickLeaveHours) AS 'Total sick leave hours'  
FROM HumanResources.Employee  
WHERE JobTitle LIKE 'Vice President%';  

Ниже приводится результирующий набор.Here is the result set.

Average vacation hours       Total sick leave hours
 ----------------------       ----------------------
25                           97
  
(1 row(s) affected)

Б.B. Использование функций SUM и AVG в предложении GROUP BYUsing the SUM and AVG functions with a GROUP BY clause

При использовании с предложением GROUP BY каждая агрегатная функция создает одно значение, охватывающее каждую группу, а не одно значение для всей таблицы.When used with a GROUP BY clause, each aggregate function produces a single value covering each group, instead of a single value covering the whole table. В следующем примере создается итоговое значение для каждой территории сбыта в базе данных AdventureWorks2012AdventureWorks2012.The following example produces summary values for each sales territory in the AdventureWorks2012AdventureWorks2012 database. Итог содержит средний бонус, полученный продавцами по каждой территории, и сумму продаж за текущий год для каждой территории.The summary lists the average bonus received by the sales people in each territory, and the sum of year-to-date sales for each territory.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'  
FROM Sales.SalesPerson  
GROUP BY TerritoryID;  
GO  

Ниже приводится результирующий набор.Here is the result set.

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. Использование функции AVG с ключевым словом DISTINCTUsing AVG with DISTINCT

Эта инструкция возвращает среднюю ориентировочную цену на продукцию из базы данных AdventureWorks2012AdventureWorks2012.This statement returns the average list price of products in the AdventureWorks2012AdventureWorks2012 database. При использовании DISTINCT в расчете учитываются только уникальные значения.Through the use of DISTINCT, the calculation considers only unique values.

SELECT AVG(DISTINCT ListPrice)  
FROM Production.Product;  

Ниже приводится результирующий набор.Here is the result set.

------------------------------
437.4042
  
(1 row(s) affected)

Г.D. Использование функции AVG без ключевого слова DISTINCTUsing AVG without DISTINCT

Без ключевого слова DISTINCT функция AVG находит среднюю ориентировочную цену всех продуктов в таблице Product в базе данных AdventureWorks2012AdventureWorks2012, учитывая и все повторяющиеся значения.Without DISTINCT, the AVG function finds the average list price of all products in the Product table in the AdventureWorks2012AdventureWorks2012 database, including any duplicate values.

SELECT AVG(ListPrice)  
FROM Production.Product;  

Ниже приводится результирующий набор.Here is the result set.

------------------------------
438.6662
  
(1 row(s) affected)

Д.E. Использование предложения OVERUsing the OVER clause

Следующий пример показывает использование функции AVG с предложением OVER для получения скользящего среднего годовых продаж на каждой территории в таблице Sales.SalesPerson в базе данных AdventureWorks2012AdventureWorks2012.The following example uses the AVG function with the OVER clause, to provide a moving average of yearly sales for each territory in the Sales.SalesPerson table in the AdventureWorks2012AdventureWorks2012 database. Данные секционируются по TerritoryID и логически сортируются по SalesYTD.The data is partitioned by TerritoryID and logically ordered by SalesYTD. Это означает, что функция AVG вычисляется для каждой территории на основании объема продаж за год.This means that the AVG function is computed for each territory based on the sales year. Обратите внимание, что в TerritoryID 1 для продаж за 2005 год используются две строки, в которых представлены два менеджера по продажам с показателями за этот год.Note that for TerritoryID 1, there are two rows for sales year 2005, which represent the two sales-people with sales that year. После расчета среднего значения продаж для двух данных строк в вычисление включается третья строка, представляющая продажи за 2006 год.The average sales for these two rows is calculated, and then the third row representing sales for the year 2006 is included in the calculation.

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;  

Ниже приводится результирующий набор.Here is the result set.

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.In this example, the OVER clause does not include PARTITION BY. Это означает, что функция будет применяться для всех строк, возвращаемых запросом.This means that the function will apply to all rows returned by the query. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции AVG.The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function applies. Запрос возвращает скользящее среднее значение продаж за год для всех территорий, указанных в предложении WHERE.The query returns a moving average of sales, by year, for all sales territories specified in the WHERE clause. Предложение ORDER BY, указанное в инструкции SELECT, определяет порядок, в котором эта инструкция отображает строки запроса.The ORDER BY clause specified in the SELECT statement determines the order in which the SELECT statement displays the rows of the 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;  

Ниже приводится результирующий набор.Here is the result set.

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)  

См. также разделSee also

Агрегатные функции (Transact-SQL)Aggregate Functions (Transact-SQL)
Предложение OVER (Transact-SQL)OVER Clause (Transact-SQL)