Предварительная агрегация перед JOIN

Сведение дочерних таблиц к одной строке на заказ до JOIN

Когда у одного заказа есть несколько позиций и несколько платежей, прямое соединение таблиц через LEFT JOIN перемножает строки между собой. В итоге агрегатная функция SUM суммирует каждую позицию столько раз, сколько платежей привязано к заказу, а каждый платёж — столько раз, сколько в нём позиций.

Чтобы проследить этот механизм, подготовим тестовую базу данных в SQLite:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL
);

CREATE TABLE items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  amount INTEGER NOT NULL
);

CREATE TABLE payments (
  payment_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  amount INTEGER NOT NULL
);

INSERT INTO orders (order_id, client_id) VALUES
  (1, 10),
  (2, 10),
  (3, 20),
  (4, 30);

INSERT INTO items (item_id, order_id, amount) VALUES
  (1, 1, 100),
  (2, 1, 200),
  (3, 1, 100),
  (4, 2, 200),
  (5, 4, 400);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (10, 1, 150),
  (11, 1, 150),
  (12, 2, 200),
  (13, 3, 350);

Посмотрим на заказ order_id = 1. В таблице items для него лежат три позиции: 100, 200 и 100. Их реальная сумма равна

400. В таблице payments находятся два платежа по 150, их сумма равна
401. Если соединить эти таблицы напрямую и посчитать суммы в одной группировке, результат исказится:

SELECT o.order_id,
       SUM(i.amount) AS items_sum,
       SUM(p.amount) AS pay_sum
FROM orders o
LEFT JOIN items i ON o.order_id = i.order_id
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id = 1
GROUP BY o.order_id;

order_id	items_sum	pay_sum
1	800	900

Соединение сформировало декартово произведение строк: 3 позиции умножились на 2 платежа, получилось 6 промежуточных строк. Каждая позиция сложилась дважды (400 × 2 = 800), а каждый платёж — трижды (300 × 3 = 900).

Приём, который устраняет это искажение, называется предварительной агрегацией. Его суть — свести каждую дочернюю таблицу к гранулярности «не более одной строки на ключ» ещё до того, как таблица будет участвовать в общем соединении. Если дочерний набор сгруппирован по order_id, отношение между родительской таблицей orders и агрегированным набором становится «один к одному» (или «один к нулю», если дочерних записей нет). При таком соединении строки родителя не размножаются.

Шаблон CTE с контролем уникальности ключа

Для раздельной агрегации используют табличные выражения (WITH). Каждая независимая дочерняя сущность сворачивается в собственном блоке CTE, после чего родительская таблица соединяется с полученными витринами через LEFT JOIN.

Шаблон состоит из трёх шагов:

1. Свернуть позиции в CTE item_totals с группировкой по order_id.
2. Свернуть платежи в CTE payment_totals с группировкой по order_id.
3. В основном запросе взять строки из orders и присоединить оба CTE по ключу order_id.

Запрос выглядит так:

WITH item_totals AS (
  SELECT order_id, SUM(amount) AS items_sum
  FROM items
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount) AS pay_sum
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id,
       COALESCE(i.items_sum, 0) AS items_sum,
       COALESCE(p.pay_sum, 0) AS pay_sum
FROM orders o
LEFT JOIN item_totals i ON o.order_id = i.order_id
LEFT JOIN payment_totals p ON o.order_id = p.order_id
ORDER BY o.order_id;

order_id	items_sum	pay_sum
1	400	300
2	200	200
3	0	350
4	400	0

Корректность итоговых сумм опирается на строгое правило: соединяемый CTE обязан содержать не более одной строки на каждый ключ order_id. Убедиться в уникальности ключа внутри CTE можно отдельным проверочным запросом:

WITH item_totals AS (
  SELECT order_id, SUM(amount) AS items_sum
  FROM items
  GROUP BY order_id
)
SELECT order_id, COUNT(*)
FROM item_totals
GROUP BY order_id
HAVING COUNT(*) > 1;

order_id	COUNT(*)

Запрос возвращает пустой набор. Это подтверждает, что в item_totals нет дубликатов по столбцу order_id для текущих данных. Пустой результат доказывает уникальность ключа соединения, но сам по себе не гарантирует полноту выборки или правильность расчёта формул внутри агрегатов.

Обработка заказов без дочерних строк и применение COALESCE

По входному контракту нашей схемы суммы amount в таблицах items и payments задаются целыми числами, а ограничение NOT NULL запрещает пропуск суммы у созданной строки. Однако в итоговом отчёте присутствуют заказы, у которых дочерние записи полностью отсутствуют:

• У заказа 3 есть платежи на сумму 350, но нет ни одной позиции в items.
• У заказа 4 есть позиция на сумму 400, но нет ни одной записи в payments.

Когда для заказа 3 выполняется группировка таблицы items, строка с order_id = 3 просто не попадает в CTE item_totals. При последующем соединении orders LEFT JOIN item_totals для заказа 3 не находится пары справа. По правилам LEFT JOIN все столбцы неприсоединённой таблицы заполняются значениями NULL. Поэтому столбец i.items_sum становится равен NULL.

Значение NULL здесь выражает именно отсутствие дочерних строк, а не неизвестное число в исходной таблице. Для аналитического отчёта сумма позиций отсутствующего списка равна нулю. Чтобы заменить маркер отсутствия на число, используется функция COALESCE(i.items_sum, 0).

COALESCE возвращает первый аргумент, отличный от NULL. Если агрегат вернул число (как 400 для заказа 1), оно остаётся без изменений. Если строки не было и в выборку пришёл NULL (как для заказа 3), функция подставляет 0. Без этой замены заказ 3 вернул бы NULL в столбце items_sum, что привело бы к получению NULL при любых дальнейших арифметических операциях над суммами заказа.

Границы применимости: почему SUM(DISTINCT) искажает итоги

Популярная попытка обойти размножение строк без использования CTE — вызов SUM(DISTINCT amount) внутри общего соединения. Предполагается, что ключевое слово DISTINCT отбросит размножившиеся копии строк.

Проверим этот приём на заказе 1, выполнив запрос с DISTINCT:

SELECT o.order_id,
       SUM(DISTINCT i.amount) AS distinct_items_sum,
       SUM(DISTINCT p.amount) AS distinct_pay_sum
FROM orders o
LEFT JOIN items i ON o.order_id = i.order_id
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id = 1
GROUP BY o.order_id;

order_id	distinct_items_sum	distinct_pay_sum
1	300	150

Результат оказался неверным по обоим столбцам: вместо 400 и 300 запрос вернул 300 и 150.

Причина сбоя заключается в том, что DISTINCT удаляет повторяющиеся значения чисел, а не дубликаты строк соединения:

• В таблице items для заказа 1 лежат три реальные строки: 100, 200 и 100. Число 100 встречается дважды в независимых позициях. SUM(DISTINCT i.amount) оставил только уникальный набор чисел {100, 200} и сложил их один раз, получив 300 вместо 400.
• В таблице payments для заказа 1 внесены два платежа: 150 и 150. Значение 150 одно, поэтому SUM(DISTINCT p.amount) вернул 150 вместо 300.

SUM(DISTINCT amount) может случайно совпасть с правильным результатом — например, если все исходные значения заказа различны или дублируются только нули. Однако это совпадение не гарантирует корректности на других данных: любые повторяющиеся ненулевые суммы теряются. В общем случае SUM(DISTINCT ...) не устраняет размножение строк от соединения, поэтому надёжным решением остаётся предварительная агрегация.