SQL — это язык, который работает с базами данных: помогает получать из них нужную информацию, добавлять новые данные или изменять и удалять их. С помощью агрегатных функций мы обобщаем информацию, вычисляем описательную статистику и используем продвинутые методы, которые дают возможность лучше обрабатывать и понимать аналитические данные. В статье расскажем, зачем нужны агрегатные функции, обсудим основные понятия и возможности, рассмотрим примеры.
Что такое агрегатные функции SQL
Агрегатные функции — это функции, которые берут ноль, одну или несколько строк для какой-то колонки и возвращают единственное значение.
Для сравнения — скалярные функции принимают на вход одно значение и возвращают одно значение. Пример скалярной функции: abs — функция, которая берет число и отдает абсолютное значение этого числа.
Свойства агрегатных функций:
- Игнорируют NULL-значения. Исключение — функция COUNT(*). Дальше детально рассмотрим, что это значит.
- Все агрегатные функции детерминированы. Это значит, что для одинакового набора данных функции возвращают одинаковое значение.
Подробнее о языке SQL узнаете на курсе от Skypro «Анализ данных». Вы узнаете, что такое агрегатные функции, научитесь составлять простые и сложные запросы и работать с таблицами и базами данных.
Агрегатные функции часто используют с операторами GROUP BY и HAVING. Оператор GROUP BY группирует строки с одинаковыми значениями в одну строку. Оператор HAVING применяют в качестве фильтра для запросов, в которых есть оператор GROUP BY.
С агрегатными функциями можно задействовать ключевые слова DISTINCT и ALL, которые мы рассмотрим ниже. Синтаксис выглядит так:
aggregation_function(DISTINCT | ALL expression)
В примерах будем использовать таблицу Employees («Сотрудники»):
name | office_id | salary | role |
Ivan | 1 | 500 | SWE |
Misha | 2 | 750 | Manager |
Olya | 2 | 600 | QA |
Kolya | 1 | 900 | SWE |
Max | 2 | NULL | Manager |
Создать ее можно с помощью команды:
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 v17.5 — системой, которая управляет базами данных.
Функция COUNT
COUNT считает количество строк в таблице. Она может принимать в качестве параметров как числовые, так и нечисловые типы данных.
Об этом рассказывают в модуле по SQL на курсе от Skypro «Анализ данных». Вы сможете создавать, изменять и удалять данные в таблицах. Научитесь использовать разные комбинации, чтобы решать реальные задачи. Кроме теории, вас ждет практическая работа и защита диплома. После учебы сможете работать аналитиком данных, продуктовым или финансовым аналитиком.
COUNT(*) — специальная форма функции COUNT, которая возвращает количество всех строк в указанной таблице. Обратите внимание: COUNT(*) считает дубликат и NULL.
Напишем запрос к таблице Employees, который будет считать количество сотрудников для каждой роли:
SELECT role, count(*) number_of_empoyee FROM employees GROUP BY role;
Посчитаем количество сотрудников уникальных офисов:
role | number_of _employee |
QA | 1 |
Manager | 2 |
SWE | 2 |
SELECT COUNT(DiSTINCT office_id) AS unique_offices FROM employees;
unique_offices |
2 |
SUM
Функция SUM вычисляет суммы всех выбранных столбцов. Обратите внимание: она работает только с числовыми полями.
Посчитать суммарную зарплату для всех сотрудников можно так:
SELECT SUM(salary) AS total_salary FROM employees;
total_salary |
2750 |
MAX и MIN
Эти функции нужны, чтобы быстро найти максимальное и минимальное значение для определенного столбца.
Например, минимальная зарплата среди всех сотрудников:
SELECT MIN(salary) AS min_salary FROM employees;
min_salary |
500 |
Напишем более сложный запрос, который использует оператор HAVING, — максимальная зарплата для каждого офиса, где больше двух сотрудников:
SELECT office_id, MAX(salary) AS max_salary FROM employees GROUP BY office_id HAVING COUNT(*) > 2;
office_id | max_salary |
2 | 750 |
AVG
Функция нужна, чтобы вычислить среднее значение заданного столбца.
Например, рассчитаем среднюю зарплату по всем сотрудникам:
SELECT AVG(salary) AS avg_salary FROM employees;
avg_salary |
687.500000000000 |
Важно: в столбце salary у нас есть значение NULL, но функция AVG проигнорировала его и посчитала среднее значение так: (500 + 750 + 600 + 900) / 4 = 687,5.
Комбинирование функций
В одном запросе можно использовать сразу несколько функций. Вот как это работает на примере таблицы Sales с колонками product_id — идентификатор товара, quantity — количество продаж, price — цена каждого товара.
product_id | quantity | price |
1 | 10 | 5 |
2 | 5 | 10 |
1 | 15 | 5 |
3 | 8 | 7.5 |
2 | 12 | 10 |
В таблице product_id повторяются, потому что один и тот же продукт может быть реализован несколько раз, и каждая продажа записывается отдельной строкой. Так получается отследить каждую транзакцию по отдельности и точнее анализировать продажи.
Предположим, мы хотим узнать общий доход от продаж каждого продукта и средний доход на единицу товара. Для этого используем комбинацию агрегатных функций SUM() и AVG().
SELECT product_id, SUM(quantity * price) AS total_revenue, AVG(quantity * price) AS average_revenue_per_unit
FROM sales
GROUP BY product_id;
product_id | total_revenue | average_revenue_per_unit |
1 | 125 | 8.33 |
2 | 170 | 17 |
3 | 60 | 7.5 |
Где применяют агрегирующие функции
Их активно используют в различных проектах и продуктах. Например, с помощью функций:
- строят модели в аналитике;
- строят графики в реальном времени с метриками сервисов;
- пишут отчеты.
Бизнес-аналитик может составить стратегию развития компании и различные отчеты. Как это делать — узнаете на курсе от Skypro «Бизнес-аналитик». Сможете прогнозировать показатели бизнеса, отслеживать тенденции его развития и формулировать гипотезы роста.
Для некоторых продуктов критически важна производительность агрегирующих функций. Например, когда надо построить аналитику в реальном времени. Поэтому есть базы данных, которые оптимизированы для вычислений агрегатных функций: ClickHouse и Apache Druid. Еще есть хранилище данных, которое оптимизировано под запросы с агрегатными функциями, — Firebolt.
Краткие итоги
- Агрегирующие функции — важная часть языка SQL, достаточно простой и понятный механизм, который стоит освоить. Суть функции в том, что она вычисляет набор значений и возвращает одно.
- COUNT считает количество строк в таблице, SUM вычисляет суммы выбранных столбцов, MAX и MIN находят максимальное и минимальное значение определенного столбца, AVG вычисляет среднее значение столбца.
- Агрегирующие функции можно комбинировать друг с другом и c другими функциями SQL.
- Агрегатные функции используют с операторами GROUP BY и HAVING. GROUP BY группирует строки с одинаковыми значениями в одну строку, а HAVING применяют как фильтр для запросов, в которых есть оператор GROUP BY.
Добавить комментарий