PostgreSQL 15中实现累计负值抵扣的自定义聚合函数开发

需求说明 #

需要实现以下逻辑：

• 所有负值进行累计，形成待抵扣的负债
• 遇到正值时用其抵扣累计负债，具体规则：
  • 若正值金额大于累计负债：输出抵扣后的剩余正值，累计负债清零
  • 若正值金额不足以覆盖累计负债：输出0，累计负债更新为抵扣后的剩余负值
  • 若当前是正值且无累计负债：直接输出该正值

测试数据集 #

SELECT id, value, expected_output, comment
FROM (VALUES 
      (1, 0.89, 0.89, ''), 
      (2, 0.17, 0.17, ''), 
      (3, -0.30, 0, 'accum = -0.30'), 
      (4, -0.36, 0, 'accum = -0.30 - 0.36 = -0.66'), 
      (5, 1.41, 0.75, '0.75 = 1.41 - 0.66; accum = 0'), 
      (6, -0.49, 0, 'accum = -0.49'),
      (7, 0.24, 0, '0, because 0.24 - 0.49 = -0.25 is still negative, so accum = -0.25'), 
      (8, 6.15, 5.9, '5.9 = 6.15 - 0.25; accum = 0'), 
      (9, 6.05, 6.05, '')
) 
AS t(id, value, expected_output, comment);

现有问题分析 #

你之前写的聚合状态函数只维护了单一的累计值，但我们需要同时跟踪累计负债和当前行的输出结果两个状态，所以仅用单一数值无法满足需求，必须用复合类型来保存状态。

解决方案：自定义聚合函数 #

1. 创建复合类型存储状态 #

这个类型用来同时记录累计的负债金额和当前行的输出值：

CREATE TYPE debt_output AS (
    debt numeric,
    output numeric
);

2. 编写状态转换函数 #

该函数处理每一行的value，根据规则更新累计负债并计算当前行输出：

CREATE OR REPLACE FUNCTION debt_settlement_accum(
    _state debt_output,
    _current numeric
) RETURNS debt_output
LANGUAGE plpgsql
AS $BODY$
BEGIN
    -- 初始化状态（首次处理时累计负债设为0）
    IF _state.debt IS NULL THEN
        _state.debt := 0;
    END IF;

    IF _current < 0 THEN
        -- 当前是负值：累加到负债，输出0
        _state.debt := _state.debt + _current;
        _state.output := 0;
    ELSE
        -- 当前是正值：结算累计负债
        IF _current > abs(_state.debt) THEN
            -- 正值足够抵扣：输出剩余部分，负债清零
            _state.output := _current + _state.debt; -- 等价于 current - abs(debt)
            _state.debt := 0;
        ELSE
            -- 正值不足抵扣：输出0，负债更新为剩余值
            _state.debt := _state.debt + _current;
            _state.output := 0;
        END IF;
    END IF;

    RETURN _state;
END;
$BODY$;

3. 创建聚合函数 #

基于上面的状态函数创建聚合：

CREATE AGGREGATE debt_settlement(numeric) (
    SFUNC = debt_settlement_accum,
    STYPE = debt_output,
    INITCOND = '(0, 0)'
);

4. 使用窗口函数获取结果 #

通过窗口函数按id顺序逐行处理，提取输出值：

SELECT
    id,
    value,
    (debt_settlement(value) OVER (ORDER BY id)).output AS calculated_output,
    expected_output,
    comment
FROM (VALUES 
      (1, 0.89, 0.89, ''), 
      (2, 0.17, 0.17, ''), 
      (3, -0.30, 0, 'accum = -0.30'), 
      (4, -0.36, 0, 'accum = -0.30 - 0.36 = -0.66'), 
      (5, 1.41, 0.75, '0.75 = 1.41 - 0.66; accum = 0'), 
      (6, -0.49, 0, 'accum = -0.49'),
      (7, 0.24, 0, '0, because 0.24 - 0.49 = -0.25 is still negative, so accum = -0.25'), 
      (8, 6.15, 5.9, '5.9 = 6.15 - 0.25; accum = 0'), 
      (9, 6.05, 6.05, '')
) AS t(id, value, expected_output, comment);

执行后calculated_output列会和expected_output完全匹配，满足你的需求。

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