AVG (Transact-SQL)
Возвращает среднее арифметическое группы значений. Значения NULL не учитываются. Может следовать за предложением OVER.
.gif "Значок ссылки на раздел")
Синтаксические обозначения в Transact-SQL
Синтаксис
AVG ( [ ALL | DISTINCT ] expression )
Аргументы
ALL
Применяет агрегатную функцию ко всем значениям. По умолчанию задается параметр ALL.DISTINCT
Указывает на то, что функция AVG будет выполнена только для одного экземпляра каждого уникального значения, независимо от того, сколько раз встречается это значение.expression
Выражение, принадлежащее к категории точных или приблизительных числовых типов данных, за исключением типа данных bit. Агрегатные функции и вложенные запросы не допускаются.
Типы возвращаемых данных
Возвращаемый тип определяется типом вычисленного результата expression.
Результат выражения |
Тип возвращаемых данных |
|---|---|
tinyint |
int |
smallint |
int |
int |
int |
bigint |
bigint |
категория decimal (p, s) |
decimal(38, s) делится на decimal(10, 0) |
Категория money и smallmoney |
money |
категория float и real. |
float |
Замечания
Если тип данных expression является типом данных-псевдонимом, возвращаемый тип также является типом данных-псевдонимом. Однако если базовый тип данных типа данных-псевдонима может повышаться, например из tinyint в int, возвращаемое значение будет иметь повышенный тип данных, а не тип данных псевдонима.
Функция AVG () вычисляет среднее арифметическое набора значений, выполняя деление суммы этих значений на число значений, не равных NULL. Если сумма превышает максимальное значение для типа данных возвращаемого значения, будет возвращена ошибка.
Примеры
А.Использование функций SUM и AVG для вычислений
В следующем примере вычисляется среднее количество часов отпуска и сумма часов больничных, которые использовал вице-президент компании Компания Adventure Works Cycles. Каждая из этих агрегатных функций создает одно итоговое значение для всех извлеченных строк.
USE AdventureWorks2012;
GO
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)
Б.Использование функций SUM и AVG в предложении GROUP BY
При использовании с предложением GROUP BY каждая агрегатная функция создает одно значение для каждой группы вместо всей таблицы. В следующем примере создается итоговое значение для каждой территории продаж. Итог содержит средний бонус, полученный продавцами по каждой территории и сумму продаж за текущий год для каждой территории.
USE AdventureWorks2012;
GO
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)
В.Использование функции AVG с ключевым словом DISTINCT
Следующая инструкция возвращает среднюю справочную цену на продукцию. При указании DISTINCT в расчете учитываются только уникальные значения.
USE AdventureWorks2012;
GO
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;
Ниже приводится результирующий набор.
------------------------------
437.4042
(1 row(s) affected)
Г.Использование функции AVG без ключевого слова DISTINCT
Без ключевого слова DISTINCT функция AVG находит среднюю справочную цену всех продуктов в таблице Product, учитывая и все повторяющиеся значения.
USE AdventureWorks2012;
GO
SELECT AVG(ListPrice)
FROM Production.Product;
Ниже приводится результирующий набор.
------------------------------
438.6662
(1 row(s) affected)
Д.Использование предложения OVER
Следующий пример показывает использование функции AVG с предложением OVER для получения скользящего среднего годовых продаж на каждой территории в таблице Sales.SalesPerson. Данные секционируются по TerritoryID и логически сортируются по SalesYTD. Это означает, что функция AVG вычисляется для каждой территории на основании объема продаж за год. Обратите внимание, что в TerritoryID 1 для продаж за 2005 год используются две строки, в которых представлены два менеджера по продажам с показателями за этот год. После расчета среднего значения продаж для двух данных строк в вычисление включается третья строка, представляющая продажи за 2006 год.
USE AdventureWorks2012;
GO
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)