Комбинация JOIN с GROUP BY, HAVING и агрегатными функциями
Комбинация JOIN с GROUP BY, HAVING и агрегатными функциями позволяет получать сводную статистику по связанным данным.
Простыми словами: GROUP BY собирает строки с одинаковыми значениями в указанных колонках в одну группу, а затем вы можете применить к каждой группе агрегатную функцию (COUNT, SUM, AVG, MIN, MAX).
Аналогия: У вас есть корзина с фруктами разных видов. Вы раскладываете их по кучкам: яблоки отдельно, апельсины отдельно, бананы отдельно. Затем считаете: сколько фруктов в каждой кучке (COUNT), какой средний вес у яблок (AVG), сколько всего весят бананы (SUM).
Ключевое правило: Все колонки в SELECT, которые не являются агрегатными функциями, обязаны быть в GROUP BY.
-- Пример группировки по пользователям
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Что происходит:
-- 1. Строки группируются по u.id и u.name
-- 2. Для каждой группы считается COUNT(o.id)
-- 3. Результат: одна строка на пользователя с количеством его заказовАгрегатные функции (COUNT, SUM, AVG, MIN, MAX) часто применяются к результатам JOIN.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
amount DECIMAL(10, 2),
status VARCHAR(20)
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO orders (user_id, amount, status) VALUES
(1, 100.00, 'completed'),
(1, 200.00, 'completed'),
(1, 50.00, 'pending'),
(2, 150.00, 'completed'),
(2, 75.00, 'cancelled');-- Количество заказов на пользователя
SELECT
u.name,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Для каждого пользователя считает, сколько у него заказов. LEFT JOIN нужен, чтобы показать и тех, у кого заказов нет (будет 0).
Результат:
+---------+-------------+
| name | order_count |
+---------+-------------+
| Alice | 3 |
| Bob | 2 |
| Charlie | 0 |
+---------+-------------+
Важно: Используйте
COUNT(o.id), а неCOUNT(*)при LEFT JOIN.COUNT(*)считает все строки (включая NULL), аCOUNT(o.id)игнорирует NULL, правильно показывая 0 для пользователей без заказов.
-- Общая сумма заказов по пользователям
SELECT
u.name,
SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Суммирует все заказы каждого пользователя. Показывает только тех, у кого есть заказы (JOIN, а не LEFT JOIN).
Результат:
+-------+--------------+
| name | total_amount |
+-------+--------------+
| Alice | 350.00 |
| Bob | 225.00 |
+-------+--------------+
-- Средняя сумма заказа на пользователя
SELECT
u.name,
AVG(o.amount) AS avg_order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Вычисляет средний чек каждого пользователя (сумма заказов / количество заказов).
Можно группировать по колонкам из нескольких таблиц.
-- Количество и сумма заказов по статусам
SELECT
o.status,
COUNT(*) AS order_count,
SUM(o.amount) AS total_amount,
AVG(o.amount) AS avg_amount
FROM orders o
GROUP BY o.status;Что делает: Группирует заказы по статусу (completed, pending, cancelled) и показывает: сколько заказов каждого статуса, их общую сумму и среднюю сумму.
Результат:
+-----------+-------------+--------------+------------+
| status | order_count | total_amount | avg_amount |
+-----------+-------------+--------------+------------+
| completed | 3 | 450.00 | 150.00 |
| pending | 1 | 50.00 | 50.00 |
| cancelled | 1 | 75.00 | 75.00 |
+-----------+-------------+--------------+------------+
SELECT
u.name,
o.status,
COUNT(*) AS order_count,
SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, o.status
ORDER BY u.name, o.status;Что делает: Показывает разбивку по каждому пользователю и каждому статусу. Например: у Alice 2 завершённых заказа на 300$, 1 ожидающий на 50$.
Результат:
+---------+-----------+-------------+--------------+
| name | status | order_count | total_amount |
+---------+-----------+-------------+--------------+
| Alice | completed | 2 | 300.00 |
| Alice | pending | 1 | 50.00 |
| Bob | cancelled | 1 | 75.00 |
| Bob | completed | 1 | 150.00 |
+---------+-----------+-------------+--------------+
HAVING фильтрует результаты агрегации, в отличие от WHERE, который фильтрует строки до агрегации.
SELECT
u.name,
COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 2;Что делает: Находит пользователей, у которых больше 2 заказов. HAVING фильтрует уже после группировки.
Результат:
+-------+-------------+
| name | order_count |
+-------+-------------+
| Alice | 3 |
+-------+-------------+
SELECT
u.name,
SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.amount) > 300;Что делает: Показывает только тех пользователей, кто потратил больше $300.
-- WHERE фильтрует заказы до агрегации
-- HAVING фильтрует пользователей после агрегации
SELECT
u.name,
COUNT(o.id) AS completed_orders
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed' -- Фильтр до GROUP BY
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 2; -- Фильтр после GROUP BYЧто делает: Сначала отбирает только завершённые заказы (WHERE), затем группирует по пользователям, затем оставляет только тех, у кого таких заказов ≥ 2 (HAVING).
DISTINCT внутри агрегатных функций устраняет дубликаты.
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER
);
-- Количество уникальных пользователей, купивших каждый товар
SELECT
p.name AS product_name,
COUNT(DISTINCT o.user_id) AS unique_customers
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
GROUP BY p.id, p.name;Что делает: Для каждого товара считает, сколько разных пользователей его купили. DISTINCT нужен, чтобы один пользователь не посчитался дважды, если купил товар несколько раз.
-- Сумма уникальных сумм заказов (если есть дубликаты amount)
SELECT
u.name,
SUM(DISTINCT o.amount) AS unique_amounts_sum
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Суммирует только уникальные значения сумм заказов. Если у пользователя есть два заказа на одинаковую сумму, она посчитается только один раз.
Агрегация данных из нескольких связанных таблиц.
SELECT
u.name,
COUNT(DISTINCT o.id) AS total_orders,
SUM(o.amount) AS total_spent,
AVG(o.amount) AS avg_order,
MIN(o.amount) AS min_order,
MAX(o.amount) AS max_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Показывает всё сразу для каждого пользователя: количество заказов, общую сумму, средний чек, минимальный и максимальный заказ.
Результат:
+---------+--------------+--------------+------------+------------+------------+
| name | total_orders | total_spent | avg_order | min_order | max_order |
+---------+--------------+--------------+------------+------------+------------+
| Alice | 3 | 350.00 | 116.67 | 50.00 | 200.00 |
| Bob | 2 | 225.00 | 112.50 | 75.00 | 150.00 |
| Charlie | 0 | NULL | NULL | NULL | NULL |
+---------+--------------+--------------+------------+------------+------------+
Использование CASE внутри агрегатных функций для условной агрегации.
SELECT
u.name,
COUNT(CASE WHEN o.status = 'completed' THEN 1 END) AS completed,
COUNT(CASE WHEN o.status = 'pending' THEN 1 END) AS pending,
COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled,
COUNT(o.id) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Делает «сводную таблицу»: для каждого пользователя показывает количество заказов каждого статуса в отдельных колонках.
Результат:
+---------+-----------+---------+-----------+-------+
| name | completed | pending | cancelled | total |
+---------+-----------+---------+-----------+-------+
| Alice | 2 | 1 | 0 | 3 |
| Bob | 1 | 0 | 1 | 2 |
| Charlie | 0 | 0 | 0 | 0 |
+---------+-----------+---------+-----------+-------+
Важно:
COUNT(CASE ... THEN 1 END)считает только ненулевые значения. Дляcancelledу AliceCASEвозвращает NULL (нетELSE), поэтому не считается.
SELECT
u.name,
SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) AS completed_amount,
SUM(CASE WHEN o.status = 'pending' THEN o.amount ELSE 0 END) AS pending_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: Суммирует заказы отдельно по статусам: сколько денег принесли завершённые, сколько ожидающие.
Оконные функции работают с результатами JOIN.
SELECT
u.name,
SUM(o.amount) AS total_amount,
RANK() OVER (ORDER BY SUM(o.amount) DESC) AS spending_rank
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY spending_rank;Что делает: Показывает сумму заказов каждого пользователя и его место в рейтинге (кто больше всех потратил — тот на 1 месте).
SELECT
u.name,
o.order_date,
o.amount,
SUM(o.amount) OVER (
PARTITION BY u.id
ORDER BY o.order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.name, o.order_date;Что делает: Показывает нарастающую сумму заказов для каждого пользователя. Например, если у Alice заказы на $100, $200, $50, то running_total будет: $100, $300, $350.
-- Индекс на колонках группировки
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);Иногда эффективнее агрегировать данные до соединения:
-- Менее эффективно: агрегация после JOIN
SELECT
u.name,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Более эффективно: агрегация перед JOIN
SELECT
u.name,
COALESCE(oc.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) oc ON u.id = oc.user_id;Что делает: То же самое, что первый запрос, но эффективнее: сначала считаем заказы по каждому пользователю в подзапросе, потом присоединяем к пользователям. Меньше данных обрабатывается при группировке.
-- Опасно: JOIN может создать дубликаты
SELECT
u.name,
COUNT(oi.id) AS item_count
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name;
-- Если у пользователя несколько заказов, COUNT посчитает все items
-- Правильно: используйте COUNT(DISTINCT ...) если нужно
SELECT
u.name,
COUNT(DISTINCT oi.id) AS unique_items
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name;Что делает: Первый запрос считает все элементы заказов (может быть много). Второй — только уникальные элементы, чтобы избежать дублирования при JOIN.
-- ОШИБКА: колонка не в GROUP BY и не агрегирована
SELECT u.name, u.email, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name; -- Нет u.email!
-- Правильно:
SELECT u.name, u.email, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;Что делает: В неправильном варианте PostgreSQL выдаст ошибку, потому что email не в GROUP BY и не агрегирован. В правильном — группируем по всем неагрегированным колонкам.
В PostgreSQL можно группировать по первичному ключу:
-- Достаточно GROUP BY u.id
SELECT u.id, u.name, u.email, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;Что делает: Если есть GROUP BY u.id (первичный ключ), то остальные поля из этой таблицы можно не указывать — они однозначно определяются по ключу.
-- Неправильно: COUNT(*) считает NULL-строки
SELECT u.name, COUNT(*) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Charlie получит 1 вместо 0
-- Правильно:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Что делает: В неправильном варианте COUNT(*) считает все строки, включая те, где правая таблица (orders) содержит NULL — поэтому пользователи без заказов получат 1 вместо 0. COUNT(o.id) игнорирует NULL и правильно показывает 0.
-- Если у заказа несколько items, сумма удвоится
SELECT
u.name,
SUM(o.amount) AS total -- ОШИБКА: дублирование!
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name;
-- Правильно: агрегируйте до JOIN или используйте DISTINCT
SELECT
u.name,
SUM(DISTINCT o.amount) AS total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name;Что делает: В неправильном варианте при JOIN с order_items каждая сумма заказа умножается на количество элементов в заказе. SUM(DISTINCT o.amount) устраняет дубликаты сумм.
Далее: Подзапросы в JOIN