Урок 043 минуты чтения
Как агрегировать данные после LEFT JOIN
На этой странице
Кратко
Агрегация после LEFT JOIN нужна, когда в отчёт должны попасть все строки левой таблицы, включая те, у которых нет связанных записей (пользователи без заказов, категории без товаров). Три правила: (1) считать по столбцу правой таблицы — count(o.id), а не count(*); (2) оборачивать sum/avg в COALESCE, потому что для строк без пары они вернут NULL; (3) условия на правую таблицу ставить в ON (или в FILTER), а не в WHERE. Если присоединяется несколько one-to-many таблиц, каждую агрегируют отдельно до соединения, иначе суммы умножатся.
Вопрос
Как правильно агрегировать данные после LEFT JOIN?
Базовый шаблон
SELECT
u.id,
u.email,
count(o.id) AS orders_count,
COALESCE(sum(o.total), 0) AS revenue,
max(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY revenue DESC;Условие на правую таблицу
-- ✗ пользователи без оплаченных заказов исчезнут
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
-- ✓ вариант 1 — в ON
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
-- ✓ вариант 2 — FILTER, когда нужны разные срезы
SELECT u.id,
count(o.id) FILTER (WHERE o.status = 'paid') AS paid,
count(o.id) FILTER (WHERE o.status = 'cancelled') AS cancelled
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;Несколько связанных таблиц
-- ✓ агрегировать ветки отдельно
WITH order_stats AS (
SELECT user_id, count(*) AS orders, sum(total) AS revenue
FROM orders GROUP BY user_id
),
review_stats AS (
SELECT user_id, count(*) AS reviews, avg(rating) AS avg_rating
FROM reviews GROUP BY user_id
)
SELECT u.id,
COALESCE(os.orders, 0) AS orders,
COALESCE(os.revenue, 0) AS revenue,
COALESCE(rs.reviews, 0) AS reviews,
rs.avg_rating
FROM users u
LEFT JOIN order_stats os ON os.user_id = u.id
LEFT JOIN review_stats rs ON rs.user_id = u.id;Почему нельзя соединить сразу — в уроке о дублях от JOIN.
Категории и количество товаров
SELECT c.id, c.name, count(p.id) AS products
FROM categories c
LEFT JOIN products p ON p.category_id = c.id AND p.is_published
GROUP BY c.id, c.name;Частые ошибки
count(*)вместоcount(right.id).sumбезCOALESCE—NULLв ответе API и в арифметике.- Фильтр по правой таблице в
WHERE. - Несколько
LEFT JOINк one-to-many таблицам и общийGROUP BY. avg(COALESCE(rating, 0))— пользователи без отзывов искусственно понижают среднее.
Что важно помнить
LEFT JOIN+GROUP BY— способ получить отчёт по всем сущностям.- Счёт — по PK правой таблицы, суммы — с
COALESCE. - Несколько веток — предварительная агрегация.
Мини-шпаргалка
count(o.id).COALESCE(sum(o.x), 0).- Условие — в
ONилиFILTER. - Несколько 1:N — CTE с агрегатами.
Документация
Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.