GROUP BY и агрегаты в PostgreSQL: ошибки новичков

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

Что нужно подготовить

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

Выполните подготовочный скрипт целиком. Он создаст заказы с повторяющимися клиентами, разными статусами и одним значением NULL:

DROP TABLE IF EXISTS demo_orders;

CREATE TEMP TABLE demo_orders (
    order_id integer,
    customer_name text,
    region text,
    status text,
    amount numeric(10, 2),
    created_at date
);

INSERT INTO demo_orders (order_id, customer_name, region, status, amount, created_at)
VALUES
    (1, 'Анна', 'Москва', 'paid', 1200.00, '2024-01-10'),
    (2, 'Анна', 'Москва', 'paid', 800.00, '2024-01-12'),
    (3, 'Борис', 'Казань', 'new', 450.00, '2024-01-13'),
    (4, 'Вера', 'Москва', 'cancelled', 700.00, '2024-01-14'),
    (5, 'Глеб', NULL, 'paid', 300.00, '2024-01-15'),
    (6, 'Дина', 'Казань', 'paid', 1500.00, '2024-01-16');

Проверка: запрос SELECT * FROM demo_orders ORDER BY order_id; должен вернуть шесть строк. Если PostgreSQL сообщает, что таблица не существует в следующем запросе, вы открыли новое соединение: временная таблица живёт только внутри исходной сессии.

Шаг 1. Сгруппируйте строки и посчитайте заказы

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

SELECT
    region,
    COUNT(*) AS order_count
FROM demo_orders
GROUP BY region
ORDER BY region;

Проверка: результат должен содержать три группы — Казань, Москва и отдельную группу для NULL. В PostgreSQL все значения NULL при группировке попадают в одну группу.

Какие агрегаты нужны чаще всего

• COUNT(*) считает строки, включая строки, где отдельные поля равны NULL.
• COUNT(amount) считает только строки, в которых amount не равен NULL.
• SUM(amount) складывает значения, а AVG(amount) вычисляет среднее по ненулевым значениям.
• MIN(amount) и MAX(amount) находят минимальное и максимальное ненулевое значение.

Разница между COUNT(*) и COUNT(column) становится критичной, когда в таблице есть пропуски. Если нужно показать ноль вместо NULL для пустого результата суммы, используйте COALESCE:

SELECT
    region,
    COUNT(*) AS order_count,
    COALESCE(SUM(amount), 0) AS total_amount,
    ROUND(AVG(amount), 2) AS average_amount
FROM demo_orders
GROUP BY region
ORDER BY total_amount DESC;

Проверка: для каждой группы должны появиться количество, сумма и средний чек. В этом наборе у всех регионов есть сумма, поэтому COALESCE не меняет результат, но защищает запрос от пустого набора данных.

Шаг 2. Исправьте ошибку с полем в SELECT

Самая частая ошибка новичка — выбрать поле, которого нет ни в GROUP BY, ни внутри агрегатной функции:

SELECT
    region,
    customer_name,
    COUNT(*) AS order_count
FROM demo_orders
GROUP BY region;

PostgreSQL остановит запрос с сообщением вроде column demo_orders.customer_name must appear in the GROUP BY clause or be used in an aggregate function. Причина не в строгом характере PostgreSQL: внутри одного региона могут быть несколько клиентов, и база не знает, какое имя вывести в одну строку.

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

SELECT
    region,
    customer_name,
    COUNT(*) AS order_count
FROM demo_orders
GROUP BY region, customer_name
ORDER BY region, customer_name;

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

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

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

WHERE отбрасывает отдельные строки до группировки. HAVING отбрасывает уже собранные группы после агрегирования. Поэтому условие по обычному полю ставьте в WHERE, а условие по результату COUNT или SUM — в HAVING.

SELECT
    region,
    COUNT(*) AS paid_orders,
    SUM(amount) AS paid_amount
FROM demo_orders
WHERE status = 'paid'
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY paid_amount DESC;

Проверка: в расчёт попадут только оплаченные заказы, а в результате останутся регионы минимум с двумя такими заказами. В подготовленных данных это Москва: у неё два заказа со статусом paid.

Попытка написать агрегатное условие в WHERE закончится ошибкой aggregate functions are not allowed in WHERE. Наоборот, обычное условие по статусу в HAVING может дать рабочий, но менее понятный запрос и заставить базу сначала собрать лишние группы.

Шаг 4. Не перепутайте COUNT(*) и COUNT(DISTINCT)

Количество заказов и количество клиентов — разные показатели. COUNT(*) считает строки, а COUNT(DISTINCT customer_name) — уникальные ненулевые значения клиента:

SELECT
    region,
    COUNT(*) AS order_count,
    COUNT(DISTINCT customer_name) AS unique_customers
FROM demo_orders
GROUP BY region
ORDER BY region;

Проверка: в Москве будет три заказа и два клиента, потому что Анна встречается дважды. Если в таблице появятся два заказа с пустым именем клиента, COUNT(DISTINCT customer_name) не посчитает NULL отдельным клиентом.

Частые ошибки новичков

Группировка по дате с временем

Если поле имеет тип timestamp, группировка по нему соберёт почти каждую запись отдельно: разные секунды означают разные значения. Для отчёта по дням нужно явно привести дату к нужной гранулярности, например через created_at::date. Для часовых поясов и границ суток это отдельный вопрос: проверьте, в какой временной зоне хранятся и показываются данные.

Фильтрация результата через WHERE

Запрос с WHERE COUNT(*) > 1 не выполнится. Перенесите условие в HAVING, потому что количество появляется только после формирования группы.

Ожидание нулевых групп

Если в таблице нет строк для региона, обычный GROUP BY не создаст строку с нулём. Для полного справочника регионов нужен LEFT JOIN со справочной таблицей и COUNT(orders.order_id), а не COUNT(*): при отсутствии заказа строка после соединения всё равно существует.

Неверное толкование NULL

NULL означает неизвестное или отсутствующее значение, а не пустую строку и не ноль. Проверяйте его через IS NULL или IS NOT NULL; выражение region = NULL не вернёт нужные строки.

Двойной подсчёт после JOIN

Если заказ соединить с таблицей товаров, одна строка заказа может превратиться в несколько строк — по одной на товар. Тогда SUM(order_amount) завысит оборот. Сначала определите уровень одной строки: заказ, товар или клиент. При необходимости агрегируйте товары отдельно, а затем соединяйте результат с заказами.

Как проверить запрос перед публикацией отчёта

1. Опишите словами одну строку результата: регион, клиент, день или другая сущность.
2. Проверьте, что каждое неагрегированное поле из SELECT есть в GROUP BY.
3. Сверьте COUNT(*) с ручным подсчётом на маленьком наборе данных.
4. Проверьте строки с NULL, повторяющиеся идентификаторы и пустые группы.
5. После соединений сравните количество строк до и после JOIN. Резкий рост обычно объясняет завышенную сумму.

Для запроса на большой таблице отдельно измерьте план через EXPLAIN (ANALYZE, BUFFERS). Не переносите результат измерения с одной схемы на другую: скорость зависит от объёма данных, статистики, индексов, памяти и параллельного выполнения.

Сноска. Гранулярность — это уровень детализации результата: например, одна строка на заказ, день или регион. Ошибка в гранулярности приводит к двойному подсчёту даже при синтаксически правильном SQL.

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

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