SQL中如何使用窗口函数计算滚动累加的收益率？

必须指定ORDER BY，否则SUM() OVER()返回分区总和而非滚动累加；收益率需用EXP(SUM(LN(1+return)) OVER(...))-1计算复利累计，避免直接求和，并显式声明ROWS框架、处理NULL及建联合索引。

窗口函数里用 SUM() 做滚动累加时，必须指定 ORDER BY

不写 ORDER BY 的 SUM() OVER () 会默认对整个分区做静态求和，不是滚动效果。收益率是时序敏感的，必须按交易日期或时间戳排序才能得到逐期累加结果。

常见错误现象：SUM(return) OVER (PARTITION BY stock_id) 算出来每行都是该股票全部收益率之和，而不是“到当前行为止”的累计值。

• 正确写法必须带 ORDER BY trade_date（或等效的时间列）
• 如果存在相同日期的多条记录，建议再加一个唯一排序字段，比如 ORDER BY trade_date, id，避免窗口边界模糊
• 注意：PostgreSQL 和 SQL Server 支持 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（默认行为），但 MySQL 8.0+ 才支持标准窗口语法，旧版本需用变量模拟

收益率列本身不能直接 SUM() —— 要先转成对数收益率或用连乘逻辑

算术收益率（如 0.02, -0.015）不能简单累加，否则结果失去经济含义。滚动累计应反映复利效应，即 (1+r₁) × (1+r₂) × … × (1+rₙ) − 1。

多数场景下，你真正需要的是累计总收益率，不是收益率的和。

• 推荐方式：用 EXP(SUM(LN(1 + return)) OVER (...)) - 1（适用于所有支持 LN/LOG 和 EXP 的数据库）
• MySQL 中 LN 写作 LOG()，PostgreSQL/SQL Server 用 LN() 或 LOG()（注意底数差异）
• 如果收益率含负值且可能 ≤ -1（如 -1.2），1 + return 会 ≤ 0，LN 报错，此时需过滤或用其他建模方式

用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 显式声明更安全

虽然多数引擎默认就是这个框架，但显式写出能避免在不同数据库或未来版本中行为漂移。特别是当你的排序列有重复值时，RANGE 模式可能把同一天所有记录全纳入当前窗口，而 ROWS 按物理顺序处理更可控。

• 示例：SUM(LN(1 + return)) OVER (PARTITION BY stock_id ORDER BY trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
• 别依赖默认框架——有些 OLAP 引擎（如 Trino）对 RANGE 和 ROWS 的实现差异较大
• 如果要做 N 日滚动（比如 5 日累计），把 UNBOUNDED PRECEDING 换成 4 PRECEDING 即可

性能和 NULL 处理容易被忽略

NULL 收益率会导致整个窗口计算中断（SUM、LN 都跳过 NULL，但 LN(1 + NULL) 是 NULL，进而让后续 EXP 结果为 NULL），线上数据常有缺失，必须提前处理。

• 建议在子查询里用 COALESCE(return, 0) 填充，或用 WHERE return IS NOT NULL 过滤（根据业务决定）
• 大表上跑窗口函数时，确保 PARTITION BY 列和 ORDER BY 列有联合索引，否则排序成本极高
• Oracle 和 PostgreSQL 对 EXP(SUM(LN(...))) 有优化，但 SQLite 和某些 MySQL 版本会逐行计算，速度慢，必要时考虑物化中间结果

实际跑起来之前，先确认你的数据库版本是否支持完整窗口函数语法，再检查收益率字段有没有意外的 NULL 或极端值 —— 这两处出问题，结果看起来“算出来了”，但数字完全不可信。