Занятие 17. Агрегирующие функции. Оператор GROUP BY

📁 Блок: SQL / MySQL / Агрегация ⏱️ Время изучения: ~90 мин 🎯 Сложность: Средняя

#count #distinct #sum #avg #min #max #group_concat #group #by #order

⚡ Кратко: суть темы

Агрегирующие функции обобщают данные, а GROUP BY разбивает их на группы.

COUNT(), SUM(), AVG(), MIN(), MAX() — базовые агрегаты.
GROUP BY группирует строки с одинаковыми значениями.
Все неагрегированные столбцы в SELECT должны быть в GROUP BY.

Частая ошибка: забыть столбец в GROUP BY.

📖 Агрегирующие функции

Агрегирующие функции выполняют вычисления над набором строк и возвращают одно итоговое значение.

Функция	Назначение	Особенности
`COUNT(*)`	Подсчитывает все строки	Не игнорирует `NULL`
`COUNT(column)`	Подсчитывает строки, где `column` не `NULL`	Игнорирует `NULL`
`SUM(column)`	Сумма значений	Только числовые типы
`AVG(column)`	Среднее значение	Игнорирует `NULL`
`MIN(column)`	Минимальное значение	Работает с числами, строками, датами
`MAX(column)`	Максимальное значение	Работает с числами, строками, датами

Примеры

-- Общее количество строк
SELECT COUNT(*) FROM order_details;

-- Сумма количества товаров
SELECT SUM(quantity) FROM order_details;

-- Средняя, минимальная и максимальная цена
SELECT AVG(unit_price), MIN(unit_price), MAX(unit_price)
FROM order_details;

📖 DISTINCT в агрегатах

DISTINCT внутри агрегатной функции учитывает только уникальные значения:

-- Количество уникальных заказов
SELECT COUNT(DISTINCT order_id) FROM order_details;

📖 GROUP_CONCAT (MySQL)

Специальная агрегатная функция MySQL, которая объединяет значения нескольких строк в одну строку с разделителем:

-- Имена сотрудников через запятую
SELECT GROUP_CONCAT(first_name) FROM employees;

-- С разделителем и сортировкой
SELECT GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ' | ')
FROM employees;

Проверить по документации: GROUP_CONCAT — специфичная для MySQL/MariaDB функция. В PostgreSQL используется STRING_AGG, в SQL Server — STRING_AGG, в SQLite — GROUP_CONCAT.

📖 Оператор GROUP BY

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

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Примеры

-- Количество сотрудников в каждом городе
SELECT city, COUNT(id) AS number_employees
FROM employees
GROUP BY city
ORDER BY number_employees DESC;

-- Общее количество товаров в каждом заказе
SELECT order_id, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY order_id
ORDER BY total_quantity DESC;

-- Количество сотрудников в разрезе компании и должности
SELECT company, job_title, COUNT(id) AS number_employees
FROM employees
GROUP BY company, job_title;

📖 Правила GROUP BY

Все столбцы в SELECT, которые не используются в агрегирующих функциях, должны быть перечислены в GROUP BY.
В MySQL можно использовать порядковые номера столбцов в GROUP BY и ORDER BY (например, GROUP BY 1).
Если столбец содержит только уникальные значения, группировка по нему не имеет смысла.

-- Правильно
SELECT city, COUNT(id) AS cnt
FROM employees
GROUP BY city;

-- Краткая запись
SELECT city, COUNT(id) AS cnt
FROM employees
GROUP BY 1
ORDER BY 2 DESC;

✨ Современные практики

Явно указывайте имена столбцов в GROUP BY вместо порядковых номеров — это улучшает читаемость и переносимость.
Для фильтрации групп используйте HAVING, а не WHERE (о нём — в следующем занятии).
Будьте внимательны с NULL: COUNT(*) и COUNT(column) могут давать разные результаты.