GROUP BY и агрегаты в PostgreSQL: что знать новичку

За 20–30 минут вы соберёте в PostgreSQL 16 отчёт по категориям товаров: посчитаете строки, сумму и среднее, отфильтруете исходные записи и группы, а затем разберёте типичные ошибки GROUP BY. Для практики достаточно локальной базы или любого клиента, который умеет выполнять SQL.

Что нужно иметь до начала

• PostgreSQL 16.x. Синтаксис ниже также знаком PostgreSQL 15 и более ранним версиям, но примеры написаны и проверяются для ветки 16.x.
• Доступ к базе с правом создавать временные таблицы. Администраторские права не нужны.
• Любой SQL-клиент: psql, DBeaver, DataGrip или веб-интерфейс вашего хостинга.
• Понимание двух терминов: строка — отдельная запись таблицы, группа — набор строк с одинаковым значением выбранного поля.

Сноска. Агрегатная функция — функция, которая получает несколько строк и возвращает одно значение: например, count считает строки, а sum складывает значения.

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

Шаг 1. Подготовьте данные

Создайте временную таблицу и заполните её небольшим набором заказов. Временная таблица живёт только в текущем соединении с базой, поэтому вы не измените рабочие данные.

DROP TABLE IF EXISTS demo_orders;

CREATE TEMP TABLE demo_orders (
    order_id integer,
    category text,
    customer_id integer,
    amount numeric(10, 2),
    status text
);

INSERT INTO demo_orders (order_id, category, customer_id, amount, status)
VALUES
    (1, 'books', 101, 1200.00, 'paid'),
    (2, 'books', 102, 800.00, 'paid'),
    (3, 'books', 103, NULL, 'cancelled'),
    (4, 'games', 104, 2500.00, 'paid'),
    (5, 'games', 105, 1800.00, 'paid'),
    (6, 'games', 106, 900.00, 'cancelled'),
    (7, 'music', 107, 700.00, 'paid');

SELECT *
FROM demo_orders
ORDER BY order_id;

Проверка. В результате должны появиться семь строк. У заказа с order_id = 3 поле amount равно NULL. Это понадобится для проверки поведения агрегатов.

Шаг 2. Получите одну строку на группу

Первый запрос покажет, сколько заказов относится к каждой категории. Поле после GROUP BY определяет границы групп.

SELECT
    category,
    count(*) AS order_count
FROM demo_orders
GROUP BY category
ORDER BY category;

Проверка. Должны появиться три строки: books — 3, games — 3, music — 1. Без GROUP BY count(*) вернул бы одно общее число — 7.

Что именно группируется

PostgreSQL собирает строки с одинаковым значением category в одну группу. Затем вычисляет выбранные агрегаты отдельно для каждой группы. Если добавить в SELECT обычное поле, которого нет в GROUP BY, база не сможет выбрать его значение из нескольких строк.

SELECT
    category,
    status,
    count(*) AS order_count
FROM demo_orders
GROUP BY category, status
ORDER BY category, status;

Проверка. Теперь строк будет больше: например, для books появятся отдельные группы paid и cancelled. Группировка идёт по паре значений, а не только по категории.

Шаг 3. Добавьте агрегатные функции

Один отчёт обычно считает не только количество записей. Ниже запрос показывает количество заказов, сумму и среднюю сумму заказа.

SELECT
    category,
    count(*) AS order_count,
    count(amount) AS orders_with_amount,
    coalesce(sum(amount), 0) AS total_amount,
    round(avg(amount), 2) AS average_amount,
    min(amount) AS minimum_amount,
    max(amount) AS maximum_amount
FROM demo_orders
GROUP BY category
ORDER BY category;

Проверка. Для books order_count равен 3, а orders_with_amount — 2: count(*) считает все строки, тогда как count(amount) не считает NULL. Сумма книг равна 2000.00, среднее — 1000.00.

sum, avg, min и max игнорируют NULL. Если в группе нет ни одного ненулевого значения, результат обычно будет NULL. coalesce заменяет NULL на указанное значение, поэтому сумма пустого набора в отчёте станет нулём.

Почему count(*) и count(column) — не одно и то же

• count(*) считает строки, включая строки, где отдельные поля равны NULL.
• count(column) считает только строки, где column не равен NULL.
• count(DISTINCT customer_id) считает уникальных клиентов с ненулевым идентификатором.

Если нужно посчитать только оплаченные заказы, не маскируйте условие внутри агрегата на первом шаге. Сначала отфильтруйте строки через WHERE.

SELECT
    category,
    count(*) AS paid_orders,
    sum(amount) AS paid_amount
FROM demo_orders
WHERE status = 'paid'
GROUP BY category
ORDER BY category;

Проверка. В отчёте не будет отменённых заказов: для books количество станет 2, для games — 2, для music — 1.

Шаг 4. Разделите WHERE и HAVING

WHERE отбрасывает отдельные строки до группировки. HAVING отбрасывает уже собранные группы. Это различие критично: условие по результату sum или count нельзя поставить в обычный WHERE.

SELECT
    category,
    count(*) AS paid_orders,
    sum(amount) AS paid_amount
FROM demo_orders
WHERE status = 'paid'
GROUP BY category
HAVING sum(amount) >= 2000
ORDER BY category;

Проверка. Останутся категории, где сумма оплаченных заказов не меньше 2000. В нашем наборе это books и games; music не пройдёт условие.

Сначала PostgreSQL применяет фильтр строк, затем формирует группы, вычисляет агрегаты и проверяет HAVING. Если вам нужно отфильтровать обычное поле заказа, используйте WHERE. Если нужно отфильтровать размер или сумму группы, используйте HAVING.

Шаг 5. Проверьте типичные ошибки

Ошибка: поле не указано в GROUP BY

SELECT category, status, count(*)
FROM demo_orders
GROUP BY category;

PostgreSQL вернёт ошибку column "demo_orders.status" must appear in the GROUP BY clause or be used in an aggregate function. Исправьте запрос одним из способов: добавьте status в GROUP BY или выберите агрегат, например min(status), если такая логика действительно нужна.

Ошибка: агрегат в WHERE

SELECT category, count(*)
FROM demo_orders
WHERE count(*) > 1
GROUP BY category;

Типичное сообщение PostgreSQL: aggregate functions are not allowed in WHERE. Перенесите условие в HAVING: HAVING count(*) > 1.

Ошибка: NULL принят за ноль

Если категория содержит только строки с NULL в amount, sum(amount) вернёт NULL, а не 0. Используйте coalesce(sum(amount), 0), если отчёт должен показывать именно нулевую сумму. Не подменяйте NULL нулём автоматически, если NULL означает «сумма неизвестна».

Ошибка: фильтр меняет саму группу

Запрос с WHERE status = 'paid' считает только оплаченные заказы. Запрос без WHERE считает все заказы, а затем может использовать условную логику отдельно. Перед исправлением проверьте, что именно требуется бизнесу: «в группе есть оплаченный заказ» и «сумма оплаченных заказов» — разные показатели.

Что проверить после первого запроса

1. Сверьте число групп с числом уникальных значений поля группировки.
2. Проверьте, должны ли NULL попадать в отчёт и как они должны отображаться.
3. Сверьте count(*) и count(поле) на данных с пропусками.
4. Убедитесь, что фильтр строк стоит в WHERE, а фильтр агрегатов — в HAVING.
5. Добавьте ORDER BY явно: порядок строк без него не гарантирован.

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

Подставьте в примеры реальную таблицу и сначала повторите отчёт без дополнительных условий. Затем отдельно проверьте NULL, фильтр WHERE и условие HAVING. На рабочей базе сравните план выполнения через EXPLAIN, особенно если GROUP BY обрабатывает миллионы строк. Индексы, сортировка и параллельное выполнение могут заметно изменить стоимость запроса; оптимизация плана — отдельная тема.