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 ) ]
Аргументы
ВСЕ
Применяет агрегатную функцию ко всем значениям. 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)