Витрина заказов в SQL: объединяем платежи, товары и доставки

Для карточки заказа нужны стоимость товаров, оплаченная сумма и состояние доставок. У заказа может быть несколько строк, платежей и отправлений. Прямое соединение всех деталей создаёт произведение строк: две позиции, два платежа и две доставки превращаются в восемь записей. Рабочую витрину надо собрать с явной единицей «одна строка на заказ».

Определите показатели каждой детали

Товарная часть даёт количество строк и сумму заказа. Платежи дают подтверждённую оплаченную сумму. Доставки — число отправлений и число доставленных. У каждого источника свой ключ и собственные фильтры. Например, неуспешный платёж не входит в оплату, хотя его запись полезна для диагностики.

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

Сначала агрегируйте до заказа

Соберите отдельные CTE для товаров, успешных платежей и доставок. Каждый возвращает не больше одной строки на order_id. Затем присоедините их к списку orders через LEFT JOIN. Отсутствие детали не удаляет заказ из витрины.

Пример выполняется в SQLite. Товары уже содержат денежную сумму строки, платежи положительные и имеют статус, отправление хранится одной текущей строкой. Для журнала смены статусов доставки сначала потребуется выбрать актуальную запись каждого отправления.

WITH orders(id) AS (VALUES (1),(2),(3)),
items(order_id,line_id,amount) AS (VALUES (1,1,100),(1,2,200),(2,1,50)),
payments(id,order_id,amount,status) AS (
 VALUES (1,1,100,'paid'),(2,1,150,'paid'),(3,2,50,'failed')
), shipments(id,order_id,status) AS (VALUES (1,1,'delivered'),(2,1,'in_transit')),
i AS (SELECT order_id,COUNT(*) AS lines,SUM(amount) AS amount FROM items GROUP BY order_id),
p AS (SELECT order_id,SUM(amount) AS paid FROM payments WHERE status='paid' GROUP BY order_id),
s AS (SELECT order_id,COUNT(*) AS shipments,
 SUM(CASE WHEN status='delivered' THEN 1 ELSE 0 END) AS delivered FROM shipments GROUP BY order_id)
SELECT o.id,COALESCE(i.lines,0) AS item_lines,i.amount AS item_amount,
 COALESCE(p.paid,0) AS paid,COALESCE(s.shipments,0) AS shipments,
 COALESCE(s.delivered,0) AS delivered
FROM orders o LEFT JOIN i ON i.order_id=o.id LEFT JOIN p ON p.order_id=o.id
LEFT JOIN s ON s.order_id=o.id ORDER BY o.id;

Различайте отсутствие данных и нулевой результат

У заказа 1 две позиции на общую сумму 300, подтверждено 250, создано две доставки, одна завершена. Заказ 2 стоит 50, но его единственный платёж неуспешен, поэтому оплачено ноль. Заказ 3 пока не имеет товарных строк.

Для заказа 3 item_amount оставляем NULL, а item_lines равен нулю. Это позволяет отличить неподготовленный состав заказа от заказа с подтверждённой нулевой стоимостью. Для успешных платежей отсутствие строк в данном примере означает нулевую оплату; такое правило должно соответствовать полноте источника.

Поставьте инварианты витрины

Число строк результата должно совпасть с числом заказов выбранного периметра. order_id должен быть уникальным. Сумма подтверждённых платежей по витрине должна совпасть с теми же платежами в исходнике. Сумма товаров сверяется отдельно, без неготовых составов.

Добавьте два одинаковых по размеру платежа и убедитесь, что оба учтены. Добавьте новую доставку: стоимость заказа и оплата не должны измениться. Удалите все платежи одного заказа: его строка обязана сохраниться. Эти тесты быстро показывают, не нарушилась ли детализация.

Переплата не всегда ошибка запроса: это может быть аванс, возврат, ещё не отражённый в системе, или повторное списание. Витрина должна сохранить факт и дать возможность открыть исходные платежи. Бизнес-статус оплаты вычисляйте отдельным правилом.

После такой подготовки одну и ту же витрину можно использовать для отчёта поддержки, контроля незавершённых заказов и управленческой сводки. Добавление нового показателя начинается с вопроса, на каком уровне он считается, и заканчивается сверкой независимого источника.

От запроса к рабочей витрине

В платном курсе вы разберёте дубли после JOIN и предварительную агрегацию, затем соберёте аналитическую витрину интернет-магазина. Собственные решения проходят автоматическую проверку на нескольких наборах данных. Это прямое продолжение задачи: объединить независимые детали заказа и сохранить точные денежные итоги.

Откройте программу платного курса «Продвинутый SQL: сложные запросы и задачи для собеседований».

Продолжить разбор: Частичные возвраты в SQL: как найти заказ, которому вернули больше оплаты.