AVG (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

Эта функция возвращает среднее арифметическое группы значений. Значения NULL она не учитывает.

Соглашения о синтаксисе Transact-SQL

Синтаксис

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

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

ВСЕ

Применяет агрегатную функцию ко всем значениям. ALL является параметром по умолчанию.

DISTINCT

Указывает на то, что функция AVG выполняется только для одного уникального экземпляра каждого значения, независимо от того, сколько раз встречается это значение.

выражение

Выражение категории точного числового или приблизительного числового типа данных, за исключением типа данных 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 () вычисляет среднее значение набора значений путем деления суммы этих значений на число непустых значений. Если сумма превышает максимальное значение для типа данных возвращаемого значения, AVG() возвращает ошибку.

AVG — это детерминированная функция, если она используется без предложений OVER и ORDER BY. Он недетерминирован при указании предложений OVER и ORDER BY. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions. Кроме того, AVG может вести себя как недетерминированная функция при использовании с плавающей и реальной типы данных. Но основная причина — это приблизительная природа этих типов данных.

Примеры

А. Использование функций 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. Использование функций SUM и AVG в предложении GROUP BY

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

Эта инструкция возвращает среднюю цену на продукты в базе данных AdventureWorks2022. При использовании DISTINCT вычисление учитывает только уникальные значения.

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Результирующий набор:

------------------------------
437.4042

(1 row(s) affected)

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

Без DISTINCT AVG функция находит среднюю цену списка всех продуктов в таблице в Product базе данных AdventureWorks2022, включая все повторяющиеся значения.

SELECT AVG(ListPrice)
FROM Production.Product;

Результирующий набор:

------------------------------
438.6662

(1 row(s) affected)

Е. Использование предложения OVER

В следующем примере функция AVG используется с предложением OVER для предоставления скользящей средней ежегодной продажи для каждой территории в таблице базы Sales.SalesPerson данных AdventureWorks2022. Данные секционируются по 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. Это означает, что функция применяется ко всем строкам, возвращаемым запросом. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции AVG. Запрос возвращает скользящее среднее значение продаж за год для всех территорий, указанных в предложении WHERE. Предложение 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)
• SELECT — предложение OVER (Transact-SQL)