TSQL窗口函数来计算利息

huangapple go评论60阅读模式
英文:

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

TSQL窗口函数来计算利息

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

huangapple
  • 本文由 发表于 2023年7月10日 20:07:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76653575.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定