4. 窗口函数入门：ROW_NUMBER、RANK、LAG、LEAD 在时间序列分析中的使用

在组合风险绩效归因中，时间序列数据是最常见的分析对象——每日收益率、滚动波动率、持仓权重变化、因子暴露漂移等。要高效处理这类数据，SQL 窗口函数（Window Functions）是必不可少的工具。与普通聚合函数（GROUP BY）不同，窗口函数不会压缩行数，而是为每一行保留原始记录的同时，计算基于指定窗口的聚合、排序或偏移值。

本章聚焦四个最常用的窗口函数：ROW_NUMBER、RANK、LAG、LEAD，并结合组合绩效归因的实际场景，演示它们在时间序列分析中的具体用法。

4.1 窗口函数核心语法

所有窗口函数都遵循以下基本语法结构：

<窗口函数> OVER (
    [PARTITION BY 分区列]
    [ORDER BY 排序列 [ASC|DESC]]
    [ROWS/RANGE 窗口帧子句]
)

4.2 ROW_NUMBER 与 RANK：排序与去重

ROW_NUMBER() 为分区内的每一行分配一个唯一的连续整数，从 1 开始，即使排序值相同也不会重复。而 RANK() 在遇到相同排序值时，会分配相同的排名，并在后续排名中跳过相应数量。

应用场景： 在组合绩效归因中，经常需要找出每个资产在特定时间窗口内的最大回撤日、最高收益率日，或者剔除重复的持仓快照记录。

-- 示例：找出每个资产在2024年Q1内收益率最高的前3个交易日
WITH ranked_returns AS (
    SELECT
        trade_date,
        asset_code,
        daily_return,
        ROW_NUMBER() OVER (
            PARTITION BY asset_code
            ORDER BY daily_return DESC
        ) AS rn
    FROM daily_returns
    WHERE trade_date BETWEEN '2024-01-01' AND '2024-03-31'
)
SELECT trade_date, asset_code, daily_return
FROM ranked_returns
WHERE rn <= 3
ORDER BY asset_code, rn;

4.3 LAG 与 LEAD：访问前后行数据

LAG(column, offset, default) 返回分区内当前行之前第 offset 行的值；LEAD(column, offset, default) 返回之后第 offset 行的值。这是时间序列分析中最强大的工具之一，用于计算收益率差分、滚动变化量、同比/环比对比等。

核心应用： 计算日收益率、资产权重变化、因子暴露漂移、风险指标（如 VaR）的逐日变化。

-- 示例：计算每个资产的日收益率（基于复权净值）
SELECT
    trade_date,
    asset_code,
    nav,
    LAG(nav) OVER (
        PARTITION BY asset_code
        ORDER BY trade_date
    ) AS prev_nav,
    (nav / LAG(nav) OVER (
        PARTITION BY asset_code
        ORDER BY trade_date
    ) - 1) AS daily_return
FROM asset_nav
WHERE trade_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY asset_code, trade_date;

4.4 窗口函数在时间序列分析中的综合应用

下面通过一个完整的组合风险绩效归因场景，展示四个窗口函数的协同使用：

-- 综合示例：组合风险绩效归因时间序列分析
WITH daily_metrics AS (
    SELECT
        trade_date,
        portfolio_id,
        daily_return,
        -- 计算累计收益率（从组合成立日算起）
        SUM(1 + daily_return) OVER (
            PARTITION BY portfolio_id
            ORDER BY trade_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) - 1 AS cumulative_return,
        -- 计算日收益率排名（用于识别极端收益日）
        RANK() OVER (
            PARTITION BY portfolio_id
            ORDER BY daily_return DESC
        ) AS rank_best,
        RANK() OVER (
            PARTITION BY portfolio_id
            ORDER BY daily_return ASC
        ) AS rank_worst,
        -- 计算前一日收益率（用于自相关分析）
        LAG(daily_return, 1) OVER (
            PARTITION BY portfolio_id
            ORDER BY trade_date
        ) AS prev_return,
        -- 计算后一日收益率（用于验证动量策略）
        LEAD(daily_return, 1) OVER (
            PARTITION BY portfolio_id
            ORDER BY trade_date
        ) AS next_return
    FROM portfolio_returns
    WHERE trade_date BETWEEN '2024-01-01' AND '2024-12-31'
)
SELECT
    trade_date,
    portfolio_id,
    ROUND(daily_return * 100, 4) AS daily_return_pct,
    ROUND(cumulative_return * 100, 2) AS cumulative_return_pct,
    CASE
        WHEN rank_best <= 5 THEN 'TOP5 最佳日'
        WHEN rank_worst <= 5 THEN 'TOP5 最差日'
        ELSE '普通日'
    END AS day_type,
    ROUND(prev_return * 100, 4) AS prev_return_pct,
    ROUND(next_return * 100, 4) AS next_return_pct
FROM daily_metrics
WHERE rank_best <= 5 OR rank_worst <= 5
ORDER BY portfolio_id, trade_date;

4.5 窗口函数性能优化要点

在大型时间序列数据集（如全市场日频数据、多年历史回测数据）上使用窗口函数时，性能是关键考量：

• 分区键选择： PARTITION BY 列应具有高基数且均匀分布，避免数据倾斜导致单个分区过大。
• 排序索引： 为 PARTITION BY 和 ORDER BY 列建立复合索引，可大幅提升窗口函数执行效率。
• 避免不必要的窗口帧： 默认窗口帧（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）在 ORDER BY 列有重复值时可能产生意外结果，建议显式指定 ROWS 帧。
• 嵌套窗口函数： 多层窗口函数嵌套时，考虑使用 CTE 或子查询分层计算，提高可读性和执行计划的可预测性。

4.6 常见错误与避坑指南

• 忽略 PARTITION BY： 如果不指定 PARTITION BY，窗口函数会在整个结果集上计算，这在多资产组合中会导致跨资产的错误排序和偏移。
• ORDER BY 列重复： 当 ORDER BY 列存在重复值时，ROW_NUMBER() 的行为可能不确定，建议添加唯一排序列（如交易时间戳、记录ID）。
• LAG/LEAD 默认值： 当偏移量超出分区边界时，LAG 和 LEAD 返回 NULL。在计算收益率时，第一行的 prev_nav 为 NULL，需要用 COALESCE 或 IS NOT NULL 过滤。
• 窗口帧与 ORDER BY 的交互： 默认窗口帧在 ORDER BY 存在时是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这在有重复排序值时可能导致意外的行包含。显式使用 ROWS 帧可以避免歧义。