re:re:actable
Урок 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 с агрегатами.

Документация

PostgreSQL: Aggregate Functions, PostgreSQL: Joined Tables.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам