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

问题背景 #

有一张存储股票模拟日收益率的表portfolio，字段说明：

• date_simul：模拟执行日期
• Stock：模拟股票名称
• N Simu：单股票模拟次数（不同股票对应1000、5000或10000）
• simul：股票的第n次模拟值
• FutureDate：模拟的未来日期
• Return：该日期的模拟日收益率（小数以逗号分隔，如-0,0073）

需求是计算累计收益率：第1天为(1+r1)-1，第2天为(1+r1)*(1+r2)-1，以此类推。尝试用exp(sum(ln(1+Return))) -1的公式实现，但现有SQL未成功。

现有SQL的问题 #

原SQL存在以下关键问题：

1. 窗口函数未按股票+模拟执行日期+模拟次数分组，会导致不同股票/模拟组的收益率混算
2. 字段N Simu包含空格，未做转义处理，触发语法错误
3. SELECT列表中Return与计算字段之间缺失逗号，语法错误
4. FutureDate直接按字符串排序，会出现日期逻辑错误（如示例中02/09/22实际早于01/10/22，字符串排序会颠倒顺序）
5. Return字段用逗号作为小数分隔符，无法直接参与数值计算

修正后的SQL #

SELECT 
    date_simul,
    Stock,
    `N Simu`, -- 转义带空格的字段名，不同数据库可替换为双引号（如PostgreSQL）
    FutureDate,
    Return,
    -- 转换小数格式后，按分组计算累计收益率
    EXP(SUM(LN(1 + REPLACE(Return, ',', '.')::FLOAT)) OVER (
        PARTITION BY date_simul, Stock, `N Simu` 
        ORDER BY TO_DATE(FutureDate, 'DD/MM/YY') ASC -- 按实际日期排序，需根据数据库调整函数
    )) - 1 AS Cumul
FROM portfolio
ORDER BY Stock, TO_DATE(FutureDate, 'DD/MM/YY') ASC;

数据库适配说明 #

不同数据库的日期转换函数略有差异，需对应调整：

• PostgreSQL: TO_DATE(FutureDate, 'DD/MM/YY')
• MySQL: STR_TO_DATE(FutureDate, '%d/%m/%y')
• SQL Server: CONVERT(DATE, FutureDate, 3)

修正逻辑说明 #

1. 分组隔离：通过PARTITION BY date_simul, Stock, N Simu``确保每个股票的每一组模拟数据独立计算累计收益率，避免跨组混淆
2. 数值转换：用REPLACE(Return, ',', '.')::FLOAT将逗号分隔的小数转为数据库可识别的数值类型
3. 正确排序：将字符串日期转为日期类型后排序，保证累计收益率按时间顺序计算
4. 语法修复：补充缺失的逗号，转义带空格的字段名，消除语法错误

结果验证 #

按实际日期顺序计算后，示例数据的正确累计结果如下（注：原示例的期望结果因日期排序错误导致累计值逻辑有误，以下为时间顺序下的正确结果）：

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