Агрегатные функции, GROUP BY и HAVING

Агрегатные функции, GROUP BY и HAVING

Главная идея

Как это выглядит на практике

1. Базовая агрегация: SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price, MIN(price) AS min_price, MAX(price) AS max_price FROM products GROUP BY category ORDER BY product_count DESC. Каждая строка результата — одна категория, все остальные значения — агрегаты по её товарам.
2. HAVING — фильтрация групп: SELECT category, COUNT(*) AS cnt FROM products GROUP BY category HAVING COUNT(*) >= 5. Только категории с 5+ товарами. В отличие от WHERE category = X (фильтрация строк), HAVING фильтрует уже сформированные группы.
3. WHERE + GROUP BY + HAVING вместе: SELECT user_id, SUM(total_amount) AS total FROM orders WHERE status = 'completed' AND created_at >= '2024-01-01' GROUP BY user_id HAVING SUM(total_amount) >= 10000 ORDER BY total DESC. WHERE фильтрует строки до группировки (быстрее), HAVING — группы после.
4. GROUP BY по дате с DATE_TRUNC: SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders, SUM(total_amount) AS revenue FROM orders WHERE created_at >= '2023-01-01' GROUP BY DATE_TRUNC('month', created_at) ORDER BY month. DATE_TRUNC('month', ts) округляет до начала месяца.
5. COUNT(*) vs COUNT(col): SELECT COUNT(*) AS total_rows, COUNT(phone) AS has_phone, COUNT(*) - COUNT(phone) AS no_phone FROM users. COUNT(*) — все строки, COUNT(phone) — строки где phone IS NOT NULL. Разница = количество NULL.
6. COUNT(DISTINCT col) — уникальные значения: SELECT DATE_TRUNC('day', created_at) AS day, COUNT(DISTINCT user_id) AS unique_buyers FROM orders GROUP BY 1 ORDER BY 1. Количество уникальных покупателей в день (не заказов).
7. FILTER — условная агрегация в одном запросе: SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS total_orders, COUNT(*) FILTER (WHERE status = 'completed') AS completed, COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled, SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue FROM orders GROUP BY 1 ORDER BY 1. Вместо нескольких подзапросов — один запрос.
8. STRING_AGG и ARRAY_AGG для объединения значений группы: SELECT user_id, STRING_AGG(tag, ', ' ORDER BY tag) AS tags, ARRAY_AGG(DISTINCT category ORDER BY category) AS categories FROM user_interests GROUP BY user_id. STRING_AGG склеивает строки через разделитель, ARRAY_AGG собирает в массив.
9. GROUPING SETS — несколько GROUP BY в одном запросе: SELECT region, product_type, SUM(revenue) FROM sales GROUP BY GROUPING SETS ((region, product_type), (region), (product_type), ()). Одновременно: сумма по регион+тип, по региону, по типу, и общая сумма. Эквивалентно 4 запросам с UNION ALL.
10. ROLLUP — иерархические subtotals: SELECT year, quarter, month, SUM(revenue) FROM sales GROUP BY ROLLUP (year, quarter, month). Генерирует суммы для: (year, quarter, month), (year, quarter), (year), и общий итог. Удобно для финансовых отчётов.
11. Оконные функции vs GROUP BY: GROUP BY сжимает строки до одной на группу, оконные функции добавляют агрегат не убирая строки. SELECT id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders — каждая строка заказа + сумма всех заказов пользователя. Это следующий уровень после GROUP BY.
12. Производительность: GROUP BY на большой таблице создаёт HashAggregate или GroupAggregate node в плане. HashAggregate строит хеш-таблицу в work_mem — если не хватает места, делает spill на диск. Индекс на GROUP BY столбце позволяет GroupAggregate без сортировки и без спилла. Для частых GROUP BY запросов рассмотри материализованные представления (MATERIALIZED VIEW).

Примеры кода

Аналитический отчёт продаж — реальный пример

-- Ежемесячная статистика продаж по категориям
SELECT
  DATE_TRUNC('month', o.created_at)::DATE  AS month,
  p.category,
  COUNT(DISTINCT o.id)                      AS order_count,
  COUNT(DISTINCT o.user_id)                 AS unique_buyers,
  SUM(oi.quantity)                          AS units_sold,
  SUM(oi.quantity * oi.price_at_purchase)   AS revenue,
  AVG(oi.price_at_purchase)                 AS avg_item_price,
  -- Условные агрегаты в одном запросе
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
  ROUND(
    COUNT(*) FILTER (WHERE o.status = 'completed') * 100.0
    / NULLIF(COUNT(*), 0), 1
  )                                         AS completion_rate_pct
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'
GROUP BY DATE_TRUNC('month', o.created_at), p.category
HAVING SUM(oi.quantity * oi.price_at_purchase) > 0
ORDER BY month, revenue DESC;

WHERE vs HAVING — разница в производительности

-- МЕДЛЕННО: HAVING для условий на исходные строки
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id
HAVING user_id > 1000;  -- фильтрует после группировки всей таблицы!

-- БЫСТРО: WHERE до группировки (может использовать индекс)
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE user_id > 1000   -- фильтрует ДО GROUP BY, использует индекс
GROUP BY user_id;

-- HAVING правильно использовать — для условий на агрегаты
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS total
FROM orders
WHERE created_at >= '2024-01-01'    -- WHERE: на строки
GROUP BY user_id
HAVING COUNT(*) >= 3                -- HAVING: на агрегаты
  AND SUM(total_amount) >= 5000;

-- EXPLAIN ANALYZE для сравнения планов
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, COUNT(*)
FROM orders
WHERE user_id > 1000
GROUP BY user_id;

Продвинутые агрегаты — реальные паттерны

-- Перцентили и статистика распределения
SELECT
  category,
  COUNT(*)                                  AS count,
  ROUND(AVG(price)::NUMERIC, 2)             AS mean_price,
  PERCENTILE_CONT(0.5) WITHIN GROUP
    (ORDER BY price)                        AS median_price,
  PERCENTILE_CONT(0.95) WITHIN GROUP
    (ORDER BY price)                        AS p95_price,
  MIN(price)                                AS min_price,
  MAX(price)                                AS max_price
FROM products
GROUP BY category
ORDER BY mean_price DESC;

-- Список значений группы через STRING_AGG
SELECT
  u.id,
  u.full_name,
  STRING_AGG(t.name, ', ' ORDER BY t.name) AS tags,
  ARRAY_AGG(DISTINCT t.category)           AS tag_categories
FROM users u
JOIN user_tags ut ON ut.user_id = u.id
JOIN tags t ON t.id = ut.tag_id
GROUP BY u.id, u.full_name
HAVING COUNT(t.id) >= 3;

-- ROLLUP: суммы по иерархии (год → квартал → месяц)
SELECT
  EXTRACT(year  FROM created_at) AS year,
  EXTRACT(quarter FROM created_at) AS quarter,
  EXTRACT(month FROM created_at) AS month,
  SUM(total_amount) AS revenue,
  GROUPING(EXTRACT(year FROM created_at),
           EXTRACT(quarter FROM created_at),
           EXTRACT(month FROM created_at)) AS grouping_set
FROM orders
WHERE status = 'completed'
GROUP BY ROLLUP (
  EXTRACT(year FROM created_at),
  EXTRACT(quarter FROM created_at),
  EXTRACT(month FROM created_at)
)
ORDER BY year NULLS LAST, quarter NULLS LAST, month NULLS LAST;

Что происходит под капотом

• Логический порядок выполнения GROUP BY запроса: FROM → WHERE → GROUP BY → Агрегатные функции → HAVING → SELECT → ORDER BY → LIMIT. WHERE применяется к отдельным строкам (до группировки), HAVING — к группам (после). Это ключевое отличие, влияющее на производительность: WHERE может использовать индекс, HAVING — нет.
• HashAggregate (хеш-агрегация): PostgreSQL строит хеш-таблицу группа→агрегат в memory (work_mem). Каждая новая строка вычисляет хеш группового ключа и обновляет агрегат. После обработки всех строк — выдаёт результат. При нехватке work_mem — spill на диск (EXPLAIN показывает 'Batches: N > 1'). Увеличение work_mem может избежать spill.
• GroupAggregate (последовательная агрегация): если входные строки уже отсортированы по GROUP BY ключу — PostgreSQL использует GroupAggregate: читает группу последовательно, вычисляет агрегат, переходит к следующей группе. Не требует хеш-таблицы. Индекс на GROUP BY столбце часто позволяет GroupAggregate без предварительной сортировки.
• COUNT(*) vs COUNT(expr): COUNT(*) подсчитывает все строки результата включая строки с NULL в любом столбце. COUNT(col) подсчитывает строки где col IS NOT NULL. COUNT(DISTINCT col) подсчитывает уникальные не-NULL значения col. Важно: COUNT(col) при наличии NULL строк даёт меньший результат чем COUNT(*).
• AVG игнорирует NULL: AVG(x) = SUM(x) / COUNT(x) — COUNT(x) не включает NULL строки. AVG(5, NULL, 3) = (5+3)/2 = 4, а не (5+0+3)/3 = 2.67. Если нужно включить NULL как 0: AVG(COALESCE(x, 0)). Аналогично SUM игнорирует NULL, но SUM(всех NULL) = NULL (не 0).
• FILTER (WHERE cond) — более эффективный аналог CASE WHEN: COUNT(*) FILTER (WHERE status='ok') vs COUNT(CASE WHEN status='ok' THEN 1 END). Оба работают, но FILTER читабельнее и немного эффективнее (PostgreSQL оптимизирует лучше). FILTER работает со всеми агрегатами: SUM, AVG, STRING_AGG и другими.
• GROUPING SETS — обобщение GROUP BY: GROUP BY GROUPING SETS ((a, b), (a), ()) генерирует три набора групп и объединяет результаты. ROLLUP(a, b, c) = GROUPING SETS ((a,b,c), (a,b), (a), ()). CUBE(a, b) = GROUPING SETS ((a,b), (a), (b), ()). Эффективнее нескольких UNION ALL запросов — данные читаются один раз.
• Функциональная зависимость в GROUP BY: PostgreSQL позволяет включать в SELECT столбцы не из GROUP BY, если они функционально зависят от ключа GROUP BY. GROUP BY u.id, SELECT u.name — разрешено если id PRIMARY KEY (name уникально для каждого id). Это PostgreSQL-расширение; строгий SQL-стандарт требует все неагрегированные столбцы в GROUP BY.
• Materialized views для тяжёлой агрегации: CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', created_at), SUM(amount) FROM orders GROUP BY 1. Результат сохраняется на диске. REFRESH MATERIALIZED VIEW CONCURRENTLY обновляет без блокировки чтения. Для часто используемых, редко меняющихся агрегатов — значительное ускорение (секунды → миллисекунды).
• Параллельная агрегация: PostgreSQL 10+ поддерживает параллельный GROUP BY. Несколько workers читают разные части таблицы и вычисляют локальные агрегаты, потом Gather merge объединяет. В EXPLAIN: 'Partial HashAggregate' (worker) + 'Finalize HashAggregate' (coordinator). max_parallel_workers_per_gather контролирует количество workers.
• Индексы и GROUP BY: индекс на GROUP BY столбце позволяет GroupAggregate (чтение уже отсортированных данных без Sort node). Покрывающий индекс (включающий все SELECT-столбцы) = Index Only Scan. CREATE INDEX ON orders(user_id) INCLUDE (total_amount) позволяет SELECT user_id, SUM(total_amount) FROM orders GROUP BY user_id делать Index Only Scan без heap access.

Термины урока

Мини-разбор реальной ситуации

Стартап строит аналитический дашборд. Первая реализация: Rails загружает все заказы за последний квартал (230 000 строк, 45MB), итерирует в Ruby и строит агрегаты для 12 виджетов. Время рендеринга дашборда: 18 секунд. Сервер приложений потребляет 400MB RAM на один запрос. Senior backend разработчик переписывает каждый виджет как отдельный GROUP BY запрос. Самый сложный: 12 строк SQL с DATE_TRUNC, COUNT(DISTINCT), SUM с FILTER для разных статусов. Добавляются два индекса: CREATE INDEX ON orders(created_at) WHERE status != 'cancelled' и CREATE INDEX ON orders(user_id, created_at). Результат: дашборд загружается за 340мс. 8 SQL-запросов вместо 1 с огромной выгрузкой. RAM: 2MB вместо 400MB. Урок: GROUP BY в БД — не оптимизация, это правильная архитектура.