Совокупные значения в течение нескольких часов, каждый час

У меня есть база данных 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 и т.д.

Я не знаю, как продолжить такой запрос. Я повсюду смотрел, но также не знаю, какие функции нужно искать.

Закрытие, которое я нахожу, это среднечасовые/суточные средние или средние значения (например, 1h-8h, 9h-16h и т.д.). Но в этих случаях временная метка просто преобразуется с помощью функции date_trunc() (как в примере ниже), которая мне не подходит.

То, что я думаю, что я ищу, является функцией, подобной этой

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

Но затем используя какой-то 8-часовой диапазон для КАЖДОГО часа в предложении group-by. Возможно ли это?

Ответ 1

A функция окна с пользовательским фреймом делает это удивительно простым:

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

Живая демонстрация на sqlfiddle.

Кадр для каждого среднего - это текущая строка плюс следующее 7. Это предполагает, что у вас есть ровно одна строка за каждый час. Кажется, что ваши данные образца подразумевают это, но вы не указали.

Как это сделать, avg_8h для окончательного (в соответствии с ts) 7 строк набора вычисляется с меньшим количеством строк, пока значение последней строки не будет равно его собственному среднему значению. Вы не указали, как обращаться со специальным случаем.

Ответ 2

Ключом является создание виртуальной таблицы, с которой можно присоединиться к вашим наборам результатов. Функция generate_series может помочь сделать это следующим образом:

SELECT
    start
    , start + interval '8 hours' as end
FROM (
    SELECT generate_series(
        date'2012-01-01'
        , date'2012-02-02'
        , '1 hour'
    ) AS start
) x;

Это выводит что-то вроде этого:

         start          |          end           
------------------------+------------------------
 2012-01-01 00:00:00+00 | 2012-01-01 08:00:00+00
 2012-01-01 01:00:00+00 | 2012-01-01 09:00:00+00
 2012-01-01 02:00:00+00 | 2012-01-01 10:00:00+00
 2012-01-01 03:00:00+00 | 2012-01-01 11:00:00+00

Это дает вам возможность присоединиться к вашим данным. Таким образом, следующий запрос:

SELECT
    y.start
    , round(avg(ts_val.v))
FROM
    ts_val,
    (
        SELECT
            start
            , start + interval '8 hours' as end
        FROM (
            SELECT generate_series(
                date'2012-01-01'
                , date'2012-02-02'
                , '1 hour'
            ) AS start
        ) x
    ) y
WHERE
    ts BETWEEN y.start AND y.end
GROUP BY
    y.start
ORDER BY
    y.start
;

Для следующих данных

         ts          | v 
---------------------+---
 2012-01-01 01:00:00 | 2
 2012-01-01 09:00:00 | 2
 2012-01-01 10:00:00 | 5
(3 rows)

Выведет следующие результаты:

         start          | round 
------------------------+-------
 2012-01-01 00:00:00+00 |   2.0
 2012-01-01 01:00:00+00 |   2.0
 2012-01-01 02:00:00+00 |   3.5
 2012-01-01 03:00:00+00 |   3.5
 2012-01-01 04:00:00+00 |   3.5
 2012-01-01 05:00:00+00 |   3.5
 2012-01-01 06:00:00+00 |   3.5
 2012-01-01 07:00:00+00 |   3.5
 2012-01-01 08:00:00+00 |   3.5
 2012-01-01 09:00:00+00 |   3.5
 2012-01-01 10:00:00+00 |   5.0
(11 rows)