如何用PostgreSQL计算各时间段内的未偿债务余额

我有如下数据表：

/**
| NAME  | DELTA (PAID - EXPECTED) | PERIOD |
|-------|-------------------------|--------|
| SMITH |                      -50|       1|
| SMITH |                        0|       2|
| SMITH |                      150|       3|
| SMITH |                     -200|       4|
| DOE   |                      300|       1|
| DOE   |                        0|       2|
| DOE   |                     -200|       3|
| DOE   |                     -200|       4|
**/
DROP TABLE delete_me;

CREATE TABLE delete_me (
    "NAME" varchar(255),
    "DELTA (PAID - EXPECTED)" numeric(15,2),
    "PERIOD" Int
);

INSERT INTO delete_me("NAME",   "DELTA (PAID - EXPECTED)", "PERIOD")
VALUES
  ('SMITH',  -50,   1),
  ('SMITH',    0,   2),
  ('SMITH',  150,   3),
  ('SMITH', -200,   4),
  ('DOE',    300,   1),
  ('DOE',      0,   2),
  ('DOE',   -200,   3),
  ('DOE',   -200,   4)

业务规则 #

• PERIOD代表时间维度：1是最新时段，4是最旧时段
• 负的DELTA (PAID - EXPECTED)表示该时段有欠款；正的表示超额支付产生的溢缴款
• 溢缴款需优先偿还最旧的欠款，最终要计算每个时段的未偿债务余额（PERIOD BALANCE）

预期输出 #

| NAME  | DELTA (PAID - EXPECTED) | PERIOD | PERIOD BALANCE |
|-------|-------------------------|--------|----------------|
| SMITH |                      -50|       1|             -50|
| SMITH |                        0|       2|               0|
| SMITH |                      150|       3|               0|
| SMITH |                     -200|       4|             -50|
| DOE   |                      300|       1|               0|
| DOE   |                        0|       2|               0|
| DOE   |                     -200|       3|            -100|
| DOE   |                     -200|       4|               0|

补充说明 #

• DOE用户：最旧时段4欠款200，时段3新增200欠款，总欠款400；时段2仅付清当月账单；时段1超额支付300，先结清时段4的200欠款，剩余100抵扣时段3的欠款，最终时段3仍欠款100。
• SMITH用户：时段4欠款200，时段3超额支付150偿还该欠款，剩余50未结清；时段2付清当月账单；时段1新增50欠款，最终总欠款100，分别为时段1的50和时段4的50。

要实现这个逻辑，我们需要按用户分组，先处理最旧到最新的时段，逐步计算溢缴款的抵扣情况，最终得到每个时段的未偿余额。以下是具体SQL：

WITH ranked_periods AS (
    -- 按用户分组，将时段从旧到新排序（PERIOD越大越旧）
    SELECT 
        "NAME",
        "DELTA (PAID - EXPECTED)" AS delta,
        "PERIOD",
        -- 给每个用户的时段按从旧到新排序列号，4→1对应序号1→4
        ROW_NUMBER() OVER (PARTITION BY "NAME" ORDER BY "PERIOD" DESC) AS period_rank
    FROM delete_me
),
cumulative_calculations AS (
    -- 计算累计的溢缴款可用额度、累计欠款，以及每个时段的抵扣后余额
    SELECT 
        *,
        -- 累计的可用溢缴款：所有之前（更旧）时段的delta正数之和
        SUM(GREATEST(delta, 0)) OVER (PARTITION BY "NAME" ORDER BY period_rank) AS total_available_credit,
        -- 累计的总欠款：所有之前（更旧）时段的delta负数绝对值之和
        SUM(LEAST(delta, 0)) OVER (PARTITION BY "NAME" ORDER BY period_rank) AS total_debt
    FROM ranked_periods
),
adjusted_balances AS (
    SELECT 
        *,
        -- 计算当前时段的初始欠款（delta为负则是欠款，否则为0）
        LEAST(delta, 0) AS initial_debt,
        -- 计算到当前时段为止，累计溢缴款能覆盖的最大欠款金额
        GREATEST(0, total_available_credit + total_debt) AS credit_used
    FROM cumulative_calculations
),
final_balances AS (
    SELECT 
        "NAME",
        delta AS "DELTA (PAID - EXPECTED)",
        "PERIOD",
        -- 最终余额：初始欠款减去被溢缴款覆盖的部分，但不能超过初始欠款的绝对值
        CASE 
            WHEN initial_debt = 0 THEN 0
            ELSE GREATEST(initial_debt + credit_used, initial_debt)
        END AS "PERIOD BALANCE"
    FROM adjusted_balances
)
-- 按用户和时段从新到旧排序输出，匹配预期格式
SELECT * FROM final_balances ORDER BY "NAME", "PERIOD";

逻辑解释 #

1. ranked_periods：给每个用户的时段按从旧到新排序，方便后续按时间顺序计算抵扣。
2. cumulative_calculations：计算累计的可用溢缴款（所有旧时段的超额支付总和）和累计的总欠款（所有旧时段的欠款总和）。
3. adjusted_balances：拆分每个时段的初始欠款，同时计算累计溢缴款能覆盖的欠款额度。
4. final_balances：根据初始欠款和可覆盖额度，计算每个时段的最终未偿余额，确保不会出现余额为正的情况（因为溢缴款只会用于抵扣欠款，不会变成该时段的正向余额）。

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