PostgreSQL技术问题：行差值计算与多区间平均值差值求解

需求：计算最近5行（当前行至前4行）的平均值减去前6-10行的平均值的差值。

解决方案SQL #

SELECT 
    date_time,
    -- 最近5行（当前行 + 前4行）的平均值
    AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS recent_5_avg,
    -- 前6-10行（当前行往前数第5到第9行）的平均值
    AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 9 PRECEDING AND 5 PRECEDING) AS historical_5_avg,
    -- 最终差值
    AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
    - AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 9 PRECEDING AND 5 PRECEDING) AS avg_diff
FROM my_table;

关键说明 #

1. 窗口范围定义：
   • ROWS BETWEEN 4 PRECEDING AND CURRENT ROW：覆盖当前行及之前连续4行，正好对应「最近5行」的需求
   • ROWS BETWEEN 9 PRECEDING AND 5 PRECEDING：覆盖当前行往前数第5至第9行，对应「前6-10行」的区间
2. 仅保留差值列的简化写法：

SELECT date_time, avg_diff
FROM (
    SELECT 
        date_time,
        AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
        - AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 9 PRECEDING AND 5 PRECEDING) AS avg_diff
    FROM my_table
) AS x;

3. 空值处理：当表中数据不足10行时，历史区间的平均值会返回NULL，可通过COALESCE设置默认值：

SELECT 
    date_time,
    COALESCE(
        AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
        - AVG(price) OVER (ORDER BY date_time ROWS BETWEEN 9 PRECEDING AND 5 PRECEDING),
        0 -- 可根据需求替换为其他默认值
    ) AS avg_diff
FROM my_table;

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