Отменяющие события в SQL: как восстановить действующие операции из журнала

Касса передала продажу на сто рублей, затем сообщила, что операция аннулирована, и отправила исправленную продажу на 120. Журнал хранит все три сообщения. Если сложить положительные суммы, получится 220. Если удалить старую строку, исчезнет история исправления. Для корректного отчёта нужно восстановить действующие операции по связям отмены.

Определить, что именно делает отмена

В нашей модели отмена целиком выводит одну исходную продажу из расчёта. У неё есть свой id, тип reverse и reverses_id — идентификатор исходной операции. Это не частичный возврат денег и не самостоятельный расход. Исправленный документ создаётся новой продажей с отдельным id. Такая семантика позволяет сохранить неизменяемый журнал и построить текущую проекцию для отчёта.

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

Найти продажи без отмены

Продажа активна, если для её id не существует события reverse. NOT EXISTS удобно выражает это правило и не размножает исходные строки. В учебном журнале есть продажа 1 на 100, продажа 2 на 50, отмена продажи 1 и новая продажа 4 на 120. Итоговая сумма должна состоять из операций 2 и 4.

Пример выполняется в SQLite; функции и синтаксис ниже относятся к этому диалекту.

WITH events(id,kind,amount,reverses_id) AS (VALUES
 (1,'sale',100,NULL),(2,'sale',50,NULL),(3,'reverse',NULL,1),
 (4,'sale',120,NULL)),
active AS (
 SELECT e.id,e.amount FROM events e
 WHERE e.kind='sale' AND NOT EXISTS (
  SELECT 1 FROM events r WHERE r.kind='reverse' AND r.reverses_id=e.id
 )
)
SELECT SUM(amount) AS total,GROUP_CONCAT(id,',') AS active_ids
FROM (SELECT * FROM active ORDER BY id);

Запрос возвращает total = 170 и active_ids = 2,4. Идентификаторы полезны для проверки: один итог не показывает, какая запись исключена. GROUP_CONCAT здесь используется только для небольшого демонстрационного результата. В рабочей витрине сохраняйте отдельные строки активных операций, а агрегацию по дню или магазину выполняйте следующим шагом.

Почему дубли отмены не должны менять результат

Пусть интеграция повторно доставила отмену продажи 1. Проверка существования по-прежнему исключает продажу один раз. При JOIN и последующем суммировании отрицательных корректировок повтор мог бы уменьшить результат ещё на сто. Идемпотентная доставка на уровне загрузки всё равно нужна: проекция отчёта не заменяет уникальность event_id и контроль повторов.

Нужно отдельно найти отмены, для которых исходного id нет. Они могут прийти раньше продажи из-за задержки канала либо содержать ошибочную ссылку. Скрывать их из контроля нельзя. Для отчёта, закрываемого к сроку, определите время ожидания связанных сообщений и правило пересчёта. Сам SQL ничего не знает о том, окончателен ли журнал.

Два разных вопроса к прошлому

«Что мы знали вечером 5 октября?» и «Как сейчас выглядит итог продаж за 5 октября?» могут дать разные ответы. Для первого вопроса фильтр времени получения накладывают на все события до восстановления активных строк. Для второго выбирают бизнес-даты продаж, но учитывают отмены, пришедшие позднее. Нельзя фильтровать только продажи и случайно отрезать нужную корректировку другим календарным условием.

Если разрешена отмена отмены, простое правило из примера перестаёт работать. Понадобится модель цепочек либо явное событие восстановления с определённым приоритетом. Это решение должно быть закреплено контрактом источника. Не добавляйте рекурсию наугад: сначала установите, допустимы ли циклы ссылок, сколько коррекций может быть у операции и какое сообщение считается окончательным.

В приёмочный набор включите повтор отмены, отсутствующий исходный id, отмену после даты отчёта и продажу с нулевой суммой. Сверяйте не только общую выручку, но и число активных операций, сумму исключённых продаж и список необработанных ссылок. Так можно отличить реальное падение продаж от массового исправления документов и объяснить пересчёт уже закрытого отчёта.

Следующий шаг

Чтобы связать такой журнал с оплатами и заказами, потребуется сохранить зерно данных и проверить многошаговые соединения. Платный курс «Продвинутый SQL» разбирает сверку отчётов, исторические значения и сложные CTE с автоматической проверкой решений. На этой практике можно научиться доказывать итог через строки, которые в него вошли. Откройте программу курса.