sql - 每小时汇总一段时间内的值

Question

我有一个 PostgreSQL 9.1 数据库，其中的表包含一个时间戳 和一个测量值

'2012-10-25 01:00'   2
'2012-10-25 02:00'   5
'2012-10-25 03:00'   12
'2012-10-25 04:00'   7
'2012-10-25 05:00'   1
...                  ...

我需要每小时计算 8 小时范围内的值的平均值。换句话说，我需要 1h-8h、2h-9h、3h-10h 等的平均值。

我不知道如何进行这样的查询。我到处都看过，但也不知道要寻找什么功能。

我找到的收盘价是每小时/每天的平均值或 block 平均值(例如 1h-8h、9h-16h 等)。但在这些情况下，时间戳只是使用 date_trunc() 函数进行转换(如下例所示)，这对我来说没有用。

我想我要找的是类似这个的功能

SELECT    date_trunc('day', timestamp), max(value) 
FROM      table_name
GROUP BY  date_trunc('day', timestamp);

但随后在 group-by 子句中对每个小时使用某种 8 小时范围。这可能吗？

Answer 1

A window function with a custom frame使这非常简单:

SELECT ts
      ,avg(val) OVER (ORDER BY ts
                      ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING) AS avg_8h
FROM tbl;

Live demo on sqlfiddle.

每个平均值的帧是当前行加上后面的 7 行。这假设您每小时只有一行。您的示例数据似乎暗示了这一点，但您没有具体说明。

是这样的，avg_8h对于最后(根据ts)集合的7行用更少的行计算，直到最后一行的值等于自己的平均值。您没有指定如何处理特殊情况。