Оплаты и возвраты в SQL: как получить правильный итог

SQL Studio / Разборы SQL / Оплаты и возвраты в SQL: как получить правильный итог

Правило: сначала суммируйте подтверждённые оплаты и возвраты независимо друг от друга, затем вычитайте. Таблицы payments и refunds имеют разные ключи и детализацию; прямое соединение через заказы и позиции может размножить записи. В этой статье «чистые поступления» — учебная разница денежных операций, не бухгалтерская прибыль.

1. Сначала сверить две суммы

Неудачные попытки оплаты не должны входить в поступления. В нашей базе успешные имеют статус captured; возвраты хранятся отдельно по позициям.

SELECT
  (SELECT COALESCE(SUM(amount_kopecks), 0)
   FROM payments WHERE status = 'captured') AS captured_kopecks,
  (SELECT COALESCE(SUM(amount_kopecks), 0)
   FROM refunds) AS refunded_kopecks;

Это 1 250 ₽ подтверждённых оплат и 90 ₽ возвратов. На этом этапе мы ещё не соединяли таблицы, поэтому одна платёжная попытка или возврат не продублировались из-за других событий.

2. Чем опасно соединить всё сразу

Заказ 201 имеет две попытки оплаты, две товарные позиции и два возврата по одной из позиций. Проверим количество строк после последовательных соединений.

SELECT o.order_id, COUNT(*) AS joined_rows
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.order_id
LEFT JOIN order_items AS i ON i.order_id = o.order_id
LEFT JOIN refunds AS r ON r.item_id = i.item_id
GROUP BY o.order_id
HAVING COUNT(*) > 1
ORDER BY o.order_id;

У заказа №201 получится 6 строк, хотя сам заказ один. У заказов 203, 205 и 206 — по две строки. Если теперь суммировать p.amount_kopecks или r.amount_kopecks, отдельные события будут учтены несколько раз.

3. Правильная сверка по каждому заказу

Сначала агрегируем поступления по order_id, отдельно возвраты через принадлежность позиции заказу. После этого обе подготовленные таблицы имеют максимум одну строку на заказ.

WITH paid AS (
  SELECT order_id, SUM(amount_kopecks) AS paid_kopecks
  FROM payments
  WHERE status = 'captured'
  GROUP BY order_id
),
refunded AS (
  SELECT i.order_id, SUM(r.amount_kopecks) AS refunded_kopecks
  FROM refunds AS r
  JOIN order_items AS i ON i.item_id = r.item_id
  GROUP BY i.order_id
)
SELECT o.order_id,
       COALESCE(p.paid_kopecks, 0) AS paid_kopecks,
       COALESCE(r.refunded_kopecks, 0) AS refunded_kopecks,
       COALESCE(p.paid_kopecks, 0) - COALESCE(r.refunded_kopecks, 0) AS net_kopecks
FROM orders AS o
LEFT JOIN paid AS p ON p.order_id = o.order_id
LEFT JOIN refunded AS r ON r.order_id = o.order_id
ORDER BY o.order_id;

По заказу 201 две возвратные операции суммарно дают 7 000 копеек. Мы не удалили их через DISTINCT, а правильно сложили на уровне заказа.

4. Итог без соединений

Для единого итога достаточно двух однострочных CTE. На пустых наборах COALESCE делает суммы нулевыми.

WITH paid AS (
  SELECT COALESCE(SUM(amount_kopecks), 0) AS x
  FROM payments WHERE status = 'captured'
),
returned AS (
  SELECT COALESCE(SUM(amount_kopecks), 0) AS y
  FROM refunds
)
SELECT (x - y) / 100.0 AS net_rub
FROM paid CROSS JOIN returned;

Получаем 1 160 ₽. В настоящей бухгалтерии дата возврата может не совпадать с датой оплаты; для отчёта за период потребуется явно определить дату каждого денежного события. Без этого показанный итог нельзя называть выручкой или прибылью за конкретный месяц.

Попрактиковаться

В тренажёре есть задача с двумя CTE для подтверждённых оплат и возвратов. Она проверяет именно этот приём.

Смежные темы: ошибка суммы после JOIN · UNION ALL для денежных событий · средний чек.

Справка по агрегации и соединениям: SQLite — SUM и COUNT, SQLite — JOIN.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.