GROUP BY и агрегаты

Агрегаты - это когда ты перестаёшь смотреть на каждую строку отдельно и начинаешь думать «в целом».

Основные агрегаты

-- COUNT: сколько строк
SELECT COUNT(*) AS users_count FROM users;
SELECT COUNT(email) AS users_with_email FROM users; - пропускает NULL, про его повадки - в уроке про [WHERE](./03-where.md)

-- SUM: сумма
SELECT SUM(total) AS revenue FROM orders;

-- AVG: среднее
SELECT AVG(total) AS avg_check FROM orders;

-- MIN/MAX: минимум и максимум
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;

-- Комбинируй:
SELECT COUNT(*) AS orders_total,
       SUM(total) AS revenue,
       AVG(total) AS avg_order,
       MIN(total) AS min_order,
       MAX(total) AS max_order
FROM orders;

GROUP BY - группировка

Вместо одной строки результата выводишь несколько (по группам):

-- Сколько заказов у каждого пользователя
SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
ORDER BY orders_count DESC;

-- Результат:
-- user_id | orders_count
-- 1       | 5
-- 2       | 3
-- 3       | 1

GROUP BY по нескольким столбцам

Если нужные поля лежат в разных таблицах, агрегат считают поверх JOIN:

-- Доход по городам и статусам (город лежит в users, сумма - в orders, поэтому JOIN)
SELECT city, status, SUM(total) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY city, status
ORDER BY revenue DESC;

-- Результат:
-- city      | status    | revenue
-- Moscow    | completed | 50000
-- Moscow    | pending   | 5000
-- SPB       | completed | 30000

Важное правило: SELECT без агрегата в GROUP BY

Если пишешь GROUP BY, то в SELECT можно только:

1. Столбцы, по которым группируешь
2. Агрегаты (COUNT, SUM и т.д.)

-- НЕПРАВИЛЬНО (name не в GROUP BY!):
SELECT user_id, name, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;
-- БД не знает, какое имя вывести (у пользователя может быть одно имя, но он в 5 группах)

-- ПРАВИЛЬНО:
SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;

-- Или если нужно имя:
SELECT user_id, u.name, COUNT(*) AS orders_count
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY user_id, u.name; - добавляем name в GROUP BY

NULL в GROUP BY

NULL считается отдельной группой:

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

-- Результат:
-- city      | users_count
-- Moscow    | 100
-- SPB       | 50
-- NULL      | 10 - пользователи без города образуют свою группу!

HAVING - фильтр для групп

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

-- WHERE фильтрует строки
SELECT user_id, COUNT(*) AS orders_count
FROM orders
WHERE total > 1000 - берём только большие заказы
GROUP BY user_id
ORDER BY orders_count DESC;

-- HAVING фильтрует группы
SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3 - выводим только пользователей с 3+ заказами
ORDER BY orders_count DESC;

-- И вместе:
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE created_at > '2024-01-01' - только свежие заказы (WHERE)
GROUP BY user_id
HAVING SUM(total) > 10000 - только заказавшие на сумму > 10k (HAVING)
ORDER BY revenue DESC;

Практические примеры

Такие отчёты гоняют регулярно, и на больших таблицах они тяжёлые. Если один и тот же расчёт нужен постоянно, его выносят в materialized view - об этом в уроке про нормализацию.

Доход по месяцам:

SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Количество пользователей в каждом городе:

SELECT city, COUNT(*) AS users_count
FROM users
WHERE deleted_at IS NULL - только активные
GROUP BY city
HAVING COUNT(*) > 5 - города с 5+ пользователями
ORDER BY users_count DESC;

Средняя стоимость заказа по статусам:

SELECT status, AVG(total) AS avg_order, COUNT(*) AS orders_count
FROM orders
GROUP BY status
ORDER BY orders_count DESC;

Мини-задание

• Выведи топ‑5 пользователей по количеству заказов
• Найди города, где больше 10 пользователей
• Посчитай среднюю стоимость заказа по статусам
• Найди пользователей, которые потратили больше 50 000 (GROUP BY + HAVING + SUM)
• Выведи месячный доход (GROUP BY дата + ORDER BY)
• Прогони любой из этих запросов через EXPLAIN и найди в плане шаг с группировкой