SQL Studio / Разборы SQL / Сверка заказов и оплат
Заказ на 120 ₽ внезапно превращается в 360 ₽ после JOIN. Это не ошибка кассы: у заказа несколько попыток оплаты, а сумма заказа повторяется в каждой строке соединения. Для сверки сравнивайте одну сумму заказа с суммой только успешных платежей captured, предварительно сведённых до одной строки на order_id. Разница в целых копейках позволяет отличить недоплату, переплату и отсутствие успешного платежа.
Данные: шесть заказов и восемь попыток
Таблица orders имеет зерно «один заказ», payments — «одна попытка». Это самодостаточные WITH ... VALUES: каждый пример можно выполнить отдельно в бесплатной SQL-песочнице, не меняя учебную базу. Везде одна валюта — рубли, суммы хранятся в INTEGER копейках. Статус captured означает успешное списание; failed и pending не являются поступлением.
| order_id | к оплате, коп. |
|---|---|
| 101 | 12000 |
| 102 | 5000 |
| 103 | 7000 |
| 104 | 1500 |
| 105 | 3000 |
| 106 | 0 |
В попытках: для заказа 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;
| order_id | wrong_due | captured | attempts |
|---|---|---|---|
| 101 | 36000 | 10000 | 3 |
| 102 | 5000 | 0 | 1 |
| 103 | 7000 | 8000 | 1 |
| 104 | 1500 | 1500 | 1 |
| 105 | 3000 | 0 | 0 |
| 106 | 0 | 0 | 1 |
У 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;
| order_id | due_kopecks | captured_kopecks | delta_kopecks | result |
|---|---|---|---|---|
| 101 | 12000 | 10000 | -2000 | underpaid |
| 102 | 5000 | 0 | -5000 | no_capture |
| 103 | 7000 | 8000 | 1000 | overpaid |
| 104 | 1500 | 1500 | 0 | matched |
| 105 | 3000 | 0 | -3000 | no_attempts |
| 106 | 0 | 0 | 0 | matched |
Знак дельты: 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_id | orphan_captures | orphan_kopecks |
|---|---|---|
| 999 | 1 | 400 |
Первый отчёт начинался с заказов, поэтому 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_id | due_kopecks | captured_kopecks | result |
|---|---|---|---|
| 201 | 1 | 0 | underpaid |
| 202 | 0 | 0 | no_capture |
| 203 | 100 | 200 | overpaid |
| 204 | 0 | 0 | matched |
У 201 есть успешная операция на ноль копеек, но недостаёт 1 копейки. У 202 ноль получается из COALESCE при отсутствии платежа; у 204 нулевая операция действительно существует. Сравнение только денежных сумм не способно различить эти ситуации: проверяйте также наличие строки paid.order_id. У 203 две успешные операции по 100 копеек; они дают 200, а не 100.
Самостоятельная проверка
Добавьте заказ 107 на 1 копейку и две попытки captured по 1 копейке каждая. Какой статус должен появиться и почему обычное суммирование стоимости заказа после JOIN ошибочно покажет 2 копейки к оплате? Затем попробуйте задание о лишних попытках платежа и агрегацию captured по заказам; решения этих задач здесь не приводятся.
Первоисточники: SQLite: SUM, COUNT; SQLite: SELECT и JOIN. Примеры проверены на SQLite, не на MySQL или PostgreSQL.
В SQL Studio примеры выполняются на SQLite в браузере. Для систематической практики отдельно доступен курс SQL для аналитика на Stepik, где используется MySQL 8; различия диалектов нужно учитывать.