如何在SQL中实现锯齿形减法计算利息余额

需求说明 #

基于利息支付表，按日期顺序用总利息依次扣除每条记录的利息，计算剩余余额，直到最后一笔余额为0：

1. 先计算指定账户的利息总和（示例中为3641.18）
2. 用总利息减去第一笔利息，得到第一笔剩余余额
3. 用剩余余额继续减去下一笔利息，以此类推，最后一笔余额为0

问题分析 #

你原有的SQL查询直接用总利息减去单条记录的利息，没有计算截至当前记录的累计利息，因此无法实现递减退去的效果。此外，查询中关联的dbo.axsp_get_timebands()及对应的case条件与当前需求无关，建议移除以简化逻辑。

修正后的SQL查询 #

以下代码基于SQL Server编写，使用窗口函数实现累计利息计算，最终得到预期的余额：

WITH account_total AS (
    -- 计算指定账户的总利息
    SELECT acct, SUM(interest) AS total_interest
    FROM [table]
    WHERE acct = 963
    GROUP BY acct
),
running_interest AS (
    -- 按日期排序，计算截至当前记录的累计利息
    SELECT 
        fc.acct,
        fc.date,
        fc.interest,
        SUM(fc.interest) OVER (PARTITION BY fc.acct ORDER BY fc.date) AS cumulative_interest
    FROM [table] fc
    WHERE fc.acct = 963
)
-- 用总利息减去累计利息，得到剩余余额
SELECT 
    ri.acct,
    ri.date,
    ri.interest,
    ROUND(at.total_interest - ri.cumulative_interest, 2) AS outstanding
FROM running_interest ri
JOIN account_total at ON ri.acct = at.acct
ORDER BY ri.date;

预期结果 #

执行上述查询后，将得到符合要求的结果：

逻辑说明 #

1. account_total CTE：单独计算指定账户的总利息，确保后续计算的基准值正确
2. running_interest CTE：使用窗口函数SUM(interest) OVER (PARTITION BY acct ORDER BY date)，按账户分组、日期排序，计算每一条记录及之前所有记录的利息累计值
3. 最终计算：用总利息减去累计利息，得到当前记录扣除后的剩余余额，ROUND函数保证结果保留两位小数

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