PostgreSQL递归计算avg值并插入表的技术问题求助

当前查询的核心问题是：子查询(SELECT avg FROM average_prices ORDER BY datetime DESC LIMIT 1)在整个INSERT执行周期中只会计算一次，始终取初始的0.01作为基准值，导致所有新行的avg都基于该初始值计算，而非预期的前一期迭代结果，最终出现线性增长的错误输出。

要实现依赖前一期结果的迭代计算，需使用PostgreSQL的递归CTE（WITH RECURSIVE），它能按顺序逐行计算，每一步都用上一步的avg结果生成当前值。以下提供两种适配不同场景的实现方式：

方案1：基于时间顺序自动匹配下一个周期 #

此方案完全不依赖具体时间单位，仅通过datetime的大小关系找到下一个周期，适配任意时间间隔的场景：

WITH RECURSIVE calculated_avg AS (
    -- 锚点：获取average_prices中的初始记录
    SELECT datetime, avg
    FROM average_prices
    UNION ALL
    -- 递归迭代：依次获取下一个时间周期的pricing数据，计算当前avg
    SELECT 
        pd.datetime,
        ((9/(14 + 1.0)) * pd.price * ca.avg)::REAL AS avg
    FROM calculated_avg ca
    JOIN pricing_data pd 
        ON pd.datetime = (
            SELECT MIN(datetime) 
            FROM pricing_data 
            WHERE datetime > ca.datetime
        )
    WHERE pd.datetime IS NOT NULL -- 直到没有更多待处理的pricing数据
)
-- 将新计算的结果插入average_prices（排除已存在的初始记录）
INSERT INTO average_prices (datetime, avg)
SELECT datetime, avg
FROM calculated_avg
WHERE datetime NOT IN (SELECT datetime FROM average_prices);

方案2：基于行号顺序迭代 #

如果pricing_data的行顺序与时间周期严格对应，可通过行号关联迭代，性能更优：

WITH RECURSIVE calculated_avg AS (
    -- 锚点：获取初始记录
    SELECT datetime, avg, 0 AS step
    FROM average_prices
    UNION ALL
    -- 递归迭代：按行号依次取pricing数据计算
    SELECT 
        pd.datetime,
        ((9/(14 + 1.0)) * pd.price * ca.avg)::REAL AS avg,
        ca.step + 1 AS step
    FROM calculated_avg ca
    JOIN (
        -- 给pricing_data按时间顺序编号
        SELECT 
            datetime, 
            price,
            ROW_NUMBER() OVER (ORDER BY datetime) AS rn
        FROM pricing_data
    ) pd ON pd.rn = ca.step + 1
)
-- 插入新计算结果
INSERT INTO average_prices (datetime, avg)
SELECT datetime, avg
FROM calculated_avg
WHERE step > 0;

执行上述任一方案后，average_prices将生成符合预期的结果：

datetime       | avg
---------------------+-------
 2025-04-30 00:00:00 |  0.01
 2025-05-01 00:00:00 |  0.03
 2025-05-02 00:00:00 |  0.18
 2025-05-03 00:00:00 |  1.62
 2025-05-04 00:00:00 | 19.44
 2025-05-05 00:00:00 | 291.6

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