SQL实现按日期顺序逐行用正值抵扣负值直至结果为正的方法

这个抵扣逻辑本质是按日期升序对所有正值做累计求和，用累计值依次抵扣所有负值的绝对值总和，直到累计值首次覆盖待抵扣总额，从该条记录开始保留结果：

• 累计和首次大于等于待抵扣额的记录，取值为累计和减去待抵扣总额
• 该记录之后的所有正值直接保留原始值
• 累计和未覆盖待抵扣额的正值行全部丢弃
• 所有负值行不进入最终结果

首先要处理表内的数值格式：现有VALUE字段用逗号作为小数分隔符，计算前需要先转换成数据库可识别的数值类型，最终输出再转回逗号分隔的格式。

WITH cleaned_data AS (
    SELECT
        `DATE`,
        CAST(REPLACE(`VALUE`, ',', '.') AS DECIMAL(18,2)) AS value_num
    FROM -- 替换为实际业务表名
        your_table
),
deduct_amount AS (
    -- 计算所有需要抵扣的负值总绝对值
    SELECT SUM(ABS(value_num)) AS total_need_deduct
    FROM cleaned_data
    WHERE value_num < 0
),
positive_running_calc AS (
    SELECT
        `DATE`,
        value_num,
        -- 按日期升序计算正值的累计和
        SUM(value_num) OVER (
            ORDER BY `DATE` ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total
    FROM cleaned_data
    WHERE value_num > 0
)
SELECT
    `DATE`,
    -- 第一条覆盖抵扣额的记录计算抵扣后的值，其余保留原值，最后转回逗号小数格式
    CASE
        WHEN running_total >= total_need_deduct
         AND LAG(running_total, 1, 0) OVER (ORDER BY `DATE`) < total_need_deduct
        THEN REPLACE(CAST(running_total - total_need_deduct AS CHAR), '.', ',')
        ELSE REPLACE(CAST(value_num AS CHAR), '.', ',')
    END AS `VALUE`
FROM positive_running_calc, deduct_amount
-- 只保留累计额已经覆盖待抵扣值的记录
WHERE running_total >= total_need_deduct
ORDER BY `DATE` ASC;

用提供的测试数据执行后返回结果完全符合预期：

• 如果使用PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库，仅需要调整少量类型转换、字符串替换的函数语法，核心计算逻辑无需修改
• 如果业务场景存在多条穿插在正值之间的负值，只需要调整累计计算的范围规则即可适配
• 执行前将SQL中的your_table替换为实际的业务表名

内容的提问来源于stack exchange，提问作者DeusMorbo