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

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

Что такое агрегатные функции SQL

Агрегатная функция выполняет вычисление над набором значений и возвращает одно значение. В табличной модели данных это значит, что функция берет ноль, одну или несколько строк для какой-то колонки и возвращает единственное значение.

Подробнее о языке SQL узнаете на модуле курса «Анализ данных». Вы сможете работать с агрегатными функциями, а еще научитесь составлять простые и сложные запросы, работать с таблицами и базами данных. Станете настоящим экспертом по структурированию информации.

Для сравнения — скалярные функции принимают на вход одно значение и возвращают одно значение. Пример скалярной функции: abs — функция, принимающая число и возвращающая абсолютное значение этого числа.

Свойства агрегатных функций:

• Игнорируют NULL-значения. Исключение — функция COUNT(*). Дальше детально рассмотрим, что это значит.
• Все агрегатные функции детерминированы. Это значит, что для одинакового набора данных функции возвращают одинаковое значение.

Агрегатные функции часто используются с операторами GROUP BY и HAVING. Оператор GROUP BY группирует строки с одинаковыми значениями в одну строку. Оператор HAVING используется в качестве фильтра для запросов, в которых есть оператор GROUP BY.

С агрегатными функциями можно использовать ключевые слова DISTINCT и ALL, которые мы рассмотрим ниже. Синтаксис выглядит так:

 aggregation_function(DISTINCT | ALL expression)

В примерах будем использовать таблицу Employees («Сотрудники»).

Создать ее можно с помощью команды:

CREATE TABLE IF NOT EXISTS employees (
name VARCHAR ( 50 ) NOT NULL,
office_id INT,
salary INT,
role VARCHAR ( 50 ) NOT NULL
);

INSERT INTO employees VALUES
('Ivan', 1, 500, 'SWE'), ('Misha', 2, 750, 'Manager'), ('Olya', 2, 600, 'QA'), ('Kolya', 1, 900, 'SWE'), ('Max', 2, NULL, 'Manager');

Протестировать команды ниже поможет сервис DB Fiddle. Используемый код совместим с программой PostgreSQL v14 — системой управления базами данных.

Функция COUNT

COUNT считает количество строк в таблице. Она может принимать в качестве параметров как числовые, так и нечисловые типы данных.

Об этом рассказывают в модуле по SQL на курсе «Анализ данных». Вы сможете создавать, изменять и удалять данные в таблицах. Научитесь использовать разные комбинации, чтобы решать реальные задачи. Кроме теории вас ждет практическая работа и защита диплома. После обучения сможете работать аналитиком данных, продуктовым или финансовым аналитиком.

COUNT(*) — специальная форма функции COUNT, которая возвращает количество всех строк в указанной таблице. Обратите внимание: COUNT(*) считает дубликат и NULL.

Напишем запрос к таблице Employees, который будет считать количество сотрудников для каждой роли:

SELECT role, count(*) number_of_empoyee FROM employees GROUP BY role;

Посчитаем количество сотрудников уникальных офисов:

SELECT COUNT(DiSTINCT office_id) AS unique_offices FROM employees;

SUM

Функция SUM вычисляет суммы всех выбранных столбцов. Обратите внимание: она работает только с числовыми полями.

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

SELECT SUM(salary) AS total_salary FROM employees;

MAX и MIN

Эти функции нужны для нахождения максимального и минимального значения для определенного столбца.

Например, минимальная зарплата среди всех сотрудников:

SELECT MIN(salary) AS min_salary FROM employees;

Напишем запрос сложнее, который использует оператор HAVING, — максимальная зарплата для каждого офиса, где больше двух сотрудников:

SELECT office_id, MAX(salary) AS max_salary FROM employees GROUP BY office_id HAVING COUNT(*) > 2;

AVG

Функция используется для вычисления среднего значения заданного столбца.
Например, рассчитаем среднюю зарплату по всем сотрудникам:

SELECT AVG(salary) AS avg_salary FROM employees;

Важно: в столбце salary у нас есть значение NULL, но функция AVG проигнорировала его и посчитала среднее значение как (500 + 750 + 600 + 900) / 4 = 687,5

Где применяют агрегирующие функции

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

• строят модели в аналитике;
• строят графики в реальном времени с метриками сервисов;
• пишут отчеты.

Например, с их помощью бизнес-аналитик составляет стратегии развития компании и различные отчеты. Как это делать — узнаете на курсе «Бизнес-аналитик». После обучения сможете прогнозировать показатели бизнеса, отслеживать тенденции его развития и формулировать гипотезы роста.

Для некоторых продуктов критически важна производительность агрегирующих функций (например, для построения аналитики в реальном времени). Поэтому есть базы данных, которые оптимизированы для вычислений агрегатных функций: ClickHouse и Apache Druid. Еще есть хранилище данных, которое оптимизировано под выполнение запросов с агрегатными функциями, — firebolt.

Краткие итоги

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

Разобрали четыре функции:

• COUNT — считает количество строк в таблице;
• SUM — вычисляет суммы выбранных столбцов;
• MAX и MIN — находят максимальное и минимальное значение определенного столбца;
• AVG — вычисляет среднее значение столбца.

Агрегатные функции используют с операторами GROUP BY и HAVING:

• GROUP BY — группирует строки с одинаковыми значениями в одну строку;
• HAVING — используется как фильтр для запросов, в которых есть оператор GROUP BY.