PostgreSQL能否访问查询上一行输出值解决迭代累积计算问题

可以实现，你需要用到递归公共表表达式（Recursive CTE），它支持在迭代计算时直接读取上一轮输出的结果，完美适配你这种当前行计算依赖之前所有行输出聚合值的场景。

你的问题本质是迭代计算逻辑：每一行的饲料转换率需要基于之前所有行转换后的总重量算出来的平均体重查表获得，普通窗口函数只能基于原始输入的静态数据计算，无法处理这种动态迭代的依赖，递归CTE正好解决这个问题。

具体实现代码 #

WITH RECURSIVE ordered_transactions AS (
    -- 给所有交易按时间排序加行号，方便递归按顺序处理
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
    FROM combined_feed_and_transfer_history
),
recursive_calc AS (
    -- 锚点：处理第一行记录，完成初始值计算
    SELECT 
        ot.rn,
        ot.timestamp,
        ot.farm,
        ot.total_weight_g AS transaction_size_g,
        ot.growth_table,
        ot.number_fish,
        -- 初始总鱼数就是第一行的鱼数
        ot.number_fish AS sum_number_fish,
        -- 初始总重量（转换后）：第一行是转入，直接用交易重量
        ot.total_weight_g AS sum_conv_g,
        -- 初始平均体重
        ot.total_weight_g::numeric / ot.number_fish AS current_avg_weight_g
    FROM ordered_transactions ot
    WHERE ot.rn = 1

    UNION ALL

    -- 递归部分：处理后续每一行，依赖上一行的计算结果
    SELECT 
        ot.rn,
        ot.timestamp,
        ot.farm,
        ot.total_weight_g AS transaction_size_g,
        ot.growth_table,
        ot.number_fish,
        -- 累加鱼数
        rc.sum_number_fish + ot.number_fish AS sum_number_fish,
        -- 计算当前行转换后的重量，累加得到新的总重量
        rc.sum_conv_g + (ot.total_weight_g * CASE WHEN ot.growth_table IS NULL THEN 1 ELSE gc.feed_conversion_rate END) AS sum_conv_g,
        -- 计算新的平均体重，供下一行使用
        (rc.sum_conv_g + (ot.total_weight_g * CASE WHEN ot.growth_table IS NULL THEN 1 ELSE gc.feed_conversion_rate END))::numeric / (rc.sum_number_fish + ot.number_fish) AS current_avg_weight_g
    FROM ordered_transactions ot
    JOIN recursive_calc rc ON ot.rn = rc.rn + 1
    -- 非投喂事件不需要查表，直接走默认转换率1
    LEFT JOIN LATERAL (
        SELECT average_weight_g 
        FROM growth_coefficients
        WHERE growth_table = ot.growth_table
        ORDER BY ABS(average_weight_g - rc.current_avg_weight_g)
        LIMIT 1
    ) closest_gc ON ot.growth_table IS NOT NULL
    -- 拿到对应的转换率
    LEFT JOIN growth_coefficients gc 
        ON gc.growth_table = ot.growth_table 
        AND gc.average_weight_g = closest_gc.average_weight_g
)
-- 输出最终结果
SELECT 
    timestamp,
    sum_number_fish,
    transaction_size_g AS trans_g,
    sum_conv_g,
    current_avg_weight_g AS sum_average_g
FROM recursive_calc
ORDER BY rn;

关键说明 #

• 递归CTE会严格按时间顺序逐行处理，每一轮迭代都可以直接读取上一轮输出的总重量、平均体重等计算结果，完全满足你的需求
• 上述代码基于你的示例数据运行，最终sum_conv_g的输出结果与预期一致为5.6
• 如果后续要支持多渔场，只需要在ordered_transactions的ROW_NUMBER()窗口函数中增加PARTITION BY farm分区即可
• 所有计算完全在PostgreSQL内部完成，不需要引入外部代码处理。

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