Агрегация: GROUP BY, HAVING, GROUPING SETS

Команда аналитики получила задачу: отчёт по продажам в разрезе год/месяц/категория/регион - с промежуточными итогами. Написали 4 отдельных запроса через UNION ALL: 45 секунд на 200 млн строк. Переписали на GROUPING SETS - 2 секунды. Один проход вместо четырёх.

• **BI-отчёты (Tableau, Metabase)** - автоматически генерируют ROLLUP/CUBE для drill-down отчётов по иерархиям
• **Финансовая аналитика** - GROUPING SETS для P&L по продуктам, регионам, кварталам за один запрос
• **FILTER в дашбордах** - conversion rate = `COUNT(*) FILTER (WHERE converted) / COUNT(*)::float` без подзапросов

Агрегатные функции: операции над группами строк

Агрегатная функция принимает набор строк и возвращает одно значение. В отличие от скалярных функций, они работают не построчно, а над группой. PostgreSQL выполняет агрегацию в два этапа: сначала группирует строки, затем применяет функцию к каждой группе.

**COUNT(*) vs COUNT(col):** `COUNT(*)` считает все строки включая NULL. `COUNT(col)` считает только строки, где `col IS NOT NULL`. `COUNT(DISTINCT col)` - уникальные непустые значения. Частая ошибка: `COUNT(col)` даёт меньше `COUNT(*)` при наличии NULL.

GROUP BY и HAVING: группировка и фильтр после агрегации

`GROUP BY` разбивает строки на группы по значениям указанных столбцов - по одной результирующей строке на группу. `HAVING` фильтрует уже сформированные группы (после агрегации), тогда как `WHERE` фильтрует строки до группировки. Порядок выполнения: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

**Частая ошибка:** использовать агрегатную функцию в WHERE. Запрос `WHERE SUM(amount) > 1000` вызовет ошибку - в момент WHERE агрегации ещё нет. Фильтр по агрегату всегда идёт в HAVING.

ROLLUP и CUBE: автоматические промежуточные итоги

`ROLLUP` генерирует иерархические промежуточные итоги. Для `GROUP BY ROLLUP(year, month, day)` PostgreSQL вычислит: итоги по (year, month, day), затем (year, month), затем (year), затем общий итог - двигаясь вверх по иерархии.

**NULL как маркер агрегации:** в строках с промежуточными итогами NULL означает «агрегировано по этому измерению». Для различия между NULL-значением данных и NULL-маркером ROLLUP/CUBE используйте функцию `GROUPING(col)` - она возвращает 1 для агрегированных строк.

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

`GROUPING SETS` позволяет явно указать, какие комбинации измерений нужно вычислить. Это эффективнее цепочки UNION ALL: PostgreSQL делает один проход по данным вместо нескольких, что особенно критично для больших таблиц.

**ROLLUP и CUBE - синтаксический сахар над GROUPING SETS:** `ROLLUP(A, B)` = `GROUPING SETS((A,B), (A), ())`. `CUBE(A, B)` = `GROUPING SETS((A,B), (A), (B), ())`. GROUPING SETS нужен, когда нужна произвольная комбинация - не иерархия и не все вариации.

FILTER: условная агрегация за один проход

До PostgreSQL 9.4 для условной агрегации использовали `SUM(CASE WHEN status='paid' THEN amount ELSE 0 END)`. Синтаксис `FILTER (WHERE ...)` делает то же самое, но читается в разы чище и работает с любой агрегатной функцией.

**FILTER vs CASE WHEN:** оба дают одинаковый результат и одинаковый execution plan в большинстве случаев. `FILTER` предпочтителен по читаемости. Оба работают за один скан таблицы - это не несколько подзапросов.

Агрегация в PostgreSQL

• **Агрегатные функции** (COUNT, SUM, AVG, STRING_AGG, ARRAY_AGG) работают над группами строк; COUNT(*) vs COUNT(col) - разница в обработке NULL
• **GROUP BY + HAVING:** GROUP BY группирует, HAVING фильтрует группы после агрегации; WHERE - до GROUP BY
• **ROLLUP (иерархия) и CUBE (все комбинации)** - синтаксический сахар над GROUPING SETS для промежуточных итогов
• **GROUPING SETS** - явные комбинации за один скан таблицы; эффективнее UNION ALL в N раз
• **FILTER (WHERE ...)** - условная агрегация для нескольких метрик с разными условиями в одном SELECT

Связанные темы

Агрегация тесно связана с оконными функциями и оптимизацией запросов.

• Оконные функции — Window functions - агрегация без GROUP BY, с доступом к каждой строке
• Индексы и планировщик — Partial indexes ускоряют агрегацию с условиями, похожими на FILTER

Вопросы для размышления

• В каких случаях CUBE генерирует избыточные группировки, и как GROUPING SETS помогает избежать лишних вычислений?
• Как функция GROUPING() помогает отличить строку с NULL-значением от строки промежуточного итога в результатах ROLLUP?
• Почему FILTER (WHERE ...) при агрегации предпочтительнее CASE WHEN ... THEN ... ELSE 0 END с точки зрения читаемости и поддержки?

Связанные уроки

• pg-06-select — Aggregation layers GROUP BY on top of basic SELECT; WHERE/HAVING distinction needs SELECT context
• pg-11-window — Window functions are aggregate functions without GROUP BY collapsing rows; aggregation is the prerequisite
• pg-09-subqueries — HAVING with subqueries is a common pattern that combines aggregation with correlated subqueries
• pg-07-joins — Most real aggregations join multiple tables before grouping; join knowledge is practically required
• db-06-sql-advanced