Посчитайте по каждому клиенту сумму заказов и сумму оплат так, чтобы строки не размножились после JOIN
Проверила команда Сопровод · Обновлено
Короткий ответ
Сверните каждую таблицу до одной строки на клиента и только потом соединяйте. Посчитайте SUM(amount) по client_id отдельно в orders и в payments, а результаты присоедините к clients через LEFT JOIN. Если соединить все три таблицы сразу, каждый заказ повторится столько раз, сколько у клиента оплат, и обе суммы вырастут.
Почему этот вопрос задают
Размножение строк после соединения — частая причина неверных цифр в отчетах. Запрос не падает и выдает правдоподобные суммы, поэтому важно знать, откуда берется задвоение.
Условие задачи
Оптовый поставщик стройматериалов ведет три таблицы: clients(id, name), orders(id, client_id, amount) и payments(id, client_id, amount). Бухгалтер просит сверку: по каждому клиенту сумма отгруженных заказов и сумма полученных оплат. Аналитик соединил три таблицы двумя JOIN и получил у «Каменного двора» сумму заказов 400 000 вместо 200 000. Напишите запрос с верными суммами, отсортируйте по id клиента.
Как подойти к решению
- Посчитайте, сколько строк даст соединение: заказы клиента умножатся на его оплаты
- Сверните
ordersдо одной строки на клиента черезGROUP BY client_id - Повторите то же для
paymentsи присоедините оба итога кclientsчерезLEFT JOIN - Замените
NULLна ноль для клиентов без заказов или без оплат
Решение
Каждый подзапрос возвращает одну строку на клиента, поэтому соединение уже ничего не размножит. LEFT JOIN сохраняет «Кровлю Плюс»: у нее есть аванс 40 000 и нет заказов. По затратам это одно чтение каждой таблицы и две группировки.
SELECT c.id, c.name,
COALESCE(o.total, 0) AS orders_total,
COALESCE(p.total, 0) AS paid_total
FROM clients c
LEFT JOIN (
SELECT client_id, SUM(amount) AS total FROM orders GROUP BY client_id
) o ON o.client_id = c.id
LEFT JOIN (
SELECT client_id, SUM(amount) AS total FROM payments GROUP BY client_id
) p ON p.client_id = c.id
ORDER BY c.id;id | name | orders_total | paid_total ----+---------------+--------------+------------ 1 | Каменный двор | 200000.00 | 200000.00 2 | Стройлайн | 100000.00 | 80000.00 3 | Кровля Плюс | 0 | 40000.00 (3 rows)
Что спросят следом
Можно ли исправить исходный запрос через
SUM(DISTINCT amount)?Как вывести только клиентов с долгом?
Есть ли разница, писать подзапросы в
FROMили вынести их вWITH?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Соединю три таблицы и сгруппирую по клиенту,
GROUP BYвсе соберет»Группировка складывает то, что уже размножилось на соединении. Скажите, что два заказа и две оплаты клиента дают четыре строки, и каждая сумма войдет в итог дважды
«Поставлю
DISTINCTпослеSELECT, и дубли уйдут»DISTINCTубирает одинаковые строки результата, а суммы к этому моменту уже посчитаны с повторами. Чинить нужно до группировки: свернуть каждую таблицу отдельно«Возьму
INNER JOIN, клиенты без заказов в сверке не нужны»Уточните у бухгалтера: клиент с авансом без заказов для сверки как раз важен. С
INNER JOIN«Кровля Плюс» с оплатой 40 000 пропадет из отчета«Поделю сумму заказов на число оплат, и все сойдется»
Такая поправка ломается, как только у клиента нет оплат или меняется число строк. Лечите причину: сверните каждую таблицу до одной строки на клиента до соединения
Сигналы для интервьюера
Сильный ответ
До запроса называет связь «один ко многим» у обеих таблиц и заранее говорит, во сколько раз вырастут суммы
Тревожный сигнал
Чинит запрос по шаблону, но не может посчитать, сколько строк дало двойное соединение у «Каменного двора»
Красный флаг
Не видит проблемы в суммах вдвое больше реальных и предлагает сверять их с бухгалтерией уже после выгрузки
Как еще могут спросить
- Почему после
JOINдвух таблиц сумма в отчете выросла вдвое? - Как посчитать суммы из двух дочерних таблиц, чтобы соединение не задвоило значения?
- Что такое размножение строк при соединении и как его избежать?
- Выведите для каждого клиента итог по заказам и по платежам одним запросом











