每小时汇总几个小时范围内的值 [英] Aggregate values over a range of hours, every hour

问题描述

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

I have a PostgreSQL 9.1 database with a table containing a timestamp and a measuring value

'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等。

I need to average the value over a range of 8 hours, every hour. In other words, I need the average of 1h-8h, 2h-9h, 3h-10h etc.

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

I have no idea how to proceed for such a query. I have looked everywhere but have also no clue what functionalities to look for.

我发现的收盘价是每小时/每日平均或平均水平（例如1h-8h，9h -16h等）。但是在这些情况下，只需使用 date_trunc（）函数转换时间戳（如下例所示），对我来说就没有用了。

The closes I find are hourly/daily averages or block-averages (e.g. 1h-8h, 9h-16h etc.). But in these cases, the timestamp is simply converted using the date_trunc() function (as in the example below), which is not of use to me.

我想寻找的功能与此类似

What I think I am looking for is a function similar to this

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

但是，在group-by子句中，每个小时使用某种8小时范围。

But then using some kind of 8-hour range for EVERY hour in the group-by clause. Is that even possible?

推荐答案

A 带有自定义框架的窗口功能 使此非常简单：

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

在sqlfiddle上进行实时演示。

每个平均值的框架是当前行加上下面的7。假定您有每小时一排。您的样本数据似乎暗示了这一点，但您未指定。

The frame for each average is the current row plus the following 7. This assumes you have exactly one row for every hour. Your sample data seems to imply that, but you did not specify.

其方式为 avg_8h final（根据 ts ）用较少的行来计算集合的7行，直到最后一行的值等于其平均值为止。您没有指定如何处理特殊情况。

The way it is, avg_8h for the final (according to ts) 7 rows of the set is computed with fewer rows, until the value of the last row equals its own average. You did not specify how to deal with the special case.

这篇关于每小时汇总几个小时范围内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！