Средние значения с AVG() — расчет средних значений по столбцам

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

Функция AVG() — это агрегатная функция, которая вычисляет среднее арифметическое для числового столбца. Она складывает все значения в указанном столбце и делит результат на количество этих значений. Она не обращает внимания на NULL (и этот игнор, как ни странно, упрощает жизнь, но об этом позже).

Синтаксис AVG()

Давайте начнем с базового синтаксиса:

SELECT AVG(столбец)
FROM таблица;

Обратите внимание: здесь столбец — это столбец, содержащий числовые значения, для которых вы хотите найти среднее.

Пример 1: Средняя зарплата сотрудников

Представьте, что у нас есть таблица employees, где хранятся данные о сотрудниках и их зарплатах:

Простой запрос для расчета средней зарплаты:

SELECT AVG(salary) AS average_salary
FROM employees;

Результат:

Как это работает?

• AVG() складывает все значения зарплат: 50000 + 60000 + 55000 + 52000 = 217000.
• Делит сумму на количество ненулевых значений: 217000 / 4 = 54250.

Особенности работы AVG() с NULL

Вы могли заметить, что для расчета средней зарплаты значение NULL в столбце salary было проигнорировано. Это ключевая особенность AVG(). Она учитывает только не-NULL значения.

Попробуем пример:

SELECT AVG(NULL) AS result;

Результат:

Это ещё раз подтверждает, что AVG() игнорирует NULL. Однако, если весь набор данных состоит из NULL, то результат будет NULL.

Но если у нас в таблице будет стоять не NULL, а 0, такой результат проигнорирован не будет.

Таблица employees

SQL-запрос:

SELECT AVG(salary) AS avg_salary
FROM employees;

Результат:

Почему так?

Потому что AVG() посчитает:

[(1000 + 0 + 2000) / 3 = 1000]

Строка с NULL игнорируется при подсчёте среднего значения.

Пример: Расчет среднего возраста студентов

Теперь обратимся к таблице students:

Запрос:

SELECT AVG(age) AS average_age
FROM students;

Результат:

• AVG() игнорирует студентку Maria, так как её возраст указан как NULL.
• Среднее значение рассчитывается как: (20 + 22 + 21) / 3 = 21.

Округление результата

Иногда результат AVG() возвращает дробное значение с несколькими знаками после запятой.

Если вам нужно получить округленное число, можно воспользоваться функцией ROUND().

Таблица employees

SQL-запрос

SELECT ROUND(AVG(salary), 2) AS rounded_average_salary
FROM employees;

Результат

Строка с NULL исключается из расчета, поэтому среднее считается по трём значениям.

Фильтрация данных перед расчетом AVG()

Если нужно рассчитать среднее, но только для значений, соответствующих определённым условиям, используйте WHERE.

Таблица employees

Пример: Найдём среднюю зарплату сотрудников, чей id > 2.

SELECT AVG(salary) AS average_salary
FROM employees
WHERE id > 2;

Результат

В расчёте участвуют только зарплаты с id = 3 и id = 4. Строка с NULL исключается.

Пример: Сложные запросы с AVG()

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

Допустим, у нас есть таблица продаж sales:

Запрос для расчета средней общей суммы продаж:

SELECT AVG(quantity * price) AS average_total_sale
FROM sales;

Результат:

Жизненные лайфхаки и типичные ошибки

Работа с AVG() должна быть аккуратной, чтобы избежать распространённых ошибок:

NULL-значения: иногда удивляет, почему результат неожиданно меньше ожидаемого. Помните, что AVG() пропускает строки с NULL.

Смешение типов данных: если в столбце смешаны числа и текст (что само по себе плохая практика), AVG() вызовет ошибку.

2

Задача

SQL SELF, 7 уровень, 3 лекция

Недоступна

Простой расчет среднего возраста студентов

Простой расчет среднего возраста студентов

Открыть

2

Задача

SQL SELF, 7 уровень, 3 лекция

Недоступна

Расчет средней зарплаты сотрудников с условием

Расчет средней зарплаты сотрудников с условием

Открыть