Агрегатные функции: COUNT, SUM, FILTER и ловушки

Три лица COUNT

Выбор неверного варианта COUNT — самый частый баг агрегации в отчётном коде: число оказывается неверным без единой ошибки. Разберём, что именно делает каждая форма.

У COUNT три разных поведения, которые новички путают, — и разница меняет ответ:

• COUNT(*) — считает строки, точка. NULL включены; значения колонок не проверяются.
• COUNT(col) — считает строки, где col не NULL. Молча пропускает NULL.
• COUNT(DISTINCT col) — считает уникальные не-NULL значения col.

SELECT
  COUNT(*)               AS total_orders,      -- каждая строка
  COUNT(discount_code)   AS with_discount,     -- строки, где код не NULL
  COUNT(DISTINCT user_id) AS distinct_buyers   -- уникальные пользователи
FROM orders;

Если существует 1000 заказов, но лишь у 200 есть discount_code, COUNT(*) равен 1000, а COUNT(discount_code) — 200. Потянуться за COUNT(какая_то_колонка), когда имел в виду «все строки», — баг отчётности из топ-5: один NULL в той колонке тихо занижает счёт.

FILTER: условные агрегаты без CASE

Часто нужно «число оплаченных заказов» и «число отменённых» в одной строке. Старый трюк — CASE внутри агрегата; современный, более ясный способ — предложение FILTER:

SELECT
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'paid')    AS paid_revenue
FROM orders;

FILTER (WHERE …) ограничивает, какие строки видит один этот агрегат, независимо для каждого агрегата, не трогая WHERE запроса. Читается чище, чем SUM(CASE WHEN status='paid' THEN total END), и — что важно — COUNT(*) FILTER (WHERE …) считает совпавшие строки, тогда как COUNT(CASE WHEN … THEN 1 END) работает лишь потому, что несовпавшая ветка возвращает NULL (тонкость, что кусает писавших THEN 0, ведь это считает всё). FILTER — стандартный SQL и сеньорский дефолт для условной агрегации. (Полные сводные таблицы на нём ты построишь в уроке 05.)

Сторона производительности в том, что все три агрегата FILTER выше вычисляются за один проход по данным. Альтернатива, к которой тянутся джуны — три отдельных запроса (WHERE status='paid', затем WHERE status='cancelled', …), объединённых UNION или прогнанных последовательно, — сканирует таблицу трижды. На таблице orders в 12М строк это ~2.4с (3 × ~800мс Seq Scan) против ~850мс у однопроходной формы FILTER: движок читает каждую строку один раз и маршрутизирует её в те агрегаты, чьим предикатам она удовлетворяет. Отдельная ловушка — COUNT(DISTINCT col): в отличие от обычного COUNT, он не может быть потоковым счётчиком — ему нужно материализовать и дедуплицировать каждое значение, поэтому на колонке высокой кардинальности он строит sort или hash всех различных значений и первым в запросе спиллит на диск. COUNT(*) FILTER остаётся дешёвым счётчиком; COUNT(DISTINCT …) — дорогой родственник, за которым стоит следить в EXPLAIN ANALYZE.

Упорядоченные агрегаты: string_agg / array_agg

Некоторым агрегатам важен порядок. string_agg и array_agg конкатенируют значения группы, и порядок можно задать внутри агрегата:

SELECT user_id,
       string_agg(status, ',' ORDER BY created_at) AS status_timeline,
       array_agg(id ORDER BY created_at DESC)      AS recent_first
FROM orders
GROUP BY user_id;

Без ORDER BY внутри агрегата порядок конкатенации не определён — он зависит от плана и может меняться между запусками. Если порядок важен (таймлайн, CSV), всегда задавай его внутри скобок, а не во внешнем ORDER BY запроса (который сортирует лишь финальные строки, а не значения внутри каждого агрегата).

Числовые ловушки

Два бага агрегации тихие и дорогие:

Целочисленный AVG. AVG по колонке integer возвращает numeric в Postgres (хорошо), но SUM/деление, написанные руками по целым, усекают. Классика — SUM(total)/COUNT(*) по int-колонкам: это целочисленное деление, отбрасывающее дробь. Сначала приведи тип: SUM(total)::numeric / COUNT(*) или просто используй AVG(total).

Переполнение SUM. SUM по колонке integer возвращает bigint (безопасно до ~9.2×10^18), но SUM по колонке bigint возвращает numeric — и SUM по int-колонке с миллиардами крупных строк всё ещё может переполниться, если приводишь тип обратно. Боевой отказ: суммирование денежной колонки, хранимой как int-центы, по нагруженной таблице со временем спотыкается о integer out of range. Защищайся: храни деньги как numeric (или bigint-центы) и при сомнении приводи внутри SUM: SUM(amount::bigint).