Агрегаты в аналитике: чек-лист для PostgreSQL 16

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

Сначала зафиксируйте, что считается одной строкой

До выбора SUM, COUNT или AVG сформулируйте зерно результата: одна строка на заказ, на клиента, на день или на сочетание нескольких измерений. Это не формальность: зерно определяет, какие строки должны попасть в агрегат.

Термин. Зерно данных — уровень детализации строки. Например, строка заказа и строка товара в заказе имеют разное зерно: у одного заказа может быть несколько товарных строк.

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

Если отчёт должен показывать выручку по месяцам, но в группировку попал ещё и идентификатор заказа, вы получите строки по заказам, а не месячные итоги. Запрос корректен для SQL, но не отвечает на вопрос отчёта.

Проверьте соединения до подсчёта

Главный источник завышенных сумм — связь «один ко многим». Допустим, orders содержит итог заказа, а order_items — отдельные товары. После соединения заказ с тремя товарами появится в трёх строках. Поэтому SUM(orders.total) посчитает его итог трижды.

Для суммы товарных строк и количества заказов безопаснее сначала свернуть товары до уровня заказа, а потом присоединить их к заказам. Пример для PostgreSQL 16; названия таблиц и столбцов здесь условные.

WITH item_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS item_revenue
    FROM order_items
    GROUP BY order_id
)
SELECT
    date_trunc('month', o.created_at) AS month,
    COUNT(*) AS order_count,
    COALESCE(SUM(i.item_revenue), 0) AS item_revenue
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.id
WHERE o.status = 'paid'
  AND o.created_at >= TIMESTAMP '2025-01-01 00:00:00'
  AND o.created_at < TIMESTAMP '2025-02-01 00:00:00'
GROUP BY date_trunc('month', o.created_at)
ORDER BY month;

Здесь промежуточный результат содержит максимум одну строку на заказ. Поэтому COUNT(*) считает заказы, а не товары. Если заказ без товарных строк всё же возможен, его итог в этой выборке будет нулём благодаря COALESCE. Уточните отдельно, соответствует ли это правилам вашей метрики.

Чек-лист перед публикацией отчёта

1. Сверьте зерно. Скажите вслух, что означает одна строка результата. Проверьте, что группировка не включает лишние поля и не скрывает нужные разрезы.
2. Проследите путь каждой суммы. Для каждого JOIN выясните, сколько строк одной исходной сущности появится после соединения. Если число может вырасти, не суммируйте без проверки поля родительской таблицы.
3. Разведите виды счёта. В PostgreSQL COUNT(*) считает строки, а COUNT(column) — значения столбца, кроме NULL. После LEFT JOIN строка без совпадения всё равно существует: для количества найденных дочерних записей обычно считают её ненулевой ключ, а не *.
4. Определите смысл NULL. SUM не учитывает NULL, а если входных строк нет, возвращает NULL. Заменяйте его на ноль через COALESCE только если отсутствие данных действительно означает нулевой результат, а не неизвестное значение.
5. Проверьте границы периода. Для последовательных интервалов удобно задавать начало включительно, конец исключительно: от начала месяца до начала следующего. Так запись ровно на границе не попадёт сразу в два отчётных периода.
6. Проверьте условие до или после группировки. WHERE отбирает исходные строки до агрегации; HAVING отбирает уже сформированные группы. Перенос условия между ними может изменить сам показатель.
7. Сверьте итог с независимой выборкой. На небольшом интервале сравните число заказов с количеством уникальных идентификаторов, а сумму — с контрольным расчётом на уровне первичных строк. Расхождение сначала объясните, затем публикуйте график.

Когда выбирать COUNT DISTINCT, а когда предварительную агрегацию

COUNT(DISTINCT o.id) полезен, когда после JOIN нужно посчитать уникальные заказы. Но он не делает безопасным SUM(o.total): сумма всё ещё может повторять одно значение для каждой строки товара. Если в одном запросе есть и счёт заказов, и выручка по товарам, разберите их зерно отдельно — предварительная агрегация часто делает логику прозрачнее.

Агрегат не знает, что вы считаете: заказ, товар или клиента. Он складывает и считает строки, которые вы ему передали.

Не добавляйте DISTINCT как универсальный пластырь. Он способен замаскировать ошибку соединения и изменить смысл метрики. Например, сумма двух одинаковых по величине покупок после SUM(DISTINCT amount) учтёт значение только один раз, хотя покупок было две.

Что ломается на границах

• Пустая группа. Для периода без строк агрегат может вернуть NULL, а не ноль. Решите, как пустой период должен выглядеть в отчёте, и задайте это явно.
• NULL в ключах и показателях. COUNT(column) пропускает NULL, а обычные агрегаты не превращают пропуск в ноль автоматически. Проверьте, не означает ли NULL «данные не загрузились».
• Дробление группировки. Включённый в GROUP BY статус или категория может разделить итог на несколько строк. Это правильно только если такой разрез нужен пользователю отчёта.
• Тяжёлый DISTINCT. Подсчёт уникальных значений на большом наборе данных может требовать заметных ресурсов. Не угадывайте по размеру таблицы: проверьте план и время выполнения на данных, близких к рабочим.

Инвалидация кэша и устройство витрин данных — отдельные темы; здесь речь только о корректности SQL-агрегатов.

Что проверить дальше

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

Примеры и поведение функций сверяйте с документацией PostgreSQL 16: синтаксис и детали могут отличаться в другой СУБД или версии.