GROUP BY в PostgreSQL: агрегаты на примере заказов

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

Живой пример: отчёт по заказам

Представим интернет-магазин с таблицей orders. Одна строка — один заказ. В отчёте нужны только заказы со статусом paid, сгруппированные по менеджеру:

CREATE TABLE orders (
    id integer PRIMARY KEY,
    manager_id integer NOT NULL,
    status text NOT NULL,
    total numeric(12, 2),
    created_at timestamptz NOT NULL
);

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

Если нужно узнать общую выручку, агрегатная функция работает без GROUP BY:

SELECT
    COUNT(*) AS paid_orders,
    SUM(total) AS paid_revenue,
    AVG(total) AS average_order
FROM orders
WHERE status = 'paid';

Результат будет одной строкой. COUNT посчитает строки, SUM сложит значения, а AVG вычислит среднее. Чтобы получить не общий итог, а отдельную строку на каждого менеджера, добавьте группировку.

Как GROUP BY меняет результат

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

SELECT
    manager_id,
    COUNT(*) AS paid_orders,
    SUM(total) AS paid_revenue,
    AVG(total) AS average_order
FROM orders
WHERE status = 'paid'
GROUP BY manager_id
ORDER BY paid_revenue DESC;

Теперь каждая строка результата соответствует одному менеджеру. manager_id можно вывести напрямую, потому что он указан в GROUP BY. Остальные столбцы не выводятся как отдельные значения: для них нужно явно выбрать агрегат — сумму, минимум, максимум или другую функцию.

GROUP BY не «сортирует» данные и не выбирает случайную строку. Он сначала формирует группы, а затем сворачивает каждую группу выбранными агрегатами.

Агрегатная функция — функция, которая получает несколько строк и возвращает одно значение для всей выборки или отдельной группы: например, COUNT, SUM, AVG, MIN и MAX.

Три фильтра, которые часто путают

В отчёте по заказам фильтр может применяться на разных этапах. От этого меняется смысл результата.

WHERE фильтрует строки до группировки

Так мы считаем только оплаченные заказы. Отмененные и новые заказы вообще не попадут в группы:

SELECT
    manager_id,
    SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY manager_id;

Это обычно лучший вариант, когда ненужные строки не должны участвовать ни в одном расчёте.

HAVING фильтрует уже готовые группы

Допустим, в отчёте нужны только менеджеры, у которых оплаченная выручка превышает 100 000:

SELECT
    manager_id,
    COUNT(*) AS paid_orders,
    SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY manager_id
HAVING SUM(total) > 100000
ORDER BY paid_revenue DESC;

Условие по агрегату нельзя перенести в WHERE: на этом этапе сумма группы ещё не существует. PostgreSQL сообщит об ошибке, если написать WHERE SUM(total) > 100000.

FILTER считает несколько показателей за один проход

Иногда нужно оставить все заказы в наборе, но вывести отдельные показатели по статусам. Для этого в PostgreSQL есть фильтр внутри агрегата:

SELECT
    manager_id,
    COUNT(*) AS all_orders,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
    SUM(total) FILTER (WHERE status = 'paid') AS paid_revenue,
    SUM(total) FILTER (WHERE status = 'cancelled') AS cancelled_revenue
FROM orders
GROUP BY manager_id;

Здесь GROUP BY формирует группы по менеджерам, а каждый FILTER определяет, какие строки участвуют в конкретном агрегате. Это понятнее, чем несколько подзапросов с одинаковой группировкой. Инвалидация отчётного кеша и индексы для такого запроса — отдельные темы; здесь важна логика агрегации.

Что выбрать: WHERE, FILTER или отдельный запрос

Ошибки, из-за которых отчёт врёт

Вывод столбца, которого нет в GROUP BY

Такой запрос не выполнится:

SELECT
    manager_id,
    status,
    SUM(total) AS revenue
FROM orders
GROUP BY manager_id;

В одной группе менеджера могут быть разные статусы. PostgreSQL не станет выбирать один из них наугад и вернёт ошибку о том, что столбец status должен появиться в GROUP BY или использоваться в агрегате.

Если нужна выручка отдельно по менеджеру и статусу, группируйте по обоим полям:

SELECT
    manager_id,
    status,
    SUM(total) AS revenue
FROM orders
GROUP BY manager_id, status
ORDER BY manager_id, status;

COUNT(*) и COUNT(total) — не одно и то же

COUNT(*) считает строки. COUNT(total) считает только строки, где total не равен NULL. Если сумма заказа ещё не заполнена, эти выражения дадут разные результаты:

SELECT
    COUNT(*) AS all_rows,
    COUNT(total) AS rows_with_total,
    SUM(total) AS total_sum
FROM orders;

Не подменяйте пропущенную сумму нулём без проверки бизнес-логики. Незавершённый заказ и заказ стоимостью ноль — разные состояния.

SUM может вернуть NULL

Если после фильтрации не осталось подходящих строк, SUM обычно возвращает NULL, а не 0. Для отчёта, которому нужен числовой ноль, используйте явное преобразование:

SELECT
    COALESCE(SUM(total), 0) AS paid_revenue
FROM orders
WHERE status = 'paid';

COALESCE не исправляет исходные данные. Он только задаёт значение, которое будет показано вместо NULL.

Среднее без нужной точности

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

Группировка по месяцу: не обрезайте дату слишком рано

Для отчёта «выручка по месяцам» недостаточно вывести дату создания. Одинаковые даты будут разными группами. В PostgreSQL 16 можно сгруппировать по началу месяца:

SELECT
    date_trunc('month', created_at) AS month_start,
    SUM(total) AS revenue,
    COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY date_trunc('month', created_at)
ORDER BY month_start;

В запросе используется часовой пояс значения timestamptz. Если бизнес считает месяцы по часовому поясу магазина, его нужно явно учитывать в отдельном согласованном правиле. Иначе заказ около полуночи может попасть в соседний месяц относительно отчёта менеджера.

Когда какой вариант выбирать

• Нужен один итог по всей таблице — агрегат без GROUP BY.
• Нужна одна строка на менеджера, статус или месяц — добавляйте соответствующий ключ в GROUP BY.
• Нужно исключить строки до любых расчётов — используйте WHERE.
• Нужно отобрать менеджеров по сумме или числу заказов — используйте HAVING.
• Нужно получить оплаченные, отменённые и все заказы в одной строке — используйте несколько агрегатов с FILTER.

Проверяйте не только синтаксис, но и гранулярность результата: одна строка должна означать ровно то, что обещает отчёт. Если в ней смешаны менеджер, месяц и клиент, а группировка учитывает только менеджера, сумма почти наверняка будет интерпретирована неправильно.

Где подход ломается

Агрегация становится опасной, когда к заказам присоединяют таблицу позиций. Один заказ может иметь пять товаров, поэтому после JOIN его сумма будет повторена пять раз. Нельзя без проверки складывать поле заказа после соединения с таблицей «один-ко-многим».

Безопасные варианты — сначала агрегировать позиции до уровня заказа, отдельно получить заказные суммы или использовать агрегат, который соответствует нужной гранулярности. Конкретный выбор зависит от схемы данных; перед изменением запроса сравните число строк до и после JOIN и контрольную сумму по нескольким известным заказам.

Ещё одна граница — большие таблицы. Синтаксически правильный GROUP BY может читать миллионы строк. На PostgreSQL 16 проверяйте план через EXPLAIN (ANALYZE, BUFFERS), но запускайте такой замер на тестовой базе или с пониманием нагрузки: ANALYZE действительно выполняет запрос и может создать заметный расход ресурсов.

Что сделать дальше

Возьмите один реальный отчёт и зафиксируйте его гранулярность: «одна строка на менеджера за месяц» или «одна строка на менеджера». Затем отдельно проверьте три числа — количество заказов, сумму и средний чек — на небольшом наборе данных, который можно посчитать вручную. После этого сравните план запроса и объём прочитанных блоков через EXPLAIN (ANALYZE, BUFFERS). Такой порядок быстрее обнаружит ошибку в логике, чем попытка сразу оптимизировать индексы.