基于日收益率计算股票模拟累计收益率的SQL查询问题

问题背景 #

现有存储股票模拟日收益率的表portfolio，字段定义：

• date_simul：模拟执行日期
• Stock：模拟股票名称
• N Simu：单只股票的模拟次数（取值1000、5000、10000）
• simul：股票的第n次模拟值
• FutureDate：模拟的未来日期
• Return：对应日期的模拟日收益率

原SQL尝试计算累计收益率但未得到预期结果：

select date_simul, Stock, N Simu, FutureDate, Return
        exp(sum(ln(1+Return)) over (order by FutureDate asc)) - 1 as cumul
from portfolio
order by Stock, FutureDate; 

数据示例 #

date_simul|Stock|N Simu|FutureDate| Return
 30/09/22 |  A  | 1000 | 01/10/22 | -0,0073
 30/09/22 |  A  | 1000 | 02/09/22 | 0,0078
 30/09/22 |  A  | 1000 | 03/09/22 | 0,0296
 30/09/22 |  A  | 1000 | 04/09/22 | 0,0602
 30/09/22 |  A  | 1000 | 05/10/22 | -0,0177

期望结果 #

date_simul|Stock|N Simu|FutureDate| Return | Cumul
 30/09/22 |  A  | 1000 | 01/10/22 | -0,0073| -0,0073
 30/09/22 |  A  | 1000 | 02/09/22 | 0,0078 | 0,0005
 30/09/22 |  A  | 1000 | 03/09/22 | 0,0296 | 0,0301
 30/09/22 |  A  | 1000 | 04/09/22 | 0,0602 | 0,6321
 30/09/22 |  A  | 1000 | 05/10/22 | -0,0177| 0,06144

问题分析与修正方案 #

核心问题 #

1. 窗口函数无分区条件：未按date_simul、Stock、N Simu、simul分区，导致不同模拟组的收益被混合累计。
2. 日期排序错误：FutureDate为字符串格式，直接排序会按字符顺序（如02/09/22排在01/10/22前），需转换为日期类型后排序。
3. 小数格式不兼容：Return字段用逗号作为小数分隔符，数据库无法直接识别为数值，需先转换格式。

修正后的SQL #

以PostgreSQL为例（其他数据库可调整日期转换和数值转换函数）：

SELECT
    date_simul,
    Stock,
    "N Simu",
    FutureDate,
    Return,
    ROUND(EXP(SUM(LN(1 + REPLACE(Return, ',', '.')::NUMERIC)) OVER (
        PARTITION BY date_simul, Stock, "N Simu", simul
        ORDER BY TO_DATE(FutureDate, 'DD/MM/YY') ASC
    )) - 1, 5) AS Cumul
FROM portfolio
ORDER BY Stock, TO_DATE(FutureDate, 'DD/MM/YY') ASC;

关键修改说明 #

• 分区子句：添加PARTITION BY date_simul, Stock, "N Simu", simul，确保每组模拟的累计收益独立计算。
• 日期转换：用TO_DATE(FutureDate, 'DD/MM/YY')将字符串日期转为日期类型，保证排序逻辑正确。
• 数值转换：用REPLACE(Return, ',', '.')::NUMERIC将逗号分隔的小数转为数据库可识别的数值类型，避免计算错误。
• 结果格式化：用ROUND(..., 5)将累计收益保留5位小数，与期望结果格式一致。

内容的提问来源于stack exchange，提问作者Guillem