Расчёт остатка в SQL Sybase с переносом на следующие строки

В старых версиях Sybase ASE (Adaptive Server Enterprise) часто возникает задача: вычислить разницу между двумя показателями (1 кейс и 2 кейс) так, чтобы результат (3 кейс) не уходил в минус, а переносился на последующие даты, пока не будет полностью покрыт новыми значениями второго кейса. Это типичная задача для складских остатков или пошагового списания. Рассмотрим решение без оконных функций, доступное даже в ограниченных версиях СУБД.

Понимание задачи: что такое 1, 2 и 3 кейсы

У нас есть три столбца: 1 кейс (приход), 2 кейс (расход) и 3 кейс (остаток, который нужно вычислить). Логика:

• 3 кейс = (предыдущий остаток + текущий 1 кейс) - текущий 2 кейс.
• Если 2 кейс больше, чем (предыдущий остаток + 1 кейс), то остаток становится 0 (или null).
• Если остаток положительный, он переносится на следующую дату и участвует в расчёте.

Простое построчное вычитание (1 кейс - 2 кейс) не работает, так как не учитывает накопленный остаток с прошлых периодов.

Решение для Sybase ASE (без оконных функций)

В Sybase ASE до версии 15.7 нет поддержки LAG или SUM() OVER. Поэтому используем временную таблицу и курсор. Пример реализации:

CREATE TABLE #tmp (id INT IDENTITY, dt DATE, k1 INT, k2 INT, k3 INT);
INSERT INTO #tmp (dt, k1, k2) VALUES ('2024-01-01',43,3),('2024-01-02',0,0),('2024-01-03',3,51);

DECLARE @prev_k3 INT = 0, @id INT, @k1 INT, @k2 INT, @new_k3 INT;
DECLARE cur CURSOR FOR SELECT id, k1, k2 FROM #tmp ORDER BY id;
OPEN cur;
FETCH NEXT FROM cur INTO @id, @k1, @k2;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @new_k3 = @prev_k3 + @k1 - @k2;
    IF @new_k3 < 0 SET @new_k3 = 0;
    UPDATE #tmp SET k3 = @new_k3 WHERE id = @id;
    SET @prev_k3 = @new_k3;
    FETCH NEXT FROM cur INTO @id, @k1, @k2;
END
CLOSE cur; DEALLOCATE cur;
SELECT dt, k1, k2, k3 FROM #tmp ORDER BY dt;

Этот код обрабатывает строки по порядку, корректно перенося остаток. Для больших таблиц курсор может быть медленным, но в старых версиях Sybase это единственный надёжный способ.

Альтернативный подход: рекурсивный CTE (если доступен)

Некоторые старые версии Sybase ASE (начиная с 15.0) поддерживают рекурсивные Common Table Expressions (CTE). Если ваша версия позволяет, можно использовать следующий запрос:

WITH cte AS (
    SELECT dt, k1, k2,
           CASE WHEN k1 - k2 < 0 THEN 0 ELSE k1 - k2 END AS k3,
           ROW_NUMBER() OVER (ORDER BY dt) AS rn
    FROM your_table
),
rec AS (
    SELECT dt, k1, k2, k3, rn FROM cte WHERE rn = 1
    UNION ALL
    SELECT c.dt, c.k1, c.k2,
           CASE WHEN r.k3 + c.k1 - c.k2 < 0 THEN 0 ELSE r.k3 + c.k1 - c.k2 END,
           c.rn
    FROM rec r JOIN cte c ON c.rn = r.rn + 1
)
SELECT dt, k1, k2, k3 FROM rec ORDER BY dt;

Рекурсивный CTE более производителен, чем курсор, но требует поддержки синтаксиса WITH и ROW_NUMBER().

Почему простое вычитание не даёт нужного результата?

При построчном вычитании (1 кейс - 2 кейс) на дате 3/01 получилось бы 3 - 51 = -48, что не соответствует логике. В реальном сценарии остаток с предыдущего дня (40) суммируется с приходом (3), давая 43, затем вычитается расход (51) - остаток становится 0. Именно это и делает наш алгоритм.

Часто задаваемые вопросы (FAQ)