如何使用PostgreSQL窗口函数计算运行平衡

Question

我想要一个查询来跟踪抵押贷款账户的利息。为了简单起见，假设利息是每年计算的。还有一次性存款/提款(还款等(。

我想查询这些信息并计算运行余额，大概是使用窗口函数。下面是我要查询的表类型的示例。

year | changes | interest | comment
2020 | 10000   | 2.5      | initial mortgage of 10k
2021 | 0       | 2.0      | next year the rate drops
2022 | 5000    | 2.0      | we borrow an extra 5k
2023 | 0.      | 1.5      | rate drop again

我想要一个计算每年运行余额的查询，如下所示：

year | changes | interest | balance
2020 | 10000   | 2.5      | 10250.0 = 10000 * (1 + 2.5 / 100)
2021 | 0       | 2.0      | 10455.0 = 10250 * (1 + 2.0 / 100)
2022 | 5000    | 2.0      | 15764.1 = (10455 + 5000) * (1 + 2.0 / 100)
2023 | 0.      | 1.5      | 16000.56 = 15764.1 * (1 + 1.5 / 100)

如何在PostgreSQL中做到这一点？

Answer 1

由于需要将上一年的余额乘以当前利息，因此使用递归CTE:最容易实现

WITH RECURSIVE CTE AS (
SELECT t.year, t.changes, t.interest, t.changes * (1.0 + t.interest / 100.0) AS balance
FROM transactions t
WHERE year = (SELECT MIN(year) FROM transactions)
UNION ALL
SELECT t.year, t.changes, t.interest, (t.changes + CTE.balance) * (1.0 + t.interest / 100.0)
FROM transactions t
JOIN CTE ON t.year = CTE.year + 1
)
SELECT year, changes, interest, ROUND(CAST(balance AS numeric), 2) AS balance
FROM CTE

输出：

year    changes     interest    balance
2020    10000       2.5         10250.00
2021    0           2           10455.00
2022    5000        2           15764.10
2023    0           1.5         16000.56

dbfiddle 演示

Answer 2

递归CTE可能是更好的方法。但是使用窗口函数也可以做到这一点。

三个关键思想是：

• 使用exp(sum(ln()))作为product()聚合函数
• 将每个值投影到最近的时间，累积所有兴趣函数
• 除以"累计利息"，直到该值，以调整新的输入值

实际的代码并没有那么复杂：

select t.*,
(sum(changes * running_interest) over (order by year) /
coalesce(prev_running_interest, 1)
) as val
from (select t.*, 
exp(sum(ln(1 + interest / 100)) over (order by year desc)) as running_interest,
exp(sum(ln(1 + interest / 100)) over (order by year desc rows between unbounded preceding and 1 preceding)) as prev_running_interest
from t
) t
order by year;

您将在数据库中注意到<>篡改浮点运算引起的轻微不准确性。您总是可以将小数点后几位转换为更美观的数字。