JOIN трёх таблиц: почему отчёт показывает 36 000 вместо 12 000?

Один заказ, две товарные строки и три платёжные попытки. Таблицы соединены без синтаксической ошибки, но суммы уже не принадлежат заказу.

SQL Studio / Разборы SQL / JOIN трёх таблиц: почему отчёт показывает 36 000 вместо 12 000?

Чтобы соединить три таблицы SQL и сохранить итог по заказам, сначала определите, что означает одна строка каждой таблицы. В нашем случае orders — один заказ, sample_lines — одна товарная позиция, sample_payments — одна попытка оплаты. Обе последние таблицы имеют связь «многие к одному» с заказом, а значит два последовательных JOIN могут сочетать каждую позицию с каждой попыткой. Для отчёта «одна строка на заказ» сначала сгруппируйте позиции и captured-платежи независимо, затем присоедините оба итога.

Сначала фиксируем гранулярность

Заказ 1001: две товарные строки по 6 000 копеек, две успешные оплаты на 3 000 и 9 000 и одна неудачная на 12 000. Заказ 1002: одна товарная строка на 8 000 с количеством 2 и одна captured-оплата на 8 000. У 1003 пока нет ни позиций, ни оплат. Здесь line_kopecks — сумма всей товарной строки, а не цена единицы: две штуки заказа 1002 вместе стоят 8 000 копеек, повторно умножать эту сумму на quantity нельзя. Это три разных зерна; количество строк оплаты нельзя считать числом заказов.

Неправильный отчёт: 2 позиции × 3 события

Популярная попытка — присоединить оба дочерних набора, а затем суммировать столбцы. Ошибка логическая, SQL исполнится:

WITH sample_orders(order_id, customer, expected_kopecks) AS (
  VALUES (1001, 'Анна', 12000),
         (1002, 'Борис', 8000),
         (1003, 'Вера', 0)
),
sample_lines(line_id, order_id, quantity, line_kopecks) AS (
  VALUES (11, 1001, 1, 6000),
         (12, 1001, 1, 6000),
         (13, 1002, 2, 8000)
),
sample_payments(payment_id, order_id, status, amount_kopecks) AS (
  VALUES (21, 1001, 'captured', 3000),
         (22, 1001, 'captured', 9000),
         (23, 1001, 'failed', 12000),
         (24, 1002, 'captured', 8000)
)
SELECT o.order_id, COUNT(*) AS joined_rows,
       SUM(COALESCE(l.line_kopecks, 0)) AS items_wrong,
       SUM(CASE WHEN p.status = 'captured'
                THEN p.amount_kopecks ELSE 0 END) AS paid_wrong
FROM sample_orders AS o
LEFT JOIN sample_lines AS l ON l.order_id = o.order_id
LEFT JOIN sample_payments AS p ON p.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;
Итог с размноженными строками
order_idjoined_rowsitems_wrongpaid_wrong
100163600024000
1002180008000
1003100

Для заказа 1001 шесть строк: каждая из двух позиций сопоставлена с тремя попытками. Поэтому сумма позиций стала 36 000 вместо 12 000, а captured-оплаты — 24 000 вместо 12 000 копеек. Статусный фильтр в CASE исключил сумму failed, но не предотвратил повтор каждого captured-платежа. Для заказа 1003 COUNT(*) = 1 отражает сохранённую LEFT JOIN строку, а не существующую позицию или оплату.

Правильный отчёт: каждый поток агрегируем до order_id

В lines_by_order считаем позиции и количество штук, в paid_by_order — только captured-платежи. После GROUP BY order_id каждый из этих двух CTE содержит не более одной строки на заказ. Главная таблица и LEFT JOIN сохраняют заказ без событий.

WITH sample_orders(order_id, customer, expected_kopecks) AS (
  VALUES (1001, 'Анна', 12000),
         (1002, 'Борис', 8000),
         (1003, 'Вера', 0)
),
sample_lines(line_id, order_id, quantity, line_kopecks) AS (
  VALUES (11, 1001, 1, 6000),
         (12, 1001, 1, 6000),
         (13, 1002, 2, 8000)
),
sample_payments(payment_id, order_id, status, amount_kopecks) AS (
  VALUES (21, 1001, 'captured', 3000),
         (22, 1001, 'captured', 9000),
         (23, 1001, 'failed', 12000),
         (24, 1002, 'captured', 8000)
),
lines_by_order AS (
  SELECT order_id,
         SUM(quantity) AS units,
         SUM(line_kopecks) AS items_kopecks
  FROM sample_lines
  GROUP BY order_id
),
paid_by_order AS (
  SELECT order_id, SUM(amount_kopecks) AS paid_kopecks
  FROM sample_payments
  WHERE status = 'captured'
  GROUP BY order_id
)
SELECT o.order_id,
       COALESCE(l.units, 0) AS units,
       COALESCE(l.items_kopecks, 0) AS items_kopecks,
       COALESCE(p.paid_kopecks, 0) AS paid_kopecks
FROM sample_orders AS o
LEFT JOIN lines_by_order AS l ON l.order_id = o.order_id
LEFT JOIN paid_by_order AS p ON p.order_id = o.order_id
ORDER BY o.order_id;
Один заказ — одна строка: настоящие суммы и количество
order_idunitsitems_kopeckspaid_kopecks
100121200012000
1002280008000
1003000

Теперь у 1001 ровно 2 штуки и 12 000 копеек как по позициям, так и по captured-платежам. 1002 имеет две штуки, хотя строка позиции одна. Для 1003 показываем нули через COALESCE после агрегации; это бизнес-решение «нет связанных сумм = 0», а не изменение исходных значений NULL.

Проверка до расчёта: сколько строк создал JOIN?

Не ориентируйтесь только на итоговые суммы. Сравним физические строки результата, уникальные номера товарных строк и число платёжных попыток:

WITH sample_orders(order_id, customer, expected_kopecks) AS (
  VALUES (1001, 'Анна', 12000),
         (1002, 'Борис', 8000),
         (1003, 'Вера', 0)
),
sample_lines(line_id, order_id, quantity, line_kopecks) AS (
  VALUES (11, 1001, 1, 6000),
         (12, 1001, 1, 6000),
         (13, 1002, 2, 8000)
),
sample_payments(payment_id, order_id, status, amount_kopecks) AS (
  VALUES (21, 1001, 'captured', 3000),
         (22, 1001, 'captured', 9000),
         (23, 1001, 'failed', 12000),
         (24, 1002, 'captured', 8000)
)
SELECT o.order_id,
       COUNT(l.line_id) AS counted_lines,
       COUNT(DISTINCT l.line_id) AS actual_lines,
       COUNT(DISTINCT p.payment_id) AS payment_attempts,
       COUNT(*) AS joined_rows
FROM sample_orders AS o
LEFT JOIN sample_lines AS l ON l.order_id = o.order_id
LEFT JOIN sample_payments AS p ON p.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;
Контроль размножения строк на трёх таблицах
order_idcounted_linesactual_linespayment_attemptsjoined_rows
10016236
10021111
10030001

COUNT(l.line_id) дал 6 для заказа 1001, хотя уникальных line_id всего 2. COUNT(DISTINCT p.payment_id) = 3 — число попыток, не число оплат: статус здесь специально не фильтруется. На пустом заказе COUNT(*) = 1, но оба счётчика непустых дочерних ключей равны 0. Такой контроль помогает понять, на каком шаге отчёт потерял детализацию.

Почему SUM(DISTINCT сумма) — плохая заплатка

Две законные строки товара могут иметь одинаковую стоимость. Для заказа 1001 оба значения равны 6 000; DISTINCT удалит одно из них и создаст недосчёт:

WITH sample_orders(order_id, customer, expected_kopecks) AS (
  VALUES (1001, 'Анна', 12000),
         (1002, 'Борис', 8000),
         (1003, 'Вера', 0)
),
sample_lines(line_id, order_id, quantity, line_kopecks) AS (
  VALUES (11, 1001, 1, 6000),
         (12, 1001, 1, 6000),
         (13, 1002, 2, 8000)
),
sample_payments(payment_id, order_id, status, amount_kopecks) AS (
  VALUES (21, 1001, 'captured', 3000),
         (22, 1001, 'captured', 9000),
         (23, 1001, 'failed', 12000),
         (24, 1002, 'captured', 8000)
)
SELECT o.order_id,
       COALESCE(SUM(DISTINCT l.line_kopecks), 0) AS items_lost
FROM sample_orders AS o
LEFT JOIN sample_lines AS l ON l.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;
SUM(DISTINCT) теряет одну законную позицию
order_iditems_lost
10016000
10028000
10030

Решение не в угадывании уникальности сумм. Уникальными должны быть идентификаторы фактов: line_id, payment_id, а отчёт нужно собирать на заданном зерне order_id. Если оплаченная сумма и стоимость позиций должны сходиться, сравнивайте их уже после независимой агрегации и с оговорённым статусным правилом.

Практика

Добавьте к заказу 1001 ещё одну captured-оплату на 1 000 копеек. В каком запросе вы увидите изменение суммы ровно на 1 000, а в каком — на 2 000? Проверьте самостоятельно в бесплатной песочнице. Для следующего шага прочитайте как учитывать возвраты без JOIN-фан-аута, как различать строки и уникальные ключи и базовый случай с двумя таблицами.

В задаче P116 «Сверка товаров в заказе» нужно сохранить валовую сумму заказа при соединении с товарными позициями. Это прямой способ проверить идею независимой предагрегации.

Задача с автоматической проверкой: P060 «Три таблицы без ловушки». Примените контроль зерна и количества строк после JOIN на реальной учебной базе, а не только на самодостаточном примере статьи.


Для самостоятельной практики здесь достаточно бесплатного SQLite-тренажёра. Если нужен отдельный последовательный курс на MySQL 8, а не на SQLite, можно посмотреть курс SQL для аналитика на Stepik: функции и некоторые детали синтаксиса у движков отличаются.