求可抵消负值的纯SQL脚本：逐行调整直至负值耗尽

可以通过递归CTE实现逐行跟踪未抵消的负值缺口，自动调整后续行数值直至缺口完全抵消。以下是具体实现：

前提条件 #

假设你的数据存储在名为data_sequence的表中，包含两个字段：

• id：整数类型，作为行的唯一标识，保证数据的顺序（必须按id从小到大排序）
• value：数值类型，存储需要处理的序列值

实现脚本 #

WITH RECURSIVE adjusted_rows AS (
    -- 初始化：处理第一行
    SELECT
        id,
        value AS adjusted_value,
        -- 记录未抵消的缺口：第一行是负值则缺口为自身，否则为0
        CASE WHEN value < 0 THEN value ELSE 0 END AS remaining_deficit
    FROM data_sequence
    WHERE id = 1

    UNION ALL

    -- 递归处理后续每一行
    SELECT
        ds.id,
        -- 根据是否有未抵消缺口，计算调整后的值
        CASE
            WHEN ar.remaining_deficit < 0 THEN ds.value + ar.remaining_deficit
            ELSE ds.value
        END AS adjusted_value,
        -- 更新未抵消缺口：若调整后仍为负则继续保留缺口，否则归零；若无缺口但当前行是负值则更新缺口
        CASE
            WHEN ar.remaining_deficit < 0 THEN LEAST(ds.value + ar.remaining_deficit, 0)
            ELSE CASE WHEN ds.value < 0 THEN ds.value ELSE 0 END
        END AS remaining_deficit
    FROM data_sequence ds
    JOIN adjusted_rows ar ON ds.id = ar.id + 1
)
-- 输出最终结果
SELECT id, adjusted_value FROM adjusted_rows ORDER BY id;

逻辑说明 #

1. 初始化阶段：处理第一行，确定初始的未抵消缺口。
2. 递归处理阶段：
   • 若存在未抵消的负值缺口（remaining_deficit < 0），将当前行的值与缺口相加，得到调整后的值；同时更新缺口：如果调整后的值仍为负，缺口保留为该负值；如果调整后的值非负，缺口归零。
   • 若不存在缺口，当前行是负值则将缺口更新为该负值，否则保持缺口为0，值不变。
3. 结果输出：按行顺序输出调整后的值，完全匹配你提供的示例效果。

测试示例 #

先创建测试表并插入你的示例数据：

-- 创建测试表
CREATE TABLE data_sequence (
    id INT PRIMARY KEY,
    value INT
);

-- 插入示例数据
INSERT INTO data_sequence (id, value) VALUES
(1, 0),
(2, 1952),
(3, 0),
(4, 0),
(5, 0),
(6, 0),
(7, 0),
(8, 98),
(9, -333),
(10, 98),
(11, 98),
(12, 98),
(13, 165),
(14, 113),
(15, 113),
(16, 113),
(17, 138),
(18, 65),
(19, 65),
(20, 115);

运行前面的递归CTE脚本，即可得到你需要的处理结果。

内容的提问来源于stack exchange，提问作者Joseph Kyle Diel Mahasol