Агрегация: суммы, количества и итоги — PostgreSQL

Агрегация: суммы, количества и итоги по группам

Агрегатная функция получает несколько строк и возвращает одно итоговое значение. Например, count считает строки, sum складывает числа, min и max находят границы, а avg вычисляет среднее.

Агрегация меняет смысл строки результата. До группировки одна строка может означать заказ, после неё — итог одной организации за месяц.

#COUNT и NULL

SELECT
    count(*) AS result_rows,
    count(p.id) AS matched_payments,
    coalesce(sum(p.amount), 0) AS captured_amount
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id
 AND p.status = 'captured';

После LEFT JOIN заказ без платежа всё равно даёт одну строку, поэтому count(*) его посчитает. count(p.id) считает только строки, где p.id не равен NULL, то есть найденные платежи.

Почти все агрегатные функции игнорируют NULL. На пустом наборе sum возвращает NULL, а не ноль. coalesce(value, 0) заменяет NULL на ноль, если такой смысл подходит задаче.

#GROUP BY задаёт группы

SELECT
    org_id,
    date_trunc('month', created_at) AS month,
    count(*) AS orders,
    sum(total_amount) AS revenue
FROM orders
GROUP BY org_id, date_trunc('month', created_at);

GROUP BY собирает строки с одинаковыми org_id и месяцем. Одна строка результата теперь означает «одна организация за один месяц». date_trunc('month', ...) округляет момент до начала месяца.

Столбец в SELECT должен либо входить в ключ группировки, либо быть результатом агрегатной функции. Иначе непонятно, какое из нескольких значений группы показывать.

#Несколько условных показателей

FILTER задаёт условие только для конкретной агрегатной функции:

SELECT
    org_id,
    count(*) AS all_orders,
    count(*) FILTER (WHERE status = 'paid') AS paid_orders,
    sum(total_amount) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders
GROUP BY org_id;

Все заказы участвуют в all_orders, а в двух других показателях — только оплаченные. Если перенести условие в общий WHERE, неоплаченные строки исчезнут для всех вычислений.

#WHERE и HAVING

WHERE фильтрует исходные строки до группировки. HAVING фильтрует уже полученные группы:

SELECT org_id, sum(total_amount) AS revenue
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00+00'
GROUP BY org_id
HAVING sum(total_amount) >= 50000;

Сначала остаются заказы 2026 года, затем считается сумма каждой организации, после чего остаются организации с выручкой от 50 000.

#Детальные строки, подытоги и общий итог

GROUPING SETS позволяет в одном запросе вычислить несколько уровней:

SELECT
    org_id,
    status,
    count(*) AS order_count,
    sum(total_amount) AS revenue,
    grouping(org_id) AS org_is_total,
    grouping(status) AS status_is_total
FROM orders
GROUP BY GROUPING SETS (
    (org_id, status),
    (org_id),
    ()
);

Запрос возвращает:

итог по каждой паре «организация + статус»;
подытог организации по всем статусам;
общий итог по всем организациям.

В строке итога PostgreSQL подставляет NULL вместо свёрнутого измерения. Но NULL мог находиться и в исходных данных. Функция grouping() различает эти случаи: 1 означает строку итога, 0 — обычную группу.

ROLLUP(a, b) — короткая запись для (a, b), (a), (). CUBE(a, b) создаёт все сочетания, включая итог только по b; число групп при многих столбцах быстро растёт.

#Сначала агрегируйте, потом соединяйте

Если у заказа несколько позиций и платежей, прямой JOIN размножит строки. Сначала получите одну строку на заказ в каждом источнике:

WITH items AS (
    SELECT order_id, sum(quantity * unit_price) AS item_total
    FROM order_items
    GROUP BY order_id
), payments_by_order AS (
    SELECT order_id, sum(amount) AS captured_total
    FROM payments
    WHERE status = 'captured'
    GROUP BY order_id
)
SELECT o.org_id,
       sum(items.item_total) AS item_revenue,
       sum(payments_by_order.captured_total) AS captured_revenue
FROM orders AS o
JOIN items ON items.order_id = o.id
LEFT JOIN payments_by_order ON payments_by_order.order_id = o.id
GROUP BY o.org_id;

Не заменяйте это на sum(DISTINCT amount): два разных заказа могут иметь одинаковую честную сумму, и один из них ошибочно исчезнет из вычисления.

#Память и временные файлы

PostgreSQL может группировать через хеш-таблицу (HashAggregate) или после сортировки (GroupAggregate). Если памяти не хватает, промежуточные данные попадают во временные файлы на диске.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT org_id, status, sum(total_amount)
FROM orders
GROUP BY org_id, status;

В плане сравнивайте ожидаемое и фактическое число групп, способ сортировки, использование диска и временные чтения или записи. Не увеличивайте глобально work_mem по одному запросу: память выделяется нескольким операциям в каждом из множества одновременных сеансов.

#Практика

В лаборатории 10 вы построите детальные итоги, подытоги и общий итог через GROUPING SETS. Проверка сравнит сумму с независимо вычисленным контрольным значением.

Перед завершением объясните:

Почему count(*) и count(column) могут отличаться?
На каком этапе работают WHERE и HAVING?
Какой смысл имеет одна строка до и после GROUP BY?
Почему две связи «один ко многим» нужно сначала агрегировать отдельно?

Подробнее: агрегатные выражения, GROUP BY.