Глава 5 из 28
5. JOIN и агрегаты: GROUP BY, HAVING, FILTER
Данные в реляционной базе разложены по таблицам: клиенты отдельно, заказы отдельно. Чтобы получить осмысленный ответ, например «сколько потратил каждый клиент», таблицы нужно соединить (JOIN), а строки свернуть в итоги (GROUP BY и агрегатные функции). Эта глава про оба приёма и про три ловушки, в которые попадают почти все: потерянные строки в LEFT JOIN, умножение сумм после соединения и путаница между WHERE и HAVING.
Данные те же, что в прошлой главе: клиенты и заказы, плюс позиции заказов. Скрипт создания лежит в конце. У двух клиентов (Жанна и Игорь) заказов нет.
INNER JOIN: только совпавшие строки
JOIN сопоставляет строки двух таблиц по условию в ON. Обычный JOIN (он же INNER JOIN) оставляет только те строки, для которых нашлась пара:
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.name, o.id;
name | order_id | amount
-------+----------+---------
Анна | 1 | 1500.00
Анна | 2 | 320.50
Борис | 3 | 99.90
Вера | 6 | 2300.00
Глеб | 11 | 210.00
Дарья | 7 | 120.00
Короткие псевдонимы c и o сокращают запись, а префикс перед колонкой показывает, из какой она таблицы. Если колонка называется одинаково в обеих таблицах (id), префикс обязателен, иначе будет ошибка column reference "id" is ambiguous.
Егора, Жанны и Игоря в выдаче нет: оплаченных заказов у них не нашлось, пары нет, и строки исчезли.
LEFT JOIN: сохранить все строки левой таблицы
Чтобы увидеть всех клиентов, включая тех, у кого нет заказов, берут LEFT JOIN. Для клиентов без пары колонки правой таблицы заполняются NULL:
SELECT c.id, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;
id | name | order_id | amount
----+-------+----------+---------
1 | Анна | 1 | 1500.00
1 | Анна | 2 | 320.50
1 | Анна | 10 | 990.00
2 | Борис | 3 | 99.90
2 | Борис | 4 | 450.00
3 | Вера | 5 | 800.00
3 | Вера | 6 | 2300.00
4 | Глеб | 11 | 210.00
5 | Дарья | 7 | 120.00
5 | Дарья | 8 | 640.00
6 | Егор | 9 | 75.50
6 | Егор | 12 | 60.00
7 | Жанна | |
8 | Игорь | |
Жанна и Игорь попали в результат с пустыми значениями заказа. Этим пользуются, чтобы найти «сирот»: строки без связанных записей.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;
id | name
----+-------
7 | Жанна
8 | Игорь
Ловушка: условие в WHERE превращает LEFT JOIN в INNER
Хотим показать всех клиентов и их оплаченные заказы. Естественная запись приводит к потере строк:
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;
id | name | order_id
----+-------+----------
1 | Анна | 1
1 | Анна | 2
2 | Борис | 3
3 | Вера | 6
4 | Глеб | 11
5 | Дарья | 7
Жанны и Игоря снова нет. Условие o.status = 'paid' в WHERE проверяется после соединения. У клиентов без заказов o.status равен NULL, сравнение с ним неизвестно, и строка отбрасывается. Получился обычный INNER JOIN, а LEFT в запросе только вводит в заблуждение.
Условие по правой таблице нужно переносить в ON, тогда оно участвует в поиске пары, а не в фильтрации результата:
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;
id | name | order_id
----+-------+----------
1 | Анна | 1
1 | Анна | 2
2 | Борис | 3
3 | Вера | 6
4 | Глеб | 11
5 | Дарья | 7
6 | Егор |
7 | Жанна |
8 | Игорь |
Теперь все восемь клиентов на месте. У кого оплаченных заказов нет, в order_id стоит NULL.
Агрегатные функции
Агрегаты сворачивают много строк в одно значение: count, sum, avg, min, max.
SELECT count(*) AS orders_total,
sum(amount) AS revenue,
round(avg(amount), 2) AS avg_amount,
min(amount) AS min_amount,
max(amount) AS max_amount
FROM orders;
orders_total | revenue | avg_amount | min_amount | max_amount
--------------+---------+------------+------------+------------
12 | 7565.90 | 630.49 | 60.00 | 2300.00
Разница между вариантами count часто бывает важна:
count(*)считает строки.count(колонка)считает строки, где колонка неNULL.count(DISTINCT колонка)считает уникальные значения.
SELECT count(*) AS all_rows,
count(referred_by) AS with_referrer,
count(DISTINCT referred_by) AS distinct_referrers
FROM customers;
all_rows | with_referrer | distinct_referrers
----------+---------------+--------------------
8 | 3 | 2
Клиентов 8, пригласивший указан у трёх, а различных пригласивших двое (Анна и Борис). Агрегаты, кроме count(*), пропускают NULL. Поэтому avg считает среднее только по известным значениям, а sum пустого набора возвращает NULL, а не ноль:
SELECT sum(amount) AS empty_sum, coalesce(sum(amount), 0) AS safe_sum
FROM orders
WHERE status = 'refunded';
empty_sum | safe_sum
-----------+----------
| 0
В отчётах оборачивайте сумму в COALESCE, иначе вместо нуля получите пустую ячейку.
GROUP BY: итоги по группам
GROUP BY разбивает строки на группы с одинаковыми значениями и считает агрегат по каждой группе:
SELECT c.name,
count(*) AS orders_count,
sum(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total DESC;
name | orders_count | total
-------+--------------+---------
Вера | 2 | 3100.00
Анна | 3 | 2810.50
Дарья | 2 | 760.00
Борис | 2 | 549.90
Глеб | 1 | 210.00
Егор | 2 | 135.50
Правило: каждая колонка в SELECT должна либо входить в GROUP BY, либо стоять внутри агрегата. Иначе PostgreSQL откажет, потому что не знает, какое из значений в группе показать.
SELECT c.name, o.status, count(*)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
ERROR: column "o.status" must appear in the GROUP BY clause or be used in an aggregate function
Группировать можно по нескольким колонкам и по выражениям. Обороты по месяцам:
SELECT date_trunc('month', created_at)::date AS month,
count(*) AS orders_count,
sum(amount) AS total
FROM orders
GROUP BY date_trunc('month', created_at)
ORDER BY month;
month | orders_count | total
------------+--------------+---------
2026-09-01 | 4 | 2370.40
2026-10-01 | 8 | 5195.50
Если группировать по имени, а не по id, два тёзки склеятся в одну группу. В рабочих запросах включайте в GROUP BY идентификатор.
WHERE и HAVING: фильтр до и после группировки
WHERE отбирает строки до группировки, HAVING отбирает готовые группы по значению агрегата. Агрегат в WHERE писать нельзя.
SELECT c.name, sum(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'
GROUP BY c.name
HAVING sum(o.amount) > 1000
ORDER BY total DESC;
name | total
------+---------
Вера | 3100.00
Анна | 2810.50
Сначала отбрасываются отменённые заказы (WHERE), потом считаются суммы по клиентам, потом остаются те, у кого сумма больше 1000 (HAVING). Все условия, которые можно проверить до группировки, ставьте в WHERE: это быстрее, потому что сворачивать придётся меньше строк.
FILTER: несколько условных итогов в одной строке
Часто нужно посчитать одну и ту же величину по нескольким условиям в одном запросе. Для этого у агрегата есть FILTER:
SELECT c.name,
count(*) AS orders_total,
count(*) FILTER (WHERE o.status = 'paid') AS paid_count,
sum(o.amount) FILTER (WHERE o.status = 'paid') AS paid_sum,
sum(o.amount) FILTER (WHERE o.status = 'cancelled') AS cancelled_sum
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
name | orders_total | paid_count | paid_sum | cancelled_sum
-------+--------------+------------+----------+---------------
Анна | 3 | 2 | 1820.50 |
Борис | 2 | 1 | 99.90 | 450.00
Вера | 2 | 1 | 2300.00 |
Глеб | 1 | 1 | 210.00 |
Дарья | 2 | 1 | 120.00 |
Егор | 2 | 0 | | 60.00
Пустые клетки в paid_sum и cancelled_sum — это NULL: подходящих заказов у клиента нет. Тот же результат можно получить через CASE внутри агрегата, но FILTER короче и читается проще.
Ловушка: суммы умножаются после соединения
Допустим, нужно посчитать выручку по клиентам и заодно присоединить позиции заказа, чтобы узнать число товаров. У заказа может быть несколько позиций, и каждая даёт свою строку. Сумма заказа (amount) при этом повторяется на каждой строке:
SELECT o.id, o.amount, i.product
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id IN (1, 6)
ORDER BY o.id, i.product;
id | amount | product
----+---------+-------------
1 | 1500.00 | Клавиатура
1 | 1500.00 | Мышь
6 | 2300.00 | Кабель HDMI
6 | 2300.00 | Монитор
6 | 2300.00 | Подставка
У заказа 1 две позиции, и его сумма 1500 теперь на двух строках. Если после такого соединения написать sum(o.amount), она посчитается дважды:
SELECT sum(o.amount) AS wrong_sum
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id IN (1, 6);
wrong_sum
-----------
9900.00
Правильная сумма заказов 1 и 6 — 3800. А запрос показал 9900: заказ 1 попал в сумму дважды (1500 × 2), а заказ 6 трижды (2300 × 3). Это называют fan-out. Универсальное правило: прежде чем суммировать, убедитесь, что одна строка результата соответствует одной сумме. Самый надёжный способ — сначала свернуть дочернюю таблицу до одной строки на заказ, потом соединять:
SELECT o.id, o.amount, i.items_count
FROM orders o
JOIN (SELECT order_id, sum(qty) AS items_count FROM order_items GROUP BY order_id) i
ON i.order_id = o.id
WHERE o.id IN (1, 6)
ORDER BY o.id;
id | amount | items_count
----+---------+-------------
1 | 1500.00 | 3
6 | 2300.00 | 4
Здесь на каждый заказ приходится одна строка, и o.amount можно спокойно суммировать.
Частые ошибки
- Условие по правой таблице
LEFT JOINвWHERE. Оно отбрасывает строки сNULLи превращает соединение вINNER. Переносите условие вON. - Суммировать после соединения «один ко многим». Значения родительской строки умножаются на число дочерних. Сворачивайте дочернюю таблицу заранее.
- Группировать по имени вместо идентификатора. Тёзки склеятся в одну строку.
- Класть агрегат в
WHERE. Условия по агрегатам пишут вHAVING. - Не учитывать
NULLв агрегатах.count(колонка)иavgпропускаютNULL,sumпустого набора возвращаетNULL. - Забыть про условие
ON. Без него (или с неверным) получится декартово произведение: каждая строка с каждой, миллионы строк на пустом месте.
Соединения и агрегаты разобраны на заданиях в курсах «JOIN» и «Агрегация и отчётность».
Данные для примеров
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text, referred_by int);
INSERT INTO customers VALUES
(1, 'Анна', 'Москва', NULL), (2, 'Борис', 'Казань', 1), (3, 'Вера', 'Москва', 1), (4, 'Глеб', NULL, NULL),
(5, 'Дарья', 'Казань', 2), (6, 'Егор', 'Москва', NULL), (7, 'Жанна', 'Тверь', NULL), (8, 'Игорь', NULL, NULL);
CREATE TABLE orders (
id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers (id),
status text NOT NULL, amount numeric(10, 2) NOT NULL,
created_at date NOT NULL
);
INSERT INTO orders VALUES
(1, 1, 'paid', 1500.00, '2026-09-03'),
(2, 1, 'paid', 320.50, '2026-09-15'),
(3, 2, 'paid', 99.90, '2026-09-20'),
(4, 2, 'cancelled', 450.00, '2026-09-21'),
(5, 3, 'new', 800.00, '2026-10-01'),
(6, 3, 'paid', 2300.00, '2026-10-02'),
(7, 5, 'paid', 120.00, '2026-10-03'),
(8, 5, 'shipped', 640.00, '2026-10-05'),
(9, 6, 'new', 75.50, '2026-10-07'),
(10, 1, 'shipped', 990.00, '2026-10-09'),
(11, 4, 'paid', 210.00, '2026-10-10'),
(12, 6, 'cancelled', 60.00, '2026-10-11');
CREATE TABLE order_items (
order_id int NOT NULL REFERENCES orders (id), product text NOT NULL, qty int NOT NULL,
PRIMARY KEY (order_id, product)
);
INSERT INTO order_items VALUES
(1, 'Клавиатура', 1), (1, 'Мышь', 2),
(2, 'Коврик', 1),
(6, 'Монитор', 1), (6, 'Кабель HDMI', 2), (6, 'Подставка', 1),
(7, 'Мышь', 1);
Откройте остальные главы
Ещё 20 глав: внутреннее устройство, оптимизация, администрирование. Покупка разовая, доступ навсегда.