Агрегатные функции и GROUP BY

Считаем итоги: агрегатные функции

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

Функция	Что считает
COUNT(*)	количество строк
SUM(x)	сумму значений столбца x
AVG(x)	среднее
MIN(x) / MAX(x)	минимум / максимум

SELECT COUNT(*) AS cnt
FROM airplanes;            -- сколько всего самолётов → 10

Можно посчитать сразу несколько итогов в одном запросе, и каждому удобно дать псевдоним через AS:

SELECT AVG(speed) AS avg_speed,
       MIN(speed) AS min_speed,
       MAX(speed) AS max_speed
FROM airplanes;

Дробное среднее удобно округлять функцией ROUND(значение, знаков): ROUND(AVG(speed), 1) — до одного знака после запятой.

⚠️ Агрегаты и NULL: COUNT(*) считает все строки, а COUNT(x), SUM(x), AVG(x), MIN(x) и MAX(x) пропускают строки, где x IS NULL. Поэтому COUNT(*) и COUNT(x) могут вернуть разные числа.

GROUP BY — итоги по группам

Самое интересное начинается, когда нужно посчитать итог отдельно для каждой группы: сколько мест каждого класса, сколько рейсов в каждом статусе, сколько аэропортов в каждой стране. Здесь помогает GROUP BY: он разбивает строки на группы по значению столбца, и агрегат считается внутри каждой группы.

SELECT fare_conditions, COUNT(*) AS cnt
FROM seats
GROUP BY fare_conditions;

Этот запрос вернёт по одной строке на каждый класс обслуживания и количество мест в нём.

Разбор: как база выполняет GROUP BY

Возьмём запрос SELECT fare_conditions, COUNT(*) AS cnt FROM seats GROUP BY fare_conditions и пройдём его по шагам:

1. FROM seats — база берёт все места всех самолётов.
2. GROUP BY fare_conditions — раскладывает их на «кучки»: Economy, Comfort, Business.
3. COUNT(*) — в каждой кучке считает строки.
4. SELECT — для каждой кучки выводит одну строку: название класса и число мест.

Отсюда и золотое правило: после группировки у каждой кучки есть одно общее значение fare_conditions, но много разных seat_no. Поэтому seat_no можно вывести только внутри агрегата — например, MIN(seat_no).

Типичные ошибки

• Столбец не в GROUP BY. SELECT model, COUNT(*) FROM airplanes — ошибка: непонятно, какую модель показать рядом с общим числом.
• Агрегат в WHERE. WHERE COUNT(*) > 5 — ошибка: WHERE работает до подсчёта. Для условий на агрегаты есть HAVING — о нём следующий урок.
• COUNT(*) и COUNT(столбец). Первый считает все строки, второй — только строки, где столбец заполнен. На столбцах с пустыми значениями они дают разные числа.

⚠️ Золотое правило GROUP BY: каждый столбец в SELECT, который НЕ обёрнут в агрегатную функцию, обязан присутствовать в GROUP BY. Иначе база не поймёт, какое из значений группы показать, и выдаст ошибку.

Результат группировки тоже можно сортировать. Частый приём — «от самой большой группы к маленькой»: ORDER BY cnt DESC (можно сортировать прямо по псевдониму агрегата).