GROUP BY и агрегаты за 5 минут

Агрегатная функция сворачивает много строк в одно значение: COUNT, SUM, AVG, MIN, MAX. С GROUP BY она считается отдельно для каждой группы.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;

Правило колонок

В SELECT с GROUP BY можно выводить только колонки из GROUP BY и агрегаты. Запрос SELECT city, name, COUNT(*) ... GROUP BY city упадёт: какое из имён в группе показать, неизвестно. Исключение в PostgreSQL: если группируете по первичному ключу, можно выводить остальные колонки этой таблицы, они однозначно определены.

COUNT и NULL

• COUNT(*) считает строки.
• COUNT(col) считает строки, где col IS NOT NULL.
• COUNT(DISTINCT col) считает разные непустые значения.

Все агрегаты, кроме COUNT(*), игнорируют NULL. AVG(score) по значениям 10, NULL, 20 даст 15, а не 10. Если NULL надо считать нулём: AVG(COALESCE(score, 0)).

На пустом наборе COUNT вернёт 0, а SUM, AVG, MAX вернут NULL. Отсюда частое COALESCE(SUM(amount), 0).

WHERE против HAVING

• WHERE фильтрует строки до группировки, агрегаты в нём нельзя.
• HAVING фильтрует группы после, агрегаты можно.

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- только оплаченные строки
GROUP BY customer_id
HAVING SUM(total) > 10000;     -- только щедрые клиенты

Условие, которое не зависит от агрегата, лучше писать в WHERE: меньше строк дойдёт до группировки.

Группы с нулём

GROUP BY строит группы только из существующих строк. Чтобы увидеть категорию, в которой 0 товаров, начните с таблицы категорий и сделайте LEFT JOIN, а считайте COUNT(p.id), а не COUNT(*): у пустой группы COUNT(*) вернёт 1 (строка с NULL справа тоже строка).

FILTER и условные суммы

SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY customer_id;

FILTER есть в PostgreSQL. Переносимый вариант: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END).

Полезное

• STRING_AGG(name, ', ' ORDER BY name) склеивает строки группы.
• ARRAY_AGG(id) собирает массив.
• GROUP BY ROLLUP(city, status) добавляет промежуточные итоги.

Частые ошибки

• Агрегат в WHERE.
• COUNT(*) после LEFT JOIN там, где нужен COUNT(правая.id).
• Ожидание, что AVG учтёт NULL как 0.
• Неагрегированная колонка в SELECT без GROUP BY по ней.

Что спросят на собесе

• Чем WHERE отличается от HAVING.
• Разница COUNT(*), COUNT(col) и COUNT(DISTINCT col).
• Как агрегаты обрабатывают NULL и что вернёт SUM на пустом наборе.
• Как посчитать количество по группам, включая пустые.
• Как найти дубли по колонке.