在SQL中运行累积收益 [英] Running cumulative return in sql

问题描述

是否希望获得一系列每日收益的连续累积收益?我知道可以使用exp和sum来解决此问题，但是我的返回序列不是使用LN来计算的.

Looking to have a running cumulative return for a series of daily returns? I know this can be solved using exp and sum, but my return series is not calculated using LN.

希望在不使用循环的情况下解决此问题，因为它们在sql中效率很低. 快速运行很重要.

Hoping to solve this without using loops, as they are very inefficient in sql. Its important to make this run fast.

数据集:

期望的结果

推荐答案

这是您想要的吗?

select t.*,
       (select exp(sum(log(1 + return))) - 1
        from table t2
        where t2.date <= t.date
       ) as cumereturn
from table t;

exp()和log()的功能在您使用的数据库中可能有所不同.在许多数据库中，您还可以使用:

The functions for exp() and log() may be different in the database you are using. In many databases, you can also use:

select t.*, exp(sum(log(1 + return) over (order by date)) - 1
from table t;

我认为任何数据库都没有内置的product()聚合功能. las.

I don't think any database has a built in product() aggregation function. Alas.

这篇关于在SQL中运行累积收益的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！