Агрегация: GROUP BY, HAVING, GROUPING SETS

[Egor_DBA]

Урок 9. Агрегация: GROUP BY, HAVING, GROUPING SETS

Любая аналитика начинается с вопроса "сколько" и "в среднем по чему". Сколько билетов продали, какая выручка по направлениям, сколько мест занято на рейсе. На этот класс задач в postgresql отвечает агрегация: множество строк сворачивается в одно итоговое значение по группам. В этом уроке разберём агрегатные функции (count, sum, avg, min, max, string_agg, array_agg), научимся правильно группировать через GROUP BY, поймём разницу между HAVING и WHERE, освоим точечную фильтрацию внутри агрегата через FILTER и многоуровневые итоги через GROUPING SETS, ROLLUP и CUBE. Считать будем пассажиропоток и выручку на демобазе "Авиаперевозки".



Как это работает

Агрегатная функция получает на вход не одну строку, а целый набор строк и возвращает по нему одно число или одно значение. Без GROUP BY весь результат запроса - это одна большая группа, поэтому count(*) по таблице вернёт ровно одну строку с общим количеством. Как только мы добавляем GROUP BY, сервер раскладывает строки по корзинам с одинаковыми значениями группирующих столбцов и вызывает агрегат отдельно для каждой корзины.

Отсюда главное правило: в списке SELECT может стоять либо столбец, по которому идёт группировка, либо агрегатная функция от других столбцов. Просто так упомянуть негруппируемый столбец нельзя - для группы это не одно значение, а целый список, и сервер не знает, какое из них показать. Исключение - функциональная зависимость от первичного ключа: если группируешь по id, можно тянуть и остальные поля той же таблицы.

Очень важно различать порядок обработки. WHERE отсекает строки ДО группировки, работает с исходными данными и не видит агрегатов. HAVING отсекает уже готовые группы ПОСЛЕ агрегации и умеет ссылаться на sum, count и прочие итоги. Поэтому "только города с выручкой выше миллиона" - это HAVING, а "только за сентябрь" - это WHERE. Логический порядок такой: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Внутри агрегата можно отфильтровать вход, не трогая остальной запрос, - для этого служит конструкция FILTER (WHERE ...). Это чище и читабельнее старого трюка с count в CASE, и часто быстрее. А GROUPING SETS, ROLLUP и CUBE позволяют за один проход посчитать итоги сразу по нескольким разрезам и получить подытоги и общий итог - то, ради чего раньше городили UNION ALL из нескольких запросов.

SQL и примеры

Базовый подсчёт без группировки - сколько всего билетов и бронирований:

Код: Выделить всё

SELECT count(*) AS tickets, count(DISTINCT book_ref) AS bookings
FROM tickets;

Выручка и средний чек по бронированиям из таблицы bookings, total_amount хранит сумму брони:

Код: Выделить всё

SELECT count(*)            AS bookings,
       sum(total_amount)   AS revenue,
       round(avg(total_amount), 2) AS avg_check,
       min(total_amount)   AS min_amount,
       max(total_amount)   AS max_amount
FROM bookings;

Пассажиропоток по направлениям: считаем посадочные талоны по аэропорту вылета. Здесь WHERE отсекает строки до группировки, GROUP BY задаёт корзины, ORDER BY сортирует результат:

Код: Выделить всё

SELECT f.departure_airport,
       count(*) AS passengers
FROM boarding_passes bp
JOIN flights f ON f.flight_id = bp.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.departure_airport
ORDER BY passengers DESC
LIMIT 10;

HAVING против WHERE на одном запросе. Берём выручку по тарифам ticket_flights, но оставляем только тарифы, где суммарная выручка больше 100 млн. amount - стоимость перелёта по тарифу:

Код: Выделить всё

SELECT fare_conditions,
       count(*)        AS seats_sold,
       sum(amount)     AS revenue
FROM ticket_flights
GROUP BY fare_conditions
HAVING sum(amount) > 100000000
ORDER BY revenue DESC;

FILTER внутри агрегата: за один проход считаем общее число перелётов и отдельно бизнес-класс по каждому тарифному классу - условие живёт прямо в агрегате:

Код: Выделить всё

SELECT count(*) AS total,
       count(*) FILTER (WHERE fare_conditions = 'Business') AS business,
       count(*) FILTER (WHERE fare_conditions = 'Economy')  AS economy,
       round(avg(amount) FILTER (WHERE amount > 0), 2)      AS avg_paid
FROM ticket_flights;

string_agg и array_agg собирают значения группы в одну строку или массив. Покажем модели самолётов по дальности, склеив бортовые модели через запятую:

Код: Выделить всё

SELECT range / 1000 AS thousand_km,
       count(*)      AS models,
       string_agg(model ->> 'ru', ', ' ORDER BY model ->> 'ru') AS aircrafts
FROM aircrafts
GROUP BY range / 1000
ORDER BY thousand_km;

Многоуровневые итоги через ROLLUP. Считаем выручку по аэропорту вылета и тарифу, а сервер сам добавит подытоги по аэропорту и общий итог. Псевдостолбец grouping() показывает, какая строка является итоговой (1) или детальной (0):

Код: Выделить всё

SELECT f.departure_airport,
       tf.fare_conditions,
       sum(tf.amount) AS revenue,
       grouping(f.departure_airport, tf.fare_conditions) AS gflag
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
GROUP BY ROLLUP (f.departure_airport, tf.fare_conditions)
ORDER BY f.departure_airport NULLS LAST, tf.fare_conditions NULLS LAST;

GROUPING SETS даёт полный контроль над разрезами - тут считаем выручку отдельно по тарифу, отдельно по статусу рейса и общий итог в одном запросе:

Код: Выделить всё

SELECT tf.fare_conditions, f.status, sum(tf.amount) AS revenue
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
GROUP BY GROUPING SETS ((tf.fare_conditions), (f.status), ());

CUBE строит все возможные комбинации разрезов - удобно для сводных таблиц, но осторожно с числом столбцов, комбинаций становится 2 в степени N.

Частые грабли

• Ошибка "column must appear in the GROUP BY clause or be used in an aggregate function" - в SELECT попал негруппируемый столбец. Либо добавь его в GROUP BY, либо оберни в агрегат.
• count(*) и count(столбец) - это разные вещи. count(*) считает все строки, count(col) пропускает NULL. Легко получить расхождение на пустых полях.
• sum по столбцу, где все значения NULL, вернёт NULL, а не ноль. Оборачивай в coalesce(sum(x), 0), если нужен именно ноль.
• avg игнорирует NULL в знаменателе. Средний чек по 100 строкам, где 30 пустых, считается по 70 значениям, а не по 100 - это часто не то, что ожидают.
• Попытка фильтровать по агрегату в WHERE: WHERE sum(amount) > 100 не работает, для этого есть HAVING.
• В ROLLUP и CUBE итоговые строки имеют NULL в группирующих столбцах. Если в данных тоже есть настоящие NULL, отличить итог от данных можно только через grouping().
• DISTINCT внутри агрегата (count(DISTINCT ...)) заметно тяжелее обычного count - на больших таблицах это узкое место, проверяй через explain analyze.

Мини-лаба

Выполняй в psql на своей базе "Авиаперевозки".

• Посчитай общее число билетов: SELECT count(*) FROM tickets; запомни цифру.
• Посчитай выручку и средний чек по bookings одним запросом с sum и avg, округли avg до копеек.
• Сгруппируй ticket_flights по fare_conditions и выведи count и sum(amount) по каждому классу.
• Добавь HAVING, оставив только классы с суммарной выручкой больше 50 млн, и отсортируй по убыванию выручки.
• Перепиши третий запрос через FILTER: в одной строке выведи total и отдельные суммы по Economy, Comfort, Business.
• Сделай ROLLUP по departure_airport и fare_conditions через join к flights и найди строки итогов с помощью grouping().
• Прогони запрос из шага 6 через EXPLAIN ANALYZE и посмотри, какой способ агрегации выбрал планировщик: HashAggregate или GroupAggregate.

Контрольные вопросы

• Чем отличается момент применения WHERE от HAVING в порядке выполнения запроса?
• Почему count(*) и count(столбец) могут давать разные числа на одной таблице?
• Когда удобнее FILTER (WHERE ...) внутри агрегата, чем подзапрос или CASE?
• Что вернёт sum по столбцу, в котором все значения NULL, и как получить ноль?
• Какие итоговые строки добавляет ROLLUP по сравнению с обычным GROUP BY и как их отличить от обычных данных?
• В чём разница между GROUPING SETS, ROLLUP и CUBE по набору считаемых разрезов?

[Elain]

А если в group by написать не столбец, а выражение range/1000 - это вообще законно? у меня сработало, но не понял почему можно

[prometheus4]

Поймал себя на грабле с avg: пустые цены не считались в среднем, цифра вышла больше ожидаемой. coalesce спас, спасибо за пример с FILTER