PostgreSQL中如何基于前一行递归计算列值？

需求说明：需要从表中某一行（已有非空computed值）开始，后续每行的computed列值基于前一行的结果进行计算（示例中为加1，实际场景会使用复杂数学公式）；若当前行原有computed值非空则保留，否则使用递推结果。

输入数据 #

symbol  date                    close   computed
TBBB    2024-02-22 14:30:00+00  19.05   
TBBB    2024-02-23 14:30:00+00  19.55   
TBBB    2024-02-24 14:30:00+00  20.6    
TBBB    2024-02-25 14:30:00+00  21.3    
TBBB    2024-02-26 14:30:00+00  20.43   20.4
TBBB    2024-02-27 14:30:00+00  20.21   
TBBB    2024-02-28 14:30:00+00  20.74   
TBBB    2024-02-29 14:30:00+00  20.09   
TBBB    2024-03-01 14:30:00+00  20.79   
TBBB    2024-03-02 14:30:00+00  20.87   
TBBB    2024-03-03 14:30:00+00  20.69   
TBBB    2024-03-04 14:30:00+00  20.19   
TBBB    2024-03-05 14:30:00+00  20.9    
TBBB    2024-03-06 14:30:00+00  20.99   
TBBB    2024-03-07 14:30:00+00  21.28   
TBBB    2024-03-08 14:30:00+00  21.27   

预期结果 #

symbol  date                    close   computed
TBBB    2024-02-22 14:30:00+00  19.05   
TBBB    2024-02-23 14:30:00+00  19.55   
TBBB    2024-02-24 14:30:00+00  20.6    
TBBB    2024-02-25 14:30:00+00  21.3    
TBBB    2024-02-26 14:30:00+00  20.43   20.4
TBBB    2024-02-27 14:30:00+00  20.21   21.4
TBBB    2024-02-28 14:30:00+00  20.74   22.4
TBBB    2024-02-29 14:30:00+00  20.09   23.4
TBBB    2024-03-01 14:30:00+00  20.79   24.4
TBBB    2024-03-02 14:30:00+00  20.87   25.4
TBBB    2024-03-03 14:30:00+00  20.69   26.4
TBBB    2024-03-04 14:30:00+00  20.19   27.4
TBBB    2024-03-05 14:30:00+00  20.9    28.4
TBBB    2024-03-06 14:30:00+00  20.99   29.4
TBBB    2024-03-07 14:30:00+00  21.28   30.4
TBBB    2024-03-08 14:30:00+00  21.27   31.4

尝试过的无效代码 #

初始无关示例（基于close计算computed） #

WITH computed_values AS (SELECT symbol,
                           date,
                           close,
                           CASE
                               WHEN LAG(computed, 5) OVER (PARTITION BY symbol ORDER BY date) IS NULL THEN null
                               ELSE (close * (2.0 / (period + 1))) +
                                    (LAG(computed, 5) OVER (PARTITION BY symbol ORDER BY date) * (1 - (2.0 / (period + 1))))
                               END AS computed
                    FROM (SELECT symbol,
                                 date,
                                 close,
                                 close AS computed,
                                 5     AS period
                          FROM my_table
                          WHERE symbol = 'TBBB' 
                          ORDER BY date) subquery)
SELECT *
FROM computed_values;

AI生成的无效递归代码 #

WITH NumberedRows AS (
    SELECT 
        symbol, 
        date, 
        close,
        ROW_NUMBER() OVER (ORDER BY date ASC) AS row_num
    FROM my_table
),
KnownClose AS (
    SELECT 
        symbol, 
        date, 
        close,
        row_num
    FROM NumberedRows
    WHERE close IS NOT NULL
),
RecursiveClose AS (
    SELECT 
        symbol,
        date,
        close,
        row_num
    FROM KnownClose
    UNION ALL
    SELECT
        nr.symbol,
        nr.date,
        rc.close - 2 AS close,
        nr.row_num
    FROM RecursiveClose rc
    JOIN NumberedRows nr ON nr.row_num = rc.row_num - 1
    WHERE nr.close IS NULL
    
    UNION ALL

    SELECT
        nr.symbol,
        nr.date,
        rc.close + 2 AS close,
        nr.row_num
    FROM RecursiveClose rc
    JOIN NumberedRows nr ON nr.row_num = rc.row_num + 1
    WHERE nr.close IS NULL
) SELECT symbol,
    date,
    close
FROM RecursiveClose
ORDER BY row_num;

正确解决方案 #

递归CTE是实现这类递推计算的合适方式，核心是先对数据按顺序编号，然后从有初始computed值的行开始，逐行向后计算。以下是适配需求的代码：

WITH numbered_data AS (
    SELECT 
        symbol,
        date,
        close,
        computed,
        -- 按symbol分区、date排序生成行号，确保顺序正确
        ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date) AS row_num
    FROM my_table
),
recursive_computed AS (
    -- 锚点：所有已有非空computed值的行
    SELECT 
        symbol,
        date,
        close,
        computed,
        row_num
    FROM numbered_data
    WHERE computed IS NOT NULL
    
    UNION ALL
    
    -- 递归部分：逐行向后计算，基于前一行的computed值
    SELECT 
        nd.symbol,
        nd.date,
        nd.close,
        -- 这里替换为你的复杂公式，示例为前一行值+1
        rc.computed + 1 AS computed,
        nd.row_num
    FROM recursive_computed rc
    JOIN numbered_data nd 
        ON nd.symbol = rc.symbol 
        AND nd.row_num = rc.row_num + 1
    WHERE nd.computed IS NULL -- 仅处理原有computed为空的行
)
-- 合并原始数据中未被递归覆盖的行（即初始computed为空且在起始行之前的行）
SELECT 
    nd.symbol,
    nd.date,
    nd.close,
    COALESCE(rc.computed, nd.computed) AS computed
FROM numbered_data nd
LEFT JOIN recursive_computed rc 
    ON nd.symbol = rc.symbol 
    AND nd.row_num = rc.row_num
ORDER BY nd.symbol, nd.date;

代码说明 #

1. numbered_data CTE：为每行生成唯一的行号，按symbol分区、date排序，保证递推的顺序正确性。
2. recursive_computed CTE：
   • 锚点查询：筛选出所有已有非空computed值的行，作为递推的起点。
   • 递归查询：通过行号关联前一行，计算当前行的computed值（示例中为前一行值+1，实际可替换为任意复杂公式），仅处理原有computed为空的行。
3. 最终查询：通过左连接合并原始数据和递归计算结果，用COALESCE确保保留原始行的非空computed值，同时填充递推计算的结果。

如果存在多个symbol，该代码会自动按symbol分区独立计算，无需额外修改。

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