Создание среднего значения за час на основе 2 минут до и после мгновенного времени в PostgreSQL

У меня есть временная база данных с частотой дискретизации 2 минуты, и я хочу извлечь мгновенные почасовые значения как 00:00, 01:00, 02, ... 23 для каждого дня.
Итак, я хотел бы получить среднее значение от среднего значения:

ЧЧ-1:58, ЧЧ:00 и ЧЧ:02 = среднее число ЧЧ часов

OR

ЧЧ-1:59, ЧЧ:01 и ЧЧ:03 = среднее значение ЧЧ часов

Пример данных1:

9/28/2007 23:51 -1.68
9/28/2007 23:53 -1.76
9/28/2007 23:55 -1.96
9/28/2007 23:57 -2.02
9/28/2007 23:59 -1.92
9/29/2007 0:01  -1.64
9/29/2007 0:03  -1.76
9/29/2007 0:05  -1.83
9/29/2007 0:07  -1.86
9/29/2007 0:09  -1.94

Ожидаемый результат:

В 00:00:

(-1.92+-1.64+-1.76)/3

Пример данных2:

9/28/2007 23:54 -1.44
9/28/2007 23:56 -1.58
9/28/2007 23:58 -2.01
9/29/2007 0:00  -1.52
9/29/2007 0:02  -1.48
9/29/2007 0:04  -1.46

Ожидаемые результаты:

(-2.01+-1.52+-1.48)/3


postgresql average temporal-database
person Hamed Footohi    schedule 17.01.2013    source источник

Ответы (1)


arrow_upward
1
arrow_downward

оконные функции в PostgreSQL значительно упрощают работу с соседними строками. раньше был. Не пробовал, но должен быть примерно прав:

select
  date_trunc('hour', newest_time) as average_time,
  (oldest_temp + middle_temp + newest_temp) / 3 as average_temp
from (
  select
    date_trunc('hour', sample_time) as average_time,
    lag(sample_time, 2) over w as oldest_time,
    lag(sample_time, 1) over w as middle_time,
    sample_time as newest_time,
    lag(sample_temp, 2) over w as oldest_temp,
    lag(sample_temp, 1) over w as middle_temp,
    sample_temp as newest_temp
  from
    samples
  window
    w as (order by sample_time)
) as s
where
  oldest_time = newest_time - '4 minutes'::interval and
  middle_time = newest_time - '2 minutes'::interval and
  extract(minute from newest_time) in (2, 3);

Я ограничил это в предложении where именно тем сценарием, который вы описали - последнее значение в: 02 или: 03, предыдущие 2 значения за 2 и 4 минуты до этого. На тот случай, если у вас есть недостающие данные, которые в противном случае дали бы странные результаты, такие как усреднение по гораздо более длинному интервалу.

person Scott Lamb    schedule 17.01.2013
comment
Я придумал эту ошибку. ОШИБКА: синтаксическая ошибка в состоянии SQL или рядом с ним: 42601 Символ: 110 - person Hamed Footohi; 17.01.2013
comment
Попробуйте эту версию, которая работает у меня (хотя я не ввел никаких данных, чтобы попробовать). Кажется, я немного неправильно понял over; он применяет область действия к одному предложению select, а не к каждой оконной функции в запросе. Я не уверен, что это можно применить к where, поэтому вместо этого я использовал подзапрос. - person Scott Lamb; 17.01.2013
comment
Ах, невозможно использовать оконные функции из WHERE, потому что фильтрация WHERE происходит первой: оконная функция рассматривает строки виртуальной таблицы, созданной предложением FROM запроса, отфильтрованным его предложениями WHERE, GROUP BY и HAVING. если есть. Так что подзапрос нужен. - person Scott Lamb; 17.01.2013