PostgreSQL如何实现以上一行计算结果为下一行输入的迭代计算？

你要的这种递推计算完全可以用PostgreSQL的SQL实现，不需要借助Python。之前用lag()函数失败是因为它只能获取上一行的固定字段值，无法实现递推式的累积计算。下面提供两种可行方案：

方案一：递归CTE（逻辑直观） #

递归CTE适合处理这种行与行之间有依赖的计算，分为锚点成员（第一行数据）和递归成员（后续行依赖前一行结果）：

WITH RECURSIVE running_calc AS (
    -- 锚点：获取ID=187760的第一行，计算初始结果
    SELECT
        idx,
        date,
        ID,
        stock,
        pending,
        sells,
        stock + pending - sells AS expected_results
    FROM test_table
    WHERE ID = '187760'
    ORDER BY date ASC
    LIMIT 1

    UNION ALL

    -- 递归：关联上一行结果，计算当前行
    SELECT
        t.idx,
        t.date,
        t.ID,
        rc.expected_results AS stock, -- 用上一行的计算结果作为当前stock
        t.pending,
        t.sells,
        rc.expected_results + t.pending - t.sells AS expected_results
    FROM test_table t
    JOIN running_calc rc ON t.date > rc.date
    -- 确保每次只取按日期排序的下一行，避免多匹配
    WHERE NOT EXISTS (
        SELECT 1
        FROM test_table t2
        WHERE t2.ID = t.ID
        AND t2.date > rc.date
        AND t2.date < t.date
    )
    AND t.ID = '187760'
)
SELECT * FROM running_calc ORDER BY date ASC;

方案二：窗口函数（高效简洁） #

仔细拆解计算逻辑会发现：expected_results其实等于第一行的初始stock加上从第一行到当前行所有(pending - sells)的累积和，而当前行的stock则是上一行的expected_results，也就是初始stock加上到上一行的累积和。用窗口函数可以高效实现：

SELECT
    idx,
    date,
    ID,
    -- 当前行的stock：初始stock + 之前所有行(pending-sells)的和
    FIRST_VALUE(stock) OVER (PARTITION BY ID ORDER BY date) + 
    COALESCE(SUM(pending - sells) OVER (PARTITION BY ID ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS stock,
    pending,
    sells,
    -- 预期结果：初始stock + 当前及之前所有行(pending-sells)的和
    FIRST_VALUE(stock) OVER (PARTITION BY ID ORDER BY date) + 
    SUM(pending - sells) OVER (PARTITION BY ID ORDER BY date) AS expected_results
FROM test_table
WHERE ID = '187760'
ORDER BY date ASC;

说明 #

• 方案二的效率远高于递归CTE，适合数据量较大的场景；
• 两种方案都能输出你需要的预期结果，包含更新后的stock列和expected_results列；
• 如果你的表中有多个ID，只需去掉WHERE ID='187760'，窗口函数和递归CTE会自动按ID分区计算。

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