Агрегатные функции SQL (SUM, MIN, MAX, AVG, COUNT)

Средний 7 мин чтения
#sql#databases

Введение

Агрегатные функции в SQL — это мощный инструмент для анализа данных, хранящихся в реляционных базах данных. Они позволяют выполнять вычисления над набором значений и возвращать одно сводное значение. Эти функции являются неотъемлемой частью языка SQL и широко используются для создания отчетов, анализа данных и принятия бизнес-решений. Без них для получения сводной информации, такой как общая сумма продаж, средний возраст сотрудников или количество товаров на складе, потребовались бы сложные и неэффективные запросы или дополнительная обработка данных на стороне приложения.

Основное преимущество агрегатных функций заключается в их способности обрабатывать большие объемы данных непосредственно на сервере базы данных, что значительно сокращает объем передаваемой информации и ускоряет получение результатов. В этой статье мы подробно рассмотрим пять основных агрегатных функций: SUM, MIN, MAX, AVG и COUNT, разберем их синтаксис, принципы работы и приведем практические примеры использования.

Основные концепции

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

SUM() - Сумма значений

Функция SUM() вычисляет сумму всех значений в указанном столбце. Она может применяться только к числовым типам данных.

Синтаксис:

SELECT SUM(имя_столбца) FROM имя_таблицы;

MIN() - Минимальное значение

Функция MIN() возвращает минимальное значение в указанном столбце. Она может использоваться с числовыми, строковыми и временными типами данных.

Синтаксис:

SELECT MIN(имя_столбца) FROM имя_таблицы;

MAX() - Максимальное значение

Функция MAX() возвращает максимальное значение в указанном столбце. Как и MIN(), она применима к числовым, строковым и временным типам данных.

Синтаксис:

SELECT MAX(имя_столбца) FROM имя_таблицы;

AVG() - Среднее значение

Функция AVG() вычисляет среднее арифметическое всех значений в указанном столбце. Применяется только к числовым типам данных.

Синтаксис:

SELECT AVG(имя_столбца) FROM имя_таблицы;

COUNT() - Количество строк

Функция COUNT() подсчитывает количество строк. Существует несколько вариантов ее использования:

  • COUNT(*): подсчитывает общее количество строк в таблице или группе.
  • COUNT(имя_столбца): подсчитывает количество строк, в которых указанный столбец имеет не-NULL значения.
  • COUNT(DISTINCT имя_столбца): подсчитывает количество уникальных не-NULL значений в столбце.

Синтаксис:

SELECT COUNT(*) FROM имя_таблицы;
SELECT COUNT(имя_столбца) FROM имя_таблицы;
SELECT COUNT(DISTINCT имя_столбца) FROM имя_таблицы;

Практические примеры

Для демонстрации работы агрегатных функций создадим таблицу Employees (Сотрудники) и Departments (Отделы).

Таблица Departments

DepartmentIDDepartmentName
1IT
2HR
3Sales

Таблица Employees

EmployeeIDFirstNameLastNameDepartmentIDSalaryHireDate
1ИванИванов11200002022-01-15
2ПетрПетров11500002021-03-20
3АннаСидорова3950002023-05-10
4ЕленаСмирнова31100002022-08-01
5ДмитрийКузнецов2800002023-02-25
6СергейПопов3NULL2023-09-01

Пример 1: Общая статистика по зарплатам

Предположим, нам нужно получить общую информацию по зарплатам всех сотрудников: общую сумму, минимальную, максимальную и среднюю зарплату, а также количество сотрудников, у которых указана зарплата.

SELECT
    SUM(Salary) AS TotalSalary,
    MIN(Salary) AS MinSalary,
    MAX(Salary) AS MaxSalary,
    AVG(Salary) AS AverageSalary,
    COUNT(Salary) AS NumberOfEmployeesWithSalary
FROM Employees;

Результат:

TotalSalaryMinSalaryMaxSalaryAverageSalaryNumberOfEmployeesWithSalary
55500080000150000111000.00005

Обратите внимание, что COUNT(Salary) вернул 5, так как у одного сотрудника (Сергей Попов) зарплата не указана (NULL). Если бы мы использовали COUNT(*), результат был бы 6.

Пример 2: Статистика по отделам

Теперь усложним задачу и посчитаем статистику по каждому отделу отдельно. Для этого нам понадобится предложение GROUP BY и объединение таблиц Employees и Departments.

SELECT
    d.DepartmentName,
    COUNT(e.EmployeeID) AS NumberOfEmployees,
    AVG(e.Salary) AS AverageSalary
FROM Employees AS e
JOIN Departments AS d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
ORDER BY AverageSalary DESC;

Результат:

DepartmentNameNumberOfEmployeesAverageSalary
IT2135000.0000
Sales3102500.0000
HR180000.0000

Этот запрос показывает количество сотрудников и среднюю зарплату в каждом отделе, отсортированные по убыванию средней зарплаты. Мы видим, что IT-отдел имеет самую высокую среднюю зарплату.

Пример 3: Подсчет уникальных значений

Допустим, мы хотим узнать, сколько уникальных отделов имеют сотрудников с зарплатой выше 100,000.

SELECT COUNT(DISTINCT DepartmentID)
FROM Employees
WHERE Salary > 100000;

Результат: 2

В данном случае, это сотрудники из отделов IT (ID 1) и Sales (ID 3). Хотя таких сотрудников трое, уникальных отделов всего два.

Типичные ошибки и как их избежать

  1. Неправильное использование COUNT(): COUNT(*) и COUNT(имя_столбца) могут давать разные результаты из-за NULL значений. Всегда четко понимайте, что именно вы хотите посчитать: все строки или только строки с не-NULL значениями в определенном столбце.

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

    • Неправильно: SELECT DepartmentID, COUNT(*) FROM Employees;
    • Правильно: SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID;

  3. Фильтрация с помощью WHERE вместо HAVING: Предложение WHERE фильтрует строки до их группировки и агрегации. Если нужно отфильтровать результат после агрегации (например, показать только отделы с более чем 2 сотрудниками), следует использовать HAVING.

    • Неправильно: SELECT DepartmentName, COUNT(*) FROM ... GROUP BY DepartmentName WHERE COUNT(*) > 2;
    • Правильно: SELECT DepartmentName, COUNT(*) FROM ... GROUP BY DepartmentName HAVING COUNT(*) > 2;

Связь с другими темами

Агрегатные функции тесно связаны с другими важными концепциями SQL:

  • GROUP BY: Как мы видели, это предложение является ключевым для применения агрегатных функций к подгруппам данных.

  • HAVING: Позволяет фильтровать результаты, полученные с помощью агрегатных функций.

  • Оконные функции (Window Functions): Это более продвинутая концепция, которая также выполняет вычисления над набором строк, но, в отличие от агрегатных функций, не “схлопывает” строки в одну, а возвращает значение для каждой строки. Например, можно посчитать “скользящее среднее”.

  • Подзапросы (Subqueries): Агрегатные функции часто используются в подзапросах для получения промежуточных значений, которые затем используются в основном запросе.

Заключение

Агрегатные функции SUM, MIN, MAX, AVG и COUNT являются фундаментальными строительными блоками для анализа данных в SQL. Они предоставляют простой и эффективный способ извлечения сводной информации из больших наборов данных. Понимание их работы, синтаксиса и правильного использования в сочетании с GROUP BY и HAVING является критически важным навыком для любого специалиста, работающего с базами данных. Освоив эти функции, вы сможете писать более сложные и осмысленные запросы, превращая сырые данные в ценную информацию. Дальнейшее изучение оконных функций может стать следующим шагом в углублении ваших знаний в области анализа данных с помощью SQL.