如何计算PostgreSQL时间序列数据与7天前同时段的百分比变化？

API 探路者

2026-9-29

你原有SQL存在两处语法/逻辑错误：

WHERE子句必须放在GROUP BY之前，你写的顺序不符合PostgreSQL语法要求
FROM后填写的database应该替换为你实际存储数据的表名

你需要的最近1小时和7天前同时段均值对比可以通过CTE分别计算两个时段的聚合结果再关联实现，参考SQL如下：

WITH current_hour_data AS (
    -- 计算最近1个整小时的各ID指标均值
    SELECT
        id,
        AVG(value) AS current_avg
    FROM 你的实际表名
    WHERE datetime >= DATE_TRUNC('hour', now())
      AND datetime < DATE_TRUNC('hour', now()) + INTERVAL '1 hour'
    GROUP BY id
),
seven_days_ago_data AS (
    -- 计算7天前同一整小时的各ID指标均值
    SELECT
        id,
        AVG(value) AS seven_days_ago_avg
    FROM 你的实际表名
    WHERE datetime >= DATE_TRUNC('hour', now()) - INTERVAL '7 days'
      AND datetime < DATE_TRUNC('hour', now()) - INTERVAL '7 days' + INTERVAL '1 hour'
    GROUP BY id
)
-- 关联两个时段的结果输出对比
SELECT
    COALESCE(c.id, s.id) AS id,
    c.current_avg,
    s.seven_days_ago_avg,
    -- 可选计算差值与变化率
    c.current_avg - s.seven_days_ago_avg AS value_diff,
    CASE WHEN s.seven_days_ago_avg <> 0
         THEN ROUND((c.current_avg - s.seven_days_ago_avg) / s.seven_days_ago_avg * 100, 2)
         ELSE NULL END AS change_rate_percent
FROM current_hour_data c
FULL OUTER JOIN seven_days_ago_data s ON c.id = s.id;

如果你的业务需要统计的是当前时间往前推1小时的滑动窗口而非整时钟小时，只需要修改两个CTE的WHERE时间条件即可：

最近1小时条件改为：WHERE datetime >= now() - INTERVAL '1 hour'
7天前同时段条件改为：WHERE datetime >= now() - INTERVAL '7 days 1 hour' AND datetime < now() - INTERVAL '7 days'

针对大数据量场景的优化建议：