GROUP BY и агрегаты
Агрегаты - это когда ты перестаёшь смотреть на каждую строку отдельно и начинаешь думать «в целом».
Основные агрегаты
-- COUNT: сколько строк
SELECT COUNT(*) AS users_count FROM users;
SELECT COUNT(email) AS users_with_email FROM users; -- пропускает NULL
-- 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;
COUNT(*) считает все строки, а COUNT(столбец) - только те, где в этом столбце не NULL: если из 10 пользователей у троих нет email, COUNT(*) вернёт 10, а COUNT(email) - 7. SUM, AVG, MIN и MAX тоже пропускают NULL. Что такое NULL и почему с ним не работает = - в уроке про WHERE.
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 можно только:
- Столбцы, по которым группируешь
- Агрегаты (COUNT, SUM и т.д.)
-- НЕПРАВИЛЬНО (status не в GROUP BY!):
SELECT user_id, status, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;
-- ERROR: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
-- В группе пользователя много заказов с разными status, и БД не знает, какой из них вывести
-- ПРАВИЛЬНО:
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 и найди в плане шаг с группировкой