PostgreSQL能否实现滚动平均？求助5天滚动填充值的实现方案

PostgreSQL完全可以实现你描述的滚动平均填充需求，而且递归CTE是可行的方案，你之前认为递归方法不可用大概率是写法存在问题。

核心逻辑实现 #

假设你的表结构包含日期字段（record_date）和实际数值字段（actual_value），前5天有实际值，后续日期的实际值为NULL需要用滚动平均填充。以下是具体SQL实现：

1. 创建示例表与测试数据

CREATE TABLE daily_data (
    record_date DATE PRIMARY KEY,
    actual_value NUMERIC
);

-- 插入前5天实际数据，后续日期留空
INSERT INTO daily_data (record_date, actual_value)
VALUES
('2024-01-01', 10),
('2024-01-02', 20),
('2024-01-03', 30),
('2024-01-04', 40),
('2024-01-05', 50),
('2024-01-06', NULL),
('2024-01-07', NULL),
('2024-01-08', NULL);

2. 递归CTE计算滚动平均

WITH RECURSIVE rolling_avg AS (
    -- 基础段：获取前5天的实际数据，初始化累计总和与计数
    SELECT
        record_date,
        actual_value AS calculated_value,
        actual_value AS running_sum,
        1 AS running_count
    FROM daily_data
    WHERE record_date <= (SELECT MIN(record_date) + INTERVAL '4 days' FROM daily_data)
    
    UNION ALL
    
    -- 递归段：从第6天开始，用前5天的平均值填充当日值，并更新累计总和
    SELECT
        d.record_date,
        ra.running_sum / 5 AS calculated_value,
        -- 新总和 = 前一天总和 - 5天前的计算值 + 当日新平均值
        ra.running_sum - (SELECT calculated_value FROM rolling_avg WHERE record_date = d.record_date - INTERVAL '4 days') + (ra.running_sum / 5),
        5 AS running_count
    FROM rolling_avg ra
    JOIN daily_data d ON d.record_date = ra.record_date + INTERVAL '1 day'
    WHERE d.actual_value IS NULL
)
SELECT record_date, calculated_value
FROM rolling_avg
ORDER BY record_date;

逻辑说明 #

• 基础段：先提取前5天的实际数据，同时维护running_sum（用于后续计算5天总和），到第5天时running_sum即为前5天实际值的总和（150）。
• 递归段：每次基于前一天的记录计算当日值（前5天总和除以5），同时更新running_sum——减去5天前的计算值，加上当日的新平均值，确保下一次递归时能获取最新的连续5天总和。

如果你的日期存在断档，可以先通过generate_series生成连续日期序列，再与业务表关联后执行上述逻辑。

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