时间戳

首页 > 解决方案 > 如何在 SQL 中计算复合运行总计

sql - 如何在 SQL 中计算复合运行总计

问题描述

我有一张这样的桌子：

Year, DividendYield
1950, .1
1951, .2
1952, .3

我现在想计算总运行份额。换句话说，如果将股息再投资于新股，它现在看起来像这样：

1950 年 1 月 1 日购买的原始股票数量为 1

1950, .1, 1.1 -- yield of .1 reinvested in new shares results in .1 new shares, totaling 1.1
1951, .2, 1.32 -- (1.1 (Prior Year Total shares) * .2 (dividend yield) + 1.1 = 1.32)
1953, .3, 1.716 -- (1.32 * .3 + 1.32 = 1.716)

我能想到的最接近的是：

declare @startingShares int = 1
; with cte_data as (
    Select *, 
    @startingShares * DividendYield as NewShares, 
    (@startingShares * DividendYield) + @startingShares as TotalShares from DividendTest
)
select *, Sum(TotalShares) over (order by id) as RunningTotal from cte_data

但只有第一行是正确的。

Id  Year    DividendYield   NewShares   TotalShares RunningTotal
1   1950    0.10    0.10    1.10    1.10
2   1951    0.20    0.20    1.20    2.30
3   1953    0.30    0.30    1.30    3.60

如何使用 SQL 执行此操作？我试图不诉诸循环来处理这个。

标签： sqlsql-servercommon-table-expression

解决方案

你想要一个累积乘法。我认为相关的 CTE 实际上是最简单的解决方案：

with tt as (
      select t.*, row_number() over (order by year) as seqnum
      from t
     ),
     cte as (
      select tt.year, convert(float, tt.yield) as yield, tt.seqnum
      from tt
      where seqnum = 1
      union all
      select tt.year, (tt.yield  + 1) * (cte.yield + 1) - 1, tt.seqnum
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select cte.*
from cte;

这是一个 db<>fiddle。

您还可以使用日志和指数来表达这一点：

select t.*,
       exp(sum(log(1 + yield)) over (order by year)) - 1
from t;

这对于大多数用途来说应该没问题，但我发现对于更长的系列，这比递归 CTE 更快地引入数字错误。

推荐阅读