Размножение строк при JOIN

Мира · маркетолог магазина настолок «Кубик»

Я сама собрала отчёт «выручка по службам доставки», JOIN-ы как ты учил. Вышло 23,2 млн. А финансисты говорят, что выручка без отмен 21,9 млн. Откуда у меня лишние деньги?

В прошлом уроке мы заметили, что клиент с пятью заказами превращается после JOIN в пять строк. Пока мы считали заказы, это было нормально. Сегодня разбираемся, что происходит, когда после такого соединения что-то суммируют.

Ксерокс в бухгалтерии

Представь, что бухгалтер складывает чеки за месяц. К каждому чеку степлером прикреплены квитанции курьера. Обычно одна, но если курьер не застал клиента и приезжал дважды, квитанций две. Бухгалтер решил, что так неудобно, и сделал ксерокопию каждого чека для каждой квитанции. Теперь чек с двумя доставками лежит в стопке дважды, и сумма месяца выросла, хотя денег не прибавилось.

Это размножение строк, по-английски fan-out. JOIN делает строку на каждую пару, и если у заказа две доставки, все его позиции повторятся дважды.

Ловим на одном заказе

Начнём с подозреваемого. Найдём заказы, у которых в deliveries больше одной строки:

SELECT order_id, count(*) AS attempts
FROM deliveries
GROUP BY order_id
HAVING count(*) > 1
ORDER BY order_id
LIMIT 3;

Таких заказов 418, у каждого две попытки: первая failed, вторая delivered (у четырёх она ещё in_transit). Возьмём первый из них, заказ 38, и посмотрим, во что он превращается после соединения:

SELECT o.id, oi.product_id, oi.price, d.attempt, d.status
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN deliveries d   ON d.order_id  = o.id
WHERE o.id = 38
ORDER BY oi.product_id, d.attempt;

 id | product_id | price   | attempt | status
----+------------+---------+---------+-----------
 38 |          2 | 2440.00 |       1 | failed
 38 |          2 | 2440.00 |       2 | delivered
 38 |          5 | 1270.00 |       1 | failed
 38 |          5 | 1270.00 |       2 | delivered

В заказе две игры на 3710 ₽. После соединения с доставками каждая игра встретилась с каждой попыткой: 2 × 2 = 4 строки. Сумма price по ним 7420 ₽, ровно вдвое больше. Вот и вся загадка.

Сколько лишнего и где

У 418 заказов с повторной доставкой 678 позиций на 1 403 509 ₽. Столько отчёт Миры и посчитал дважды. А ещё 74 283 ₽ потерялись: у 12 новых заказов доставки пока нет, и INNER JOIN их выбросил. Две ошибки разного знака, и итог всё равно не сходится с финансистами.

Общее правило: если таблица в JOIN связана с твоей строкой как один-ко-многим, строки умножаются. Безопасны соединения «многие-к-одному»: заказ с клиентом, позиция с игрой. У заказа один клиент, лишних строк не появится. Опасны соединения в сторону «многих»: заказ с позициями, с доставками, с возвратами.