問(wèn)題描述
我有以下數(shù)據(jù)庫(kù)表:
Date Return Index
01-01-2020 0.1 Null
01-02-2020 0.2 Null
01-03-2020 0.3 Null
我想使用以下公式更新索引值:
I would like to update the Index value using the following formula:
Index = (Previous_Month_Index * Return) + Previous_Month_Index (Use 100 for Previous_Month_Index for the first month)
預(yù)期結(jié)果:(按日期升序計(jì)算的索引)
Expected Result: (Index to be calculated order by Date asc)
Date Return Index
01-01-2020 0.1 110 -- (100 + 10)
01-02-2020 0.2 132 -- (110 + (110 * 0.20)) = 110 + 22 = 132
01-03-2020 0.3 171.6 -- (132 + (132 * 0.30)) = 132 + 39.6 = 171.6
如何使用 SQL 執(zhí)行此操作?我嘗試了以下查詢(xún)，但出現(xiàn)錯(cuò)誤:
How can I do this using SQL? I tried the following query but getting an error:
窗口函數(shù)不能在另一個(gè)窗口函數(shù)或聚合的上下文中使用.
Windowed functions cannot be used in the context of another windowed function or aggregate.
--first, load the sample data to a temp table
select *
into #t
from
(
values
('2020-01-01', 0.10),
('2020-02-01', 0.20),
('2020-03-01', 0.30)
) d ([Date], [Return]);
--next, calculate cumulative product
select *, CumFactor = cast(exp(sum(log(case when ROW_NUMBER() OVER(order by [Date] ASC) = 1 then 100 * [Return] else [Return] end)) over (order by [Date])) as float) from #t;
drop table #t
推薦答案
從數(shù)學(xué)上來(lái)說(shuō)，你想要的結(jié)果相當(dāng)于這個(gè)產(chǎn)品:
Thinking mathematically, the result that you want is equivalent to this product:
100 * (1 + a1) * (1 + a2) * (1 + a3) * ....
其中 a1、a2、a3 是 [Return] 列的值.
where a1, a2, a3 are the values of the column [Return].
該產(chǎn)品可以通過(guò)以下方式獲得:
This product can be obtained by:
100 * EXP(SUM(LOG(1 + [Return])))
你可以在 sql 中這樣做:
and you can do this in sql like this:
SELECT *,
100 * EXP(SUM(LOG(1 + [Return])) OVER (ORDER BY [Date])) [Index]
FROM #t
請(qǐng)參閱演示.
這篇關(guān)于計(jì)算累積產(chǎn)品價(jià)值的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！