sql - 如何在 SQLite 中计算不同利率的复利
问题描述
我需要按产品计算复利,其中利率可能因年而异。
下面的简化表。initial_value
是产品在第 1 年年初final_value
的价值,是包括相应年末利息在内的价值。
product year initial_value interest final_value
a 1 10000 0.03 10,300.00
a 2 0.02 10,506.00
a 3 0.01 10,611.06
b 1 15000 0.04 15,600.00
b 2 0.06 16,536.00
b 3 0.07 17,693.52
要重新创建表:
CREATE TABLE temp (year INTEGER, product CHARACTER,
initial_value DECIMAL(10,2), interest DECIMAL(10,2));
INSERT INTO temp VALUES (1, 'a', 10000, 0.03);
INSERT INTO temp VALUES (2, 'a', 0, 0.02);
INSERT INTO temp VALUES (3, 'a', 0, 0.01);
INSERT INTO temp VALUES (1, 'b', 15000, 0.04);
INSERT INTO temp VALUES (2, 'b', 0, 0.06);
INSERT INTO temp VALUES (3, 'b', 0, 0.07);
以 product = a 为例,第 3 年的数字应计算为10000 * (1+0.03) * (1+0.02) * (1+0.01)
产品的年份和数量可能会有所不同,因此我想避免按年份转置数据,但不幸的是,我想不出另一种方法来跨行相乘以获得所需的结果。
解决方案
你可以使用RECURSIVE CTE
:
WITH RECURSIVE cte AS (
SELECT year, product, initial_value, interest, initial_value*(1+ interest) AS s
FROM temp
WHERE initial_value <> 0
UNION ALL
SELECT t.year, t.product, t.initial_value, t.interest, s * (1+t.interest)
FROM temp t
JOIN cte c
ON t.product = c.product
AND t.year = c.year+1
)
SELECT *
FROM cte
ORDER BY product, year;
输出:
┌──────┬─────────┬───────────────┬──────────┬─────────────┐
│ year │ product │ initial_value │ interest │ final_value │
├──────┼─────────┼───────────────┼──────────┼─────────────┤
│ 1 │ a │ 10000 │ 0.03 │ 10300 │
│ 2 │ a │ 0 │ 0.02 │ 10506 │
│ 3 │ a │ 0 │ 0.01 │ 10611.06 │
│ 1 │ b │ 15000 │ 0.04 │ 15600 │
│ 2 │ b │ 0 │ 0.06 │ 16536 │
│ 3 │ b │ 0 │ 0.07 │ 17693.52 │
└──────┴─────────┴───────────────┴──────────┴─────────────┘
编辑
只是为了纯粹的乐趣,我使用窗口函数重写了它:
SELECT *,
FIRST_VALUE(initial_value) OVER(PARTITION BY product ORDER BY year)
* exp (sum (ln (1+interest)) OVER(PARTITION BY product ORDER BY year))
FROM temp;
推荐阅读
- sql - 根据 ID 比较来自同一输入文件的行之间的日期,并使用 SQL Server 按增量日期复制行
- mysql - PL-SQL - 我可以将枚举作为函数参数传递吗?
- python - 带有日期和时间的 reportlab LinePlot 轴
- java - 无法向尤里卡服务器注册 spring 配置客户端
- c# - 图像比较算法
- sql-server - sql server:创建序列的过程
- jquery - Django中的引导滑块不起作用
- google-apps-script - 是否可以在 Google Apps 脚本中创建 getCommenters() 功能?
- apache-kafka - 需要使用 Oracle Golden Gate Big-Data 和 kafka Handler 基于分区从 oracle 12c 复制数据
- .net - .net 标准中的 IHttpContextAccessor 引用自 .net 框架