SQL Server 2022窗口函数计算利息未累计问题求助

问题根源 #

你的代码仅基于累计本金计算当日利息，未将前一日的本息余额作为计算基数，导致利息无法滚动累计（复利）。窗口函数sum(amount)只能累加本金，无法自动包含之前产生的利息——比如第三天的利息仅基于前两日的本金总和200计算，完全没把第二天产生的利息纳入计算基数，自然得不到你期望的累计效果。

修正方案 #

要实现利息滚动累计，需要用递归CTE逐行计算，因为每日的本息余额依赖前一日的计算结果，这是普通窗口函数无法直接做到的（窗口函数无法引用自定义的计算列）。

修正后的代码：

with t as 
(
    select 
        cast(date as date) as date, 
        amount, 
        perc,
        -- 给每行添加序号，用于递归关联
        row_number() over(order by date) as rn
    from 
        (values
            ('2000-1-1',100,0.1),
            ('2000-1-2',100,0.1),
            ('2000-1-3',100,0.1),
            ('2000-1-4',100,0.1),
            ('2000-1-5',100,0.1),
            ('2000-1-6',100,0.1)
        ) as t(date, amount, perc)
),
recursive_cte as (
    -- 锚点成员：第一天的记录，无前置金额，利息为0，余额即本金
    select 
        date, 
        amount, 
        perc,
        cast(0.0 as decimal(18,4)) as interest,
        cast(amount as decimal(18,4)) as balance,
        rn
    from t
    where rn = 1

    union all

    -- 递归成员：从第二天开始，基于前一日的本息余额计算当日利息
    select 
        t.date, 
        t.amount, 
        t.perc,
        -- 当日利息 = 前一日余额 × 日利率（年利率/365）
        cast(rc.balance * t.perc / 365 as decimal(18,4)) as interest,
        -- 当日余额 = 前一日余额 + 当日本金 + 当日利息
        cast(rc.balance + t.amount + (rc.balance * t.perc / 365) as decimal(18,4)) as balance,
        t.rn
    from t
    join recursive_cte rc on t.rn = rc.rn + 1
)
select 
    date, 
    amount, 
    perc, 
    interest, 
    balance
from recursive_cte
order by date;

计算结果验证 #

运行后你会得到期望的累计效果：

• 第一天余额：100.0000
• 第二天利息：≈0.0274，余额：200.0274
• 第三天利息：≈0.0548，余额：≈300.0822（四舍五入后为300.08）

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