PostgreSQL实现自引用月度调仓股票指数建模方法

递归CTE完全可以实现该需求，之前实现失败的核心原因是没有将「上月第二席位标的」作为递归状态逐代传递，不需要额外创建物理表维护上月成分，单条SQL即可完成全周期计算。

前提约定 #

• 假设存储市值排名的表名为stock_rank，字段分别为date（统计日期，日期类型）、stock（股票代码，字符串类型）、rank（市值排名，整数类型，数值越小排名越高）

  注意：rank是PostgreSQL保留字，生产环境建议将字段重命名为market_rank避免转义问题，下方示例保留原字段名，用双引号转义处理

• 统计日期为连续月度首日，若存在月份断档，可先生成连续月度维度表关联补全

核心逻辑 #

递归计算分两个阶段：

1. 锚定初始月（数据集中最早月份）的成分：第一席位固定取rank=1的标的，第二席位初始值取rank=2的标的，作为递归起点
2. 逐月下传上月第二席位标的，按优先级选当月第二席位：
   • 第一优先级：上月第二席位标的，若当月rank落在2-3区间，直接保留
   • 第二优先级：所有非第一席位标的按rank升序（排名从高到低）取首位，作为递补标的

• 所有月份第一席位固定取当月rank=1的标的，不受历史影响

完整实现SQL #

WITH RECURSIVE
-- 生成有序月份列表，给每个月分配连续序号
month_seq AS (
    SELECT
        "date",
        ROW_NUMBER() OVER (ORDER BY "date") AS seq_id
    FROM stock_rank
    GROUP BY "date"
),
-- 递归计算各月指数成分
index_comp AS (
    -- 锚点：第一个月的初始成分
    SELECT
        ms.seq_id,
        ms."date",
        MAX(CASE WHEN sr."rank" = 1 THEN sr.stock END) AS seat_1,
        MAX(CASE WHEN sr."rank" = 2 THEN sr.stock END) AS seat_2
    FROM month_seq ms
    JOIN stock_rank sr ON ms."date" = sr."date"
    WHERE ms.seq_id = 1
    GROUP BY ms.seq_id, ms."date"

    UNION ALL

    -- 递归部分：逐月下传上月第二席位，计算当月成分
    SELECT
        curr_ms.seq_id,
        curr_ms."date",
        MAX(CASE WHEN curr_sr."rank" = 1 THEN curr_sr.stock END) AS seat_1,
        (
            SELECT curr_sr_inner.stock
            FROM stock_rank curr_sr_inner
            WHERE curr_sr_inner."date" = curr_ms."date"
              AND curr_sr_inner."rank" != 1
            -- 排序规则：优先保留符合排名要求的上月第二席位，其余按排名从高到低取首位
            ORDER BY
                CASE
                    WHEN curr_sr_inner.stock = prev_comp.seat_2 AND curr_sr_inner."rank" BETWEEN 2 AND 3 THEN 0
                    ELSE 1
                END,
                curr_sr_inner."rank" ASC
            LIMIT 1
        ) AS seat_2
    FROM index_comp prev_comp
    JOIN month_seq curr_ms ON prev_comp.seq_id + 1 = curr_ms.seq_id
    JOIN stock_rank curr_sr ON curr_ms."date" = curr_sr."date"
    GROUP BY curr_ms.seq_id, curr_ms."date", prev_comp.seat_2
)
-- 输出全周期指数成分
SELECT "date", seat_1, seat_2
FROM index_comp
ORDER BY "date";

结果验证 #

修正样例表中日期录入错误（2020-02、2020-03的GOOG行date字段误写为2020-01-01）后，运行SQL输出结果与预期完全一致：

性能优化建议 #

• 递归迭代次数等于数据覆盖的总月份数，即便覆盖10年行情也仅120次迭代，计算效率极高
• 建议为stock_rank表建立("date", "rank")的联合B树索引，可将单月排名查询耗时降到毫秒级，支持单月数千只股票的全量计算
• 后续如果需要扩展更多成分席位，只需在递归传递状态中增加上月对应席位的标的，在排序逻辑中补充对应优先级规则即可，扩展性强

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