如何使用 PostgreSQL SQL 按还款后的余额计算利息？
支付阿杰
结论
这类计算中，当天余额取决于前一天的计算结果，属于递推关系。普通窗口函数无法直接处理，适合使用 PostgreSQL 的递归 CTE（WITH RECURSIVE）。
示例采用以下规则：
payment增加欠款本金；repayment减少欠款；- 每天产生的利息计入下一天余额，也就是按日复利；
- 当天的还款先从余额中扣除，再计算当天利息；
- 截止日只显示余额，不计入当天尚未入账的利息。
以下每日利率按 2.5% 计算，也就是 0.025。
SQL 实现
WITH RECURSIVE
params AS (
SELECT
DATE '2017-12-04' AS as_of_date,
0.025::numeric AS daily_rate
),
-- 每个账单周期从 ends_at 的下一天开始计息
cycles AS (
SELECT
bc.id AS billing_cycle_id,
bc.ends_at::date + 1 AS start_date
FROM billing_cycles AS bc
),
-- 将同一天的所有交易合并成一个余额变化值
daily_changes AS (
SELECT
p.billing_cycle_id,
p.created_at::date AS change_date,
SUM(
CASE p.type
WHEN 'payment' THEN p.amount
WHEN 'repayment' THEN -p.amount
ELSE 0::numeric
END
)::numeric AS balance_change
FROM payments AS p
GROUP BY
p.billing_cycle_id,
p.created_at::date
),
-- 计算正式计息之前的期初欠款
seeds AS (
SELECT
c.billing_cycle_id,
c.start_date,
prm.as_of_date,
prm.daily_rate,
COALESCE(
SUM(
CASE p.type
WHEN 'payment' THEN p.amount
WHEN 'repayment' THEN -p.amount
ELSE 0::numeric
END
) FILTER (
WHERE p.created_at::date < c.start_date
),
0::numeric
) AS opening_balance
FROM cycles AS c
CROSS JOIN params AS prm
LEFT JOIN payments AS p
ON p.billing_cycle_id = c.billing_cycle_id
GROUP BY
c.billing_cycle_id,
c.start_date,
prm.as_of_date,
prm.daily_rate
),
interest_schedule (
billing_cycle_id,
calculation_date,
amount,
calculated_interest,
daily_rate,
as_of_date
) AS (
-- 第一天：期初欠款加上当天发生的交易
SELECT
s.billing_cycle_id,
s.start_date,
(
s.opening_balance
+ COALESCE(dc.balance_change, 0::numeric)
)::numeric AS amount,
(
(
s.opening_balance
+ COALESCE(dc.balance_change, 0::numeric)
) * s.daily_rate
)::numeric AS calculated_interest,
s.daily_rate,
s.as_of_date
FROM seeds AS s
LEFT JOIN daily_changes AS dc
ON dc.billing_cycle_id = s.billing_cycle_id
AND dc.change_date = s.start_date
WHERE s.start_date <= s.as_of_date
UNION ALL
-- 后续日期：前一天余额 + 前一天利息 + 当天交易
SELECT
i.billing_cycle_id,
i.calculation_date + 1,
next_day.amount,
(next_day.amount * i.daily_rate)::numeric,
i.daily_rate,
i.as_of_date
FROM interest_schedule AS i
LEFT JOIN daily_changes AS dc
ON dc.billing_cycle_id = i.billing_cycle_id
AND dc.change_date = i.calculation_date + 1
CROSS JOIN LATERAL (
SELECT (
i.amount
+ i.calculated_interest
+ COALESCE(dc.balance_change, 0::numeric)
)::numeric AS amount
) AS next_day
WHERE i.calculation_date < i.as_of_date
)
SELECT
billing_cycle_id,
calculation_date AS date,
amount,
CASE
WHEN calculation_date < as_of_date
THEN calculated_interest
ELSE NULL
END AS interest
FROM interest_schedule
ORDER BY
billing_cycle_id,
calculation_date;题目中第一个账单周期的结果如下：
billing_cycle_id | date | amount | interest
-----------------+------------+-----------+----------
1 | 2017-12-01 | 4000 | 100
1 | 2017-12-02 | 2100 | 52.5
1 | 2017-12-03 | 2152.5 | 53.8125
1 | 2017-12-04 | 2206.3125 | NULL
计算过程
账单周期结束前的欠款是：
6000 - 2000 = 4000
第一天产生的利息是：
4000 × 0.025 = 100
12 月 2 日发生了一笔 2000 的还款，当天余额为：
4000 + 100 - 2000 = 2100
当天利息为：
2100 × 0.025 = 52.5
12 月 3 日的余额继续根据前一天结果计算：
2100 + 52.5 = 2152.5
递归 CTE 使用的公式就是：
当天余额 = 前一天余额 + 前一天利息 + 当天交易变化
当天利息 = 当天余额 × 每日利率
其中，repayment 对应的交易变化是负数。
为什么窗口函数不够
lag() 能读取数据库中已有的上一行，但这里的上一日余额并不是原始数据，而是前一步的计算结果：
balance[n] = balance[n - 1] + interest[n - 1] - repayment[n]
同时，interest[n - 1] 又取决于 balance[n - 1]。这种逐行反馈的关系无法通过一次普通窗口计算完成，递归 CTE 更适合表达这套计算过程。
generate_series() 可以生成日期，却不能单独解决余额递推。上面的递归查询会在每轮计算中直接生成下一天，因此无需再创建额外的日期序列。
注意事项
示例先合并同一天内的所有交易，再把合计结果计入当天余额并计算利息。如果业务要求精确到小时，或者规定“还款次日才生效”，就不能直接使用 created_at::date，而要根据交易发生的时间段拆分计息。
如果不允许超额还款导致余额变成负数，可以把下一日余额改为：
GREATEST(
i.amount
+ i.calculated_interest
+ COALESCE(dc.balance_change, 0::numeric),
0::numeric
)金额和利率应使用 numeric，不要使用 real 或 double precision，以免在金融计算中产生不必要的浮点误差。实际入账时还要明确舍入规则，例如：
ROUND(amount * daily_rate, 2)每天舍入和账单周期结束时统一舍入会得到不同的最终金额，具体方式必须以实际业务规则为准。
备注：内容仅供参考。