计算累积产品价值

问题描述

我有以下数据库表:

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)

预期结果:(按日期升序计算的索引)

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 执行此操作?我尝试了以下查询，但出现错误:

How can I do this using SQL? I tried the following query but getting an error:

窗口函数不能在另一个窗口函数或聚合的上下文中使用.

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

推荐答案

从数学上来说，你想要的结果相当于这个产品:

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].

该产品可以通过以下方式获得:

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

请参阅演示.