PostgreSQL中如何实现余额递减式扣款计算？

这是个很常见的按顺序余额分配场景，我们完全可以用PostgreSQL的窗口函数优雅解决，不用写循环或者存储过程。下面一步步来实现你要的结果：

1. 先准备测试数据 #

首先创建测试表并插入你的示例数据（注意我把金额里的逗号去掉了，PostgreSQL数值类型不支持带逗号的格式）：

CREATE TABLE payments (
    id INT,
    item TEXT,
    amount NUMERIC,
    paid NUMERIC,
    apply_payment NUMERIC
);

INSERT INTO payments VALUES
(1, 'item 1', 500, 0, 15000),
(2, 'item 2', 20000, 3000, 15000),
(3, 'item 3', 7000, 0, 15000);

2. 核心查询逻辑 #

下面的查询会计算出amount_applied并更新apply_payment为递减的余额：

WITH calculated AS (
    SELECT
        *,
        -- 计算每个项目的未付金额
        (amount - paid) AS outstanding,
        -- 按id排序，累计计算到当前项目的总未付金额
        SUM(amount - paid) OVER (ORDER BY id) AS cumulative_outstanding,
        -- 累计实际分配的金额（不超过总支付额15000）
        LEAST(SUM(amount - paid) OVER (ORDER BY id), 15000) AS cumulative_applied,
        -- 获取前一行的累计分配金额，第一行默认0
        COALESCE(LAG(LEAST(SUM(amount - paid) OVER (ORDER BY id), 15000)) OVER (ORDER BY id), 0) AS prev_cumulative_applied
    FROM payments
)
SELECT
    id,
    item,
    amount,
    paid,
    -- 当前分配时的可用余额：总支付额减去之前已分配的部分
    GREATEST(15000 - prev_cumulative_applied, 0) AS apply_payment,
    -- 当前项目实际分到的金额：当前累计分配减去之前的累计分配，负数取0
    GREATEST(cumulative_applied - prev_cumulative_applied, 0) AS amount_applied
FROM calculated
ORDER BY id;

3. 逻辑拆解 #

给你解释下关键部分的作用：

• outstanding：单个项目还需要支付的金额（总金额减去已付金额）
• cumulative_outstanding：按id顺序累加未付金额，用来跟踪到当前项目为止总共需要付多少钱
• cumulative_applied：累计实际分配的金额，最多不超过总支付额15000
• prev_cumulative_applied：上一个项目的累计分配金额，用来计算当前项目能分到多少额度
• apply_payment：当前分配时剩下的可用余额，也就是总支付额减去之前已经分配的部分
• amount_applied：当前项目实际拿到的支付金额，等于当前累计分配减去之前的累计分配，避免出现负数

4. 测试结果 #

执行上面的查询后，会得到你期望的结果：

这个逻辑还能自动处理总支付额足够覆盖所有未付的情况，比如如果支付额是24500（500+17000+7000），item3的amount_applied会是7000，apply_payment会是0（前两个项目用了17500，剩下的7000全部分配给item3）。

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