Функция SQL AVG()
Оператор SQL AVG() — Функция возвращающая среднее значение столбца. Применима только для числовых столбцов!
Функция SQL AVG() имеет следующий синтаксис:
AVG(column_name)
Примеры оператора SQL AVG: Имеется следующая таблица Universities :
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1. Используя оператор SQL AVG найти среднее число студентов (Students) всех университетов:
SELECT AVG(Students) FROM Universities
Пример 2. Используя оператор SQL AVG найти среднее число факультетов (Faculties) в университетах Москвы:
SELECT AVG(Faculties) FROM Univetsities WHERE Location = 'Moscow'
AVG SQL
Функция SQL AVG необходимо в случае, если требуется вычислить среднее значение числового столбца в таблице. Среднее значение функция AVG() в SQL вычисляет среднее значение столбца путем суммирования всех значений записей столбца и деления на количество записей.
AVG SQL Примеры
Рассмотрим пример. Допустим в таблице Price есть столбец Price_unit. В этой таблице содержатся 5 записей. Значения полей столбца Price_unit 3, 5, 14, 38 и 83.
Выполним запрос, возвращающий среднее значение столбца Price_unit
SELECT AVG(Price_unit) AS PriceAvg FROM Price;
Результатом выполнения данного запроса будет
AVG (Transact-SQL)
Эта функция возвращает среднее арифметическое группы значений. Значения NULL она не учитывает.
Синтаксис
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 () вычисляет среднее арифметическое набора значений, выполняя деление суммы этих значений на число значений, не равных NULL. Если сумма превышает максимальное значение для типа данных возвращаемого значения, AVG() возвратит ошибку.
AVG — это детерминированная функция, если она используется без предложений OVER и ORDER BY. Она не детерминирована при использовании с предложениями OVER и ORDER BY. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.
Примеры
А. Использование функций 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) SQL Server функция AVG
В SQL Server (Transact-SQL) функция AVG возвращает среднее значение выражения.
Синтаксис
Синтаксис функции AVG в SQL Server (Transact-SQL):
SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];
ИЛИ синтаксис функции AVG, когда результаты группируются одним или несколькими столбцами:
SELECT expression1, expression2, . expression_n,
AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, . expression_n;
Параметры или аргументы
expression1 , expression2 , . expression_n
Выражения, которые не включены в функцию AVG и должны быть включены в оператор GROUP BY в конце SQL предложения.
aggregate_expression - это столбец или выражение, которое будет усреднено.
tables - таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions - необязательный. Это условия, которые должны выполняться для выбранных записей.
Применение
Функция AVG может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Пример с одним полем
Рассмотрим некоторые примеры SQL Server функции AVG, чтобы понять, как использовать функцию AVG в SQL Server (Transact-SQL).
Например, вы можете узнать, как среднее количество всех products , где quantity больше 0. Например: