Как рассчитать составную промежуточную сумму в SQL
У меня есть такая таблица:
Year, DividendYield
1950, .1
1951, .2
1952, .3
Теперь я хочу рассчитать общее количество текущих акций. Другими словами, если дивиденд реинвестировать в новые акции, то теперь это будет выглядеть так:
Первоначальное количество акций, купленных 1 января 1950 г., равно 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? Я пытался не прибегать к циклу для обработки этого.
Ответы 1
Вам нужно кумулятивное умножение. Я думаю, что коррелированный 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<>рабочий пример.
Вы также можете сформулировать это, используя журналы и показатели:
select t.*,
exp(sum(log(1 + yield)) over (order by year)) - 1
from t;
Это должно быть хорошо для большинства целей, но я считаю, что для более длинных рядов это приводит к числовым ошибкам быстрее, чем рекурсивный CTE.
Я нахожу "соединение со сдвигом" очень интересным, никогда не думал об этом раньше.