Сверка заказов и оплат в SQL: почему после JOIN 120 ₽ стали 360 ₽

Сверяем стоимость заказа и captured-платежи в целых копейках, не теряя нулевые, частичные и лишние операции.

SQL Studio / Разборы SQL / Сверка заказов и оплат

Заказ на 120 ₽ внезапно превращается в 360 ₽ после JOIN. Это не ошибка кассы: у заказа несколько попыток оплаты, а сумма заказа повторяется в каждой строке соединения. Для сверки сравнивайте одну сумму заказа с суммой только успешных платежей captured, предварительно сведённых до одной строки на order_id. Разница в целых копейках позволяет отличить недоплату, переплату и отсутствие успешного платежа.

Данные: шесть заказов и восемь попыток

Таблица orders имеет зерно «один заказ», payments — «одна попытка». Это самодостаточные WITH ... VALUES: каждый пример можно выполнить отдельно в бесплатной SQL-песочнице, не меняя учебную базу. Везде одна валюта — рубли, суммы хранятся в INTEGER копейках. Статус captured означает успешное списание; failed и pending не являются поступлением.

Исходные заказы
order_idк оплате, коп.
10112000
1025000
1037000
1041500
1053000
1060

В попытках: для заказа 101 — captured 7000, captured 3000 и failed 2000; для 102 — pending 5000; для 103 — captured 8000; для 104 — captured 1500; у 105 попыток нет; для 106 — успешная нулевая операция. Есть также успешный платёж 400 копеек с order_id=999, которого нет в списке заказов: такой случай нельзя молча забыть.

1. Неверная сверка: SUM удвоила стоимость заказа

WITH orders(order_id,due_kopecks) AS (
  VALUES (101,12000),(102,5000),(103,7000),(104,1500),(105,3000),(106,0)
), payments(payment_id,order_id,status,amount_kopecks) AS (
  VALUES (1,101,'captured',7000),(2,101,'captured',3000),
         (3,101,'failed',2000),(4,102,'pending',5000),
         (5,103,'captured',8000),(6,104,'captured',1500),
         (7,106,'captured',0),(8,999,'captured',400)
)
SELECT o.order_id, SUM(o.due_kopecks) AS wrong_due,
       SUM(CASE WHEN p.status='captured' THEN p.amount_kopecks ELSE 0 END) AS captured,
       COUNT(p.payment_id) AS attempts
FROM orders o LEFT JOIN payments p ON p.order_id=o.order_id
GROUP BY o.order_id ORDER BY o.order_id;
Результат неправильного JOIN, все суммы в копейках
order_idwrong_duecapturedattempts
10136000100003
102500001
103700080001
104150015001
105300000
106001

У 101 три платежные попытки, поэтому SUM(o.due_kopecks) считает 12000 × 3 = 36000. Для captured условие CASE здесь отдельно отсекает failed, однако то, что одна колонка получилась верной, не делает весь запрос корректным. COUNT(p.payment_id) у 105 правильно равен нулю; COUNT(*) при LEFT JOIN дал бы фиктивную единицу.

2. Правильная сверка: сумма captured до JOIN

WITH orders(order_id,due_kopecks) AS (
  VALUES (101,12000),(102,5000),(103,7000),(104,1500),(105,3000),(106,0)
), payments(payment_id,order_id,status,amount_kopecks) AS (
  VALUES (1,101,'captured',7000),(2,101,'captured',3000),
         (3,101,'failed',2000),(4,102,'pending',5000),
         (5,103,'captured',8000),(6,104,'captured',1500),
         (7,106,'captured',0),(8,999,'captured',400)
), captured_by_order AS (
  SELECT order_id,SUM(amount_kopecks) AS captured_kopecks
  FROM payments WHERE status='captured' GROUP BY order_id
), attempts_by_order AS (
  SELECT order_id,COUNT(*) AS attempts_n
  FROM payments GROUP BY order_id
)
SELECT o.order_id,o.due_kopecks,
       COALESCE(c.captured_kopecks,0) AS captured_kopecks,
       COALESCE(c.captured_kopecks,0)-o.due_kopecks AS delta_kopecks,
       CASE WHEN a.attempts_n IS NULL THEN 'no_attempts'
            WHEN c.order_id IS NULL THEN 'no_capture'
            WHEN c.captured_kopecks<o.due_kopecks THEN 'underpaid'
            WHEN c.captured_kopecks>o.due_kopecks THEN 'overpaid'
            ELSE 'matched' END AS result
FROM orders o
LEFT JOIN captured_by_order c ON c.order_id=o.order_id
LEFT JOIN attempts_by_order a ON a.order_id=o.order_id
ORDER BY o.order_id;
Сверка по заказам, точный результат SQLite
order_iddue_kopeckscaptured_kopecksdelta_kopecksresult
1011200010000-2000underpaid
10250000-5000no_capture
103700080001000overpaid
104150015000matched
10530000-3000no_attempts
106000matched

Знак дельты: captured − к оплате. Для 101 получаем −2000 коп. (недоплата 20 ₽), для 103 +1000 коп. (переплата 10 ₽). У 102 попытка есть, но она pending, поэтому результат no_capture; у 105 попыток вообще не было — no_attempts. Код matched у нулевого 106 означает лишь совпадение сумм, а не подтверждение реальной продажи.

Здесь captured_by_order имеет максимум одну строку на заказ. attempts_by_order нужна только для различения «не было попыток» и «попытки были, но неуспешны». Если для бизнеса различие не нужно, этот дополнительный CTE можно убрать. Не заменяйте SUM на SUM(DISTINCT amount_kopecks): две разные успешные частичные оплаты на одинаковую сумму тоже должны обе попасть в итог.

3. Платёж без заказа: проверяем другую сторону связи

WITH orders(order_id) AS (VALUES(101),(102),(103),(104),(105),(106)),
payments(payment_id,order_id,status,amount_kopecks) AS (
  VALUES (1,101,'captured',7000),(2,101,'captured',3000),
         (3,101,'failed',2000),(4,102,'pending',5000),
         (5,103,'captured',8000),(6,104,'captured',1500),
         (7,106,'captured',0),(8,999,'captured',400)
)
SELECT p.order_id, COUNT(*) AS orphan_captures,
       SUM(p.amount_kopecks) AS orphan_kopecks
FROM payments p LEFT JOIN orders o ON o.order_id=p.order_id
WHERE p.status='captured' AND o.order_id IS NULL
GROUP BY p.order_id ORDER BY p.order_id;
Успешные платежи без соответствующего заказа
order_idorphan_capturesorphan_kopecks
9991400

Первый отчёт начинался с заказов, поэтому order_id=999 там отсутствует. Антисверка от таблицы payments находит 400 копеек, не привязанные ни к одному заказу этой выгрузки. Это может быть проблема целостности, разные периоды экспорта или отсутствующий заказ; одного результата SQL недостаточно, чтобы назвать причину. В реальной схеме с внешними ключами такое несоответствие может быть запрещено, но в независимых выгрузках встречается.

4. Границы: нулевая сумма не означает отсутствие платежа

WITH orders(order_id,due_kopecks) AS (
 VALUES (201,1),(202,0),(203,100),(204,0)
), payments(payment_id,order_id,status,amount_kopecks) AS (
 VALUES (1,201,'captured',0),(2,203,'captured',100),
        (3,203,'captured',100),(4,204,'captured',0)
), paid AS (
 SELECT order_id,SUM(amount_kopecks) AS captured_kopecks
 FROM payments WHERE status='captured' GROUP BY order_id
)
SELECT o.order_id,o.due_kopecks,COALESCE(p.captured_kopecks,0) AS captured_kopecks,
       CASE WHEN p.order_id IS NULL THEN 'no_capture'
            WHEN p.captured_kopecks<o.due_kopecks THEN 'underpaid'
            WHEN p.captured_kopecks>o.due_kopecks THEN 'overpaid'
            ELSE 'matched' END AS result
FROM orders o LEFT JOIN paid p ON p.order_id=o.order_id
ORDER BY o.order_id;
Дополнительный набор: нули и две успешные операции
order_iddue_kopeckscaptured_kopecksresult
20110underpaid
20200no_capture
203100200overpaid
20400matched

У 201 есть успешная операция на ноль копеек, но недостаёт 1 копейки. У 202 ноль получается из COALESCE при отсутствии платежа; у 204 нулевая операция действительно существует. Сравнение только денежных сумм не способно различить эти ситуации: проверяйте также наличие строки paid.order_id. У 203 две успешные операции по 100 копеек; они дают 200, а не 100.

Самостоятельная проверка

Добавьте заказ 107 на 1 копейку и две попытки captured по 1 копейке каждая. Какой статус должен появиться и почему обычное суммирование стоимости заказа после JOIN ошибочно покажет 2 копейки к оплате? Затем попробуйте задание о лишних попытках платежа и агрегацию captured по заказам; решения этих задач здесь не приводятся.

Рядом, но с другим вопросом: оплаты за вычетом возвратов, почему JOIN увеличивает суммы и разница между строками и объектами.

Первоисточники: SQLite: SUM, COUNT; SQLite: SELECT и JOIN. Примеры проверены на SQLite, не на MySQL или PostgreSQL.


В SQL Studio примеры выполняются на SQLite в браузере. Для систематической практики отдельно доступен курс SQL для аналитика на Stepik, где используется MySQL 8; различия диалектов нужно учитывать.