Распределение оплат по счетам FIFO в SQL: один платёж закрывает несколько долгов

Клиент перечислил одну сумму за несколько счетов и не указал назначение по каждому. В отчёте нужно увидеть, какой долг погашен полностью, какой частично и сколько осталось авансом. Когда учётная политика разрешает закрывать самые старые счета первыми, такую раскладку можно вычислить SQL-запросом.

Где действует правило FIFO

Здесь FIFO означает согласованный порядок погашения: сначала более ранний счёт, при одинаковой дате — меньший id. Поступления тоже упорядочены по дате и id. Нужен закрытый расчётный контур: один клиент, один договор, одна валюта. В реальной базе все эти признаки входят в PARTITION BY и в условие JOIN.

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

Денежные суммы превращаются в отрезки

Пусть выставлены счета на 100 и 80 рублей. Первый занимает участок накопленного долга от 0 до 100, второй — от 100 до 180. Пришли платежи 60 и 90 рублей: их участки — от 0 до 60 и от 60 до 150. Размер пересечения счёта и платежа равен сумме, которую этот платёж направит на этот счёт.

Именно поэтому второй платёж закрывает остаток первого счёта на 40 рублей и ещё 50 рублей второго. На втором остаётся долг 30 рублей. Такой подход даёт таблицу распределений с двумя исходными id: она пригодится для детализации и повторной проверки, а не только для общего остатка клиента.

Запрос с явным порядком

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

CREATE TABLE invoices(client TEXT,id INTEGER,day TEXT,amount INTEGER);
CREATE TABLE payments(client TEXT,id INTEGER,day TEXT,amount INTEGER);
INSERT INTO invoices VALUES ('A',1,'2026-10-01',10000),('A',2,'2026-10-02',8000),('B',3,'2026-10-01',5000);
INSERT INTO payments VALUES ('A',11,'2026-10-03',6000),('A',12,'2026-10-04',9000),('B',13,'2026-10-05',7000);
CREATE TEMP VIEW allocation AS
WITH i AS (
 SELECT *,SUM(amount) OVER(PARTITION BY client ORDER BY day,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS hi FROM invoices
), p AS (
 SELECT *,SUM(amount) OVER(PARTITION BY client ORDER BY day,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS hi FROM payments
)
SELECT i.client,i.id AS invoice_id,p.id AS payment_id,
 MIN(i.hi,p.hi)-MAX(i.hi-i.amount,p.hi-p.amount) AS allocated
FROM i JOIN p ON i.client=p.client
 AND i.hi>p.hi-p.amount AND p.hi>i.hi-i.amount;
SELECT * FROM allocation ORDER BY client,invoice_id,payment_id;

Сверяем обе стороны распределения

У клиента A распределено 150 рублей из 180 выставленных: первый счёт закрыт, второй получил 50 рублей из 80. У B распределено 50 рублей из 70 поступивших: остаток 20 рублей сохраняется как аванс. Важно показать его отдельной строкой в отчёте, иначе пользователь решит, что часть банковского поступления потерялась.

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

Что меняется при новом поступлении

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

Этот запрос строит аналитическое распределение по выбранному правилу. Для переноса в учётную систему нужен согласованный документ зачёта с теми же основаниями. Не заменяйте существующие назначения платежей результатом запроса автоматически: сначала сравните два варианта и объясните финансовой команде каждое отличие.

Как довести пример до рабочего отчёта

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

Проверяем простые границы

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

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

Продолжить практику

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

Перед продолжением разберите открытый урок об оконных рамках ROWS: он поможет проверить, какие строки попадают в накопительную сумму.