Как посчитать среднее значение в sql

SQL: Среднее значение

SQL (Structured Query Language) – это язык программирования для управления реляционными базами данных. Он позволяет манипулировать данными, извлекать, изменять и удалять их. Одним из наиболее распространенных запросов к БД является вычисление среднего значения.

Чтобы вычислить среднее значение, необходимо воспользоваться функцией AVG. Данная функция принимает аргумент – столбец, для которого нужно вычислить среднее значение.

Пример среднего значения в таблице с полями id, name и salary:

sql SELECT AVG(salary) as avg_salary FROM employees; 

В результате выполнения данного запроса будет возвращено среднее значение выражения за все строки в таблице employees.

sql +-------------+ | avg_salary | +-------------+ | 5000.00 | +-------------+ 

Однако, если нужно вычислить среднее значение только для определенных строк, нужно использовать часть WHERE.

Пример среднего значения зарплаты для сотрудников только в отделе продаж:

sql SELECT AVG(salary) as avg_salary FROM employees WHERE department = 'sales'; 

В результате выполнения данного запроса будет возвращено среднее значение зарплаты для всех сотрудников, работающих в отделе продаж.

sql +-------------+ | avg_salary | +-------------+ | 6000.00 | +-------------+ 

В некоторых случаях, может быть необходимо использовать группировку для вычисления среднего значения. Если в таблице есть поля, которые определяют группы, вычисление среднего значения может быть выполнено для каждой группы отдельно. Для этого используется функция GROUP BY, которая позволяет сгруппировать строки по значению указанного столбца.

Пример группировки и среднего значения зарплаты для каждого отдела:

sql SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department; 

В результате выполнения данного запроса будет возвращено среднее значение зарплаты для каждого отдела.

sql +-------------+-------------+ | department | avg_salary | +-------------+-------------+ | sales | 6000.00 | | marketing | 4500.00 | | accounting | 5000.00 | +-------------+-------------+ 

Таким образом, вычисление среднего значения в SQL может быть выполнено простым запросом с использованием функции AVG. При необходимости можно использовать фильтры и группировки для ограничения вычислений.

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) 

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;

Результатом выполнения данного запроса будет

Как посчитать среднее в SQL: простые шаги для вычисления среднего значения

Для расчета среднего значения в SQL можно использовать функцию AVG(). Эта функция вычисляет среднее арифметическое значения столбца или выражения.

SELECT AVG(column_name) FROM table_name; 

В примере выше, замените column_name на название столбца, для которого вы хотите вычислить среднее значение, и table_name на название таблицы, в которой находится этот столбец. Например, если у вас есть таблица "grades" с столбцом "score", и вы хотите найти средний балл, вы можете использовать следующий запрос:

SELECT AVG(score) FROM grades; 

Этот запрос вернет среднее значение всех значений в столбце "score" таблицы "grades".

Детальный ответ

Привет! Сегодня я помогу тебе разобраться, как посчитать среднее значение в SQL. Это основной запрос, который возникает при работе с данными, поэтому важно понимать, как это делается. Давай начнем!

Что такое среднее значение?

Среднее значение, также известное как среднее арифметическое, является статистической величиной, которая представляет собой сумму всех значений, поделенную на количество значений. Например, если у тебя есть набор значений [2, 4, 6, 8], то среднее значение будет равно (2+4+6+8)/4 = 5.

Как посчитать среднее значение в SQL?

В SQL среднее значение можно рассчитать с помощью функции AVG(). Эта функция принимает столбец или выражение в качестве аргумента и возвращает среднее значение из этого столбца. Вот пример, который показывает, как использовать функцию AVG() для расчета среднего значения в таблице "students":

SELECT AVG(score) AS average_score FROM students;

В этом примере мы выбираем столбец "score" из таблицы "students" и используем функцию AVG() для расчета среднего значения. Результатом будет столбец "average_score" с вычисленным значением.

Группировка для расчета среднего значения

Иногда необходимо рассчитать среднее значение для каждой группы данных. Например, у тебя может быть таблица с информацией о студентах, в которой каждый студент связан с определенным курсом. Ты можешь рассчитать средний балл для каждого курса, используя функцию AVG() совместно с оператором GROUP BY. Рассмотрим пример, где у нас есть таблица "students" с двумя столбцами: "course" (курс) и "score" (балл). Мы хотим рассчитать средний балл для каждого курса:

SELECT course, AVG(score) AS average_score FROM students GROUP BY course;

В этом запросе мы выбираем столбец "course" и используем функцию AVG() для расчета среднего значения столбца "score". Ключевое слово GROUP BY группирует данные по столбцу "course", и для каждой группы мы получаем соответствующее среднее значение в столбце "average_score".

Отбор данных для расчета среднего значения

Иногда ты можешь захотеть рассчитать среднее значение только для определенных строк данных. Например, ты можешь хотеть рассчитать средний балл только для студентов, у которых балл выше определенного порога. Для этого ты можешь использовать оператор WHERE в сочетании с функцией AVG(). Вот пример, где мы рассчитываем средний балл только для студентов с баллом выше 80:

SELECT AVG(score) AS average_score FROM students WHERE score > 80;

В этом примере мы используем оператор WHERE для фильтрации данных и выбираем только те строки, где значение столбца "score" больше 80. Затем мы используем функцию AVG() для расчета среднего значения для этих отфильтрованных строк.

Заключение

Теперь ты знаешь, как посчитать среднее значение в SQL. Не забудь использовать функцию AVG() для расчета среднего значения столбца и при необходимости группировать данные с помощью оператора GROUP BY. Ты также можешь отбирать данные с помощью оператора WHERE. Удачи в твоих SQL запросах!