英文:
TSQL window function to calculate interest
问题
我正在尝试在SQL Server 2022上使用窗口函数计算利息。
with t as
(
select *
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)
)
select
*,
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365 interest,
sum(amount) over (order by date rows between unbounded preceding and current row) +
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365
from
t
是否有人能指出为什么计算的利息没有累积，例如，我希望在第3天余额为300.08，而实际上为300.05（没有考虑从第2天起的利息）。
英文:
I am trying to calculate interest using window functions on SQL Server 2022
with t as
(
select *
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)
)
select
*,
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365 interest,
sum(amount) over (order by date rows between unbounded preceding and current row) +
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365
from
t
Can someone point me to a problem why interest calculated isn't being cummulated f.e. I expect balance to be 300.08 on 3rd while it is 300.05 (interest from 2nd isn't taken into account)
答案1
得分: 1
根据评论中提到的，这不是累积聚合，而是递归聚合。实际上，您的表达式应该像这样：
Amount + PriorBalance + (Interest + (Percentage * PriorBalance) / 365)
为了实现这一点，您需要使用递归公用表达式 (CTE)，因为累积的 SUM（或任何窗口聚合）无法访问先前行的计算值。
因此，这似乎是您需要的解决方案：
WITH t AS (
SELECT date,
amount,
perc,
ROW_NUMBER() OVER (ORDER BY date ASC) AS I
FROM (VALUES(CONVERT(date,'20000101'),100,0.1),
(CONVERT(date,'20000102'),100,0.1),
(CONVERT(date,'20000103'),100,0.1),
(CONVERT(date,'20000104'),100,0.1),
(CONVERT(date,'20000105'),100,0.1),
(CONVERT(date,'20000106'),100,0.1)) as t(date,amount,perc)),
rCTE AS (
SELECT date,
amount,
perc,
I,
CONVERT(numeric(7,6),0.000000) AS interest,
CONVERT(numeric(12,6),0.000000) AS balance
FROM t
WHERE I = 1
UNION ALL
SELECT t.date,
t.amount,
t.perc,
t.I,
CONVERT(numeric(7,6),r.interest + ((t.perc * r.amount)/365)) AS interest,
CONVERT(numeric(12,6),t.amount + r.balance + (r.interest + ((t.perc * r.amount)/365))) AS balance
FROM t t
JOIN rCTE r ON r.I = t.I -1
)
SELECT date,
amount,
perc,
interest,
balance
FROM rCTE;
GO
希望这对您有所帮助。
英文:
As mentioned in the comments, this isn't cumulative aggregation it's recursive aggregation. Effectively your expression wants to read something like:
Amount + PriorBalance + (Interest + (Percentage * PriorBalance) / 365)
To achieve this, you're doing to need to use a recursive CTE, as a cumulative SUM (or any of the windowed aggregated) cannot access the prior rows calculated value.
Therefore this appears to be the solution you need:
WITH t AS (
SELECT date,
amount,
perc,
ROW_NUMBER() OVER (ORDER BY date ASC) AS I
FROM (VALUES(CONVERT(date,'20000101'),100,0.1),
(CONVERT(date,'20000102'),100,0.1),
(CONVERT(date,'20000103'),100,0.1),
(CONVERT(date,'20000104'),100,0.1),
(CONVERT(date,'20000105'),100,0.1),
(CONVERT(date,'20000106'),100,0.1)) as t(date,amount,perc)),
rCTE AS (
SELECT date,
amount,
perc,
I,
CONVERT(numeric(7,6),0.000000) AS interest,
CONVERT(numeric(12,6),0.000000) AS balance
FROM t
WHERE I = 1
UNION ALL
SELECT t.date,
t.amount,
t.perc,
t.I,
CONVERT(numeric(7,6),r.interest + ((t.perc * r.amount)/365)) AS interest,
CONVERT(numeric(12,6),t.amount + r.balance + (r.interest + ((t.perc * r.amount)/365))) AS balance
FROM t t
JOIN rCTE r ON r.I = t.I -1
)
SELECT date,
amount,
perc,
interest,
balance
FROM rCTE;
GO
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论