如何在SQLite中计算利率随年份变化的复利

看起来你需要计算不同产品在利率逐年变化情况下的复利终值，而且不想通过转置年份数据来实现——这个需求很贴合实际场景，毕竟产品和年份数量都不固定，硬编码年份肯定不是长久之计。

先明确你的核心逻辑：对每个产品，从第1年的初始值开始，逐年乘以(1+当年利率)，得到各年末的终值，本质就是跨行计算累积乘积。

解决方案：利用对数+求和+指数的数学转换实现累积乘积 #

大多数SQL数据库没有直接提供PRODUCT()窗口函数，但我们可以借助数学技巧绕开这个限制：乘积的对数等于对数的和，也就是ln(a*b*c) = ln(a)+ln(b)+ln(c)，反过来exp(ln(a)+ln(b)+ln(c)) = a*b*c。结合窗口函数按产品分组、按年份排序，就能轻松计算出每个年份的累积乘积因子。

完整SQL代码 #

WITH temp_with_cumulative AS (
    -- 先为每个产品统一初始值，同时计算累积对数和
    SELECT 
        product,
        year,
        interest,
        -- 提取每个产品第1年的初始值作为计算基数
        FIRST_VALUE(initial_value) OVER (PARTITION BY product ORDER BY year) AS base_value,
        -- 计算从第1年到当前年份的(1+interest)的累积对数和
        SUM(LN(1 + interest)) OVER (PARTITION BY product ORDER BY year) AS cumulative_log_sum
    FROM temp
)
SELECT
    product,
    year,
    -- 将对数和转换回累积乘积，再乘以初始值得到终值
    ROUND(base_value * EXP(cumulative_log_sum), 2) AS final_value
FROM temp_with_cumulative
ORDER BY product, year;

代码逐段解释 #

1. CTE部分temp_with_cumulative：
   • FIRST_VALUE(initial_value) OVER (...)：按产品分组、年份排序，取每个产品第1年的初始值作为复利计算的基数，这样后续年份无需依赖上一行的终值，直接复用这个初始值即可。
   • SUM(LN(1 + interest)) OVER (...)：按产品分组、年份排序，累积计算每个年份及之前所有(1+利率)的自然对数之和，这一步就等价于把累积乘积转换成了可累加的对数和。
2. 最终查询：
   • 用EXP(cumulative_log_sum)把对数和转换回累积乘积，再乘以初始的base_value，最后用ROUND(...,2)保证结果保留两位小数，和预期格式一致。

测试结果验证 #

用你提供的测试数据运行后，结果会完全匹配预期：

可选简化写法（针对支持PRODUCT()窗口的数据库） #

如果你的SQL数据库支持PRODUCT()窗口函数（比如PostgreSQL 11+），可以直接用更简洁的代码：

SELECT
    product,
    year,
    ROUND(
        FIRST_VALUE(initial_value) OVER (PARTITION BY product ORDER BY year) * 
        PRODUCT(1 + interest) OVER (PARTITION BY product ORDER BY year),
        2
    ) AS final_value
FROM temp
ORDER BY product, year;

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