SQL窗口函数计算滚动累加收益率的实用方法

### 先泼盆冷水：窗口函数算滚动累加，比想象中要“矫情” 很多人在用SQL窗口函数计算滚动收益率时，会掉进同一个坑里——数据看起来“算出来了”，但结果根本经不起推敲。最典型的几个问题，其实都绕不开一个核心：**时序数据，真的不能当普通累加处理**。 先从最基础的陷阱说起。 --- ### 窗口函数里用 `SUM()` 做滚动累加，必须指定 `ORDER BY` 这不只是“写得规范”的问题，而是“否则结果根本不滚”的问题。 如果你写 `SUM(return) OVER (PARTITION BY stock_id)`，抱歉，这算出来的是**该股票所有收益率的静态总和**，每行都一样。你想要的“到当前行为止”的累计值，一句 `ORDER BY` 都没写，它当然不会按时间顺序给你滚动。 正确的做法是： - 必须带上 `ORDER BY trade_date`（或任何能标识时间顺序的列） - 如果同一天有多条记录，最好再加一个唯一排序字段，比如 `ORDER BY trade_date, id`，否则窗口边界会模糊 - 注意：PostgreSQL 和 SQL Server 默认 `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`，但 MySQL 8.0+ 才支持标准窗口语法，旧版本只能用变量模拟 换句话说，**不写 `ORDER BY` 的 `SUM() OVER()`，跟滚动累加没有任何关系**。 --- ### 收益率列本身，不能直接 `SUM()` —— 你得先转成对数收益率 这是个更隐蔽的陷阱。很多人觉得“累计收益率 = 收益率相加”，但算术收益率（比如 0.02, -0.015）是不能直接累加的。那样算出来的结果没有经济含义，因为你忽略了复利效应。 实际业务中，你需要的累计总收益率应该是： `(1+r₁) × (1+r₂) × … × (1+rₙ) − 1` 所以，正确做法是用连乘逻辑： - **推荐方式**：`EXP(SUM(LN(1 + return)) OVER (...)) - 1`（只要你的数据库支持 `LN` 和 `EXP`） - MySQL 里 `LN` 写作 `LOG()`，PostgreSQL/SQL Server 用 `LN()` 或 `LOG()`，注意底数差异 - 有一个坑：如果收益率 ≤ -1（比如 -1.2），`1 + return` 会 ≤ 0，`LN` 直接报错，这时候需要过滤或用其他建模方式 总之，**别偷懒，别直接用 `SUM()` 加收益率**。 --- ### 显式声明 `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` 更安全 虽然很多数据库引擎默认就是这个框架，但显式写出来能避免不同版本或不同数据库之间的行为漂移。尤其是当你的排序列有重复值时，`RANGE` 模式可能把同一天的所有记录全纳入当前窗口，而 `ROWS` 是按物理顺序处理的，更可控。 举个例子： ```sql 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`。收益率字段如果有 `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` 或极端值——这两处出问题，结果看起来“算出来了”，但数字完全不可信。