计算累积产品价值

Calculate cumulative product value(计算累积产品价值)
本文介绍了计算累积产品价值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有以下数据库表:

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

请参阅演示.

这篇关于计算累积产品价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Error casting varchar containing a number expressed in scientific notation to decimal(将包含以科学记数法表示的数字的 varchar 转换为十进制时出错)
SQL Server loop for changing multiple values of different users(用于更改不同用户的多个值的 SQL Server 循环)
SQL Server 2008: Can a multi-statement UDF return a UDT?(SQL Server 2008:多语句 UDF 能否返回 UDT?)
Merge columns in sql(合并sql中的列)
SQL Column is invalid in ORDER BY, not contained in aggregate or GROUP BY(SQL 列在 ORDER BY 中无效,未包含在聚合或 GROUP BY 中)
T-SQL : UNION ALL view not updatable because a partitioning column was not found(T-SQL:UNION ALL 视图不可更新,因为未找到分区列)