Пакет услуг с переносом остатка: помесячный расчёт в SQL

Сервис выдаёт сто минут в месяц и разрешает переносить до пятидесяти неиспользованных минут. Клиент потратил 70 минут в январе и 150 в феврале. Простой расчёт «использование минус сто» выставит счёт за пятьдесят лишних минут, хотя тридцать минут перешли с января. Для правильной тарификации каждый месяц должен начинаться с результата предыдущего.

Какой именно перенос считаем

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

Это последнее условие существенно. В другом тарифе старые минуты могут сгорать через месяц, а расход сначала списывается из самого старого пакета. Тогда нужно отслеживать происхождение остатков, и одного числа carry недостаточно. Не стоит применять короткий запрос к обоим тарифам только потому, что в описании встречается слово «перенос».

Почему обычной накопительной суммы мало

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

Вход usage содержит ровно один месяц на позицию n, без пропусков. Первый месяц начинает с нулевого переноса. available — доступный лимит до расхода, overage — превышение, carry — то, что можно перенести. В рабочем отчёте эти названия лучше показывать пользователю понятными подписями, сохранив исходные поля в витрине.

Ниже самодостаточный запрос SQLite с тремя месяцами.

WITH RECURSIVE
usage(n,month,used) AS (VALUES (1,'2026-01',70),(2,'2026-02',150),(3,'2026-03',60)),
ledger(n,month,used,available,overage,carry) AS (
 SELECT n,month,used,100,MAX(0,used-100),MIN(50,MAX(0,100-used))
 FROM usage WHERE n=1
 UNION ALL
 SELECT u.n,u.month,u.used,100+l.carry,
        MAX(0,u.used-100-l.carry),
        MIN(50,MAX(0,100+l.carry-u.used))
 FROM ledger l JOIN usage u ON u.n=l.n+1
)
SELECT month,used,available,overage,carry FROM ledger ORDER BY n;

Проверить состояние на каждом шаге

В январе доступно сто, использовано семьдесят: перенос тридцать, перерасход нулевой. Февраль начинается со ста тридцати минут. Расход сто пятьдесят даёт перерасход двадцать и нулевой перенос. В марте доступно сто, потрачено шестьдесят, переносится сорок. Итог нельзя проверить только суммой расходов: важен каждый переход состояния.

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

Как подготовить календарь и историю

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

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

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

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

Чтобы уверенно считать такие цепочки, нужна практика рекурсивных запросов и календарных рядов. В платном «Продвинутом SQL» есть CTE, исторические значения и цельные кейсы с проверкой на открытых и скрытых тестах. Следующий шаг — добавить несколько подписок и смену правил, сохранив объяснимость каждого месяца. Откройте программу курса.