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_id | joined_rows | items_wrong | paid_wrong |
|---|---|---|---|
| 1001 | 6 | 36000 | 24000 |
| 1002 | 1 | 8000 | 8000 |
| 1003 | 1 | 0 | 0 |
Для заказа 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_id | units | items_kopecks | paid_kopecks |
|---|---|---|---|
| 1001 | 2 | 12000 | 12000 |
| 1002 | 2 | 8000 | 8000 |
| 1003 | 0 | 0 | 0 |
Теперь у 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_id | counted_lines | actual_lines | payment_attempts | joined_rows |
|---|---|---|---|---|
| 1001 | 6 | 2 | 3 | 6 |
| 1002 | 1 | 1 | 1 | 1 |
| 1003 | 0 | 0 | 0 | 1 |
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;
| order_id | items_lost |
|---|---|
| 1001 | 6000 |
| 1002 | 8000 |
| 1003 | 0 |
Решение не в угадывании уникальности сумм. Уникальными должны быть идентификаторы фактов: line_id, payment_id, а отчёт нужно собирать на заданном зерне order_id. Если оплаченная сумма и стоимость позиций должны сходиться, сравнивайте их уже после независимой агрегации и с оговорённым статусным правилом.
Практика
Добавьте к заказу 1001 ещё одну captured-оплату на 1 000 копеек. В каком запросе вы увидите изменение суммы ровно на 1 000, а в каком — на 2 000? Проверьте самостоятельно в бесплатной песочнице. Для следующего шага прочитайте как учитывать возвраты без JOIN-фан-аута, как различать строки и уникальные ключи и базовый случай с двумя таблицами.
Задача с автоматической проверкой: P060 «Три таблицы без ловушки». Примените контроль зерна и количества строк после JOIN на реальной учебной базе, а не только на самодостаточном примере статьи.
Для самостоятельной практики здесь достаточно бесплатного SQLite-тренажёра. Если нужен отдельный последовательный курс на MySQL 8, а не на SQLite, можно посмотреть курс SQL для аналитика на Stepik: функции и некоторые детали синтаксиса у движков отличаются.