Средний 10-минутный интервал агрегации темпоральной базы данных в PostgreSQL

У меня есть данные временного ряда, где я хочу выполнить агрегацию по средней функции. Агрегацию следует выполнять с 10-минутными интервалами: (ЧЧ-1):50, ЧЧ:00, ЧЧ:10, ЧЧ:20, ЧЧ:30, ЧЧ:40 или альтернативно с 10-минутными интервалами: (ЧЧ-1). :51, ЧЧ:01, ЧЧ:11, ЧЧ:21, ЧЧ:31, ЧЧ:41. Примечание: среднечасовое значение 5 часов является средним значением (4):51, 5:01,5:11, 5:21, 5:31, 5:41 значений.

Проблема в том, что набор данных включает переменные с нечетными и четными частотами во времени. Таким образом, я не могу назначить для него агрегацию, просто применяя нечетное или четное время мгновенно. Я хотел знать, как я могу лечить эту проблему.

Код, который я использую:

select dt, average_temp from (  select  date_trunc('hour', dt + interval '10 minutes') dt, avg(ambtemp) over(  partition by date_trunc('hour', dt + interval '10 minutes')  ) average_temp  from n25_30 where extract(minute from dt) in (51, 01, 11, 21, 31, 41) and dt::timestamptz > '2007-09-29 23:59:00' AND dt < '2007-09-30 22:45:00') s  group by 1, 2 order by dt

select dt, average_temp from (  select  date_trunc('hour', dt + interval '10 minutes') dt, avg(ambtemp) over(  partition by date_trunc('hour', dt + interval '10 minutes')  ) average_temp  from n28_30 where extract(minute from dt) in (50, 00, 10, 20, 30, 40) and dt::timestamptz > '2007-09-29 23:59:00' AND dt < '2007-09-30 22:45:00') s  group by 1, 2 order by dt

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

330    1.78 2007-09-30 10:39:52
331    2.06 2007-09-30 10:41:52
332    1.90 2007-09-30 10:43:52
333    2.28 2007-09-30 10:45:52
334    1.89 2007-09-30 10:47:52
335    2.04 2007-09-30 10:49:52
336    2.31 2007-09-30 10:51:52
337    2.50 2007-09-30 10:53:52
338    2.29 2007-09-30 10:55:52
339    2.47 2007-09-30 10:57:52
340    2.48 2007-09-30 10:59:52
341    1.74 2007-09-30 11:01:52
342    1.70 2007-09-30 11:03:52
343    2.30 2007-09-30 11:05:52
344    3.01 2007-09-30 11:07:52
345    2.78 2007-09-30 11:09:52
346    2.90 2007-09-30 11:11:52
347    2.50 2007-09-30 11:13:52
348    2.58 2007-09-30 11:15:52
349    3.72 2007-09-30 11:17:52
350    3.29 2007-09-30 11:19:52
351    2.32 2007-09-30 11:21:52
352    2.86 2007-09-30 11:23:52
353    3.11 2007-09-30 11:25:52
354    3.04 2007-09-30 11:27:51
355    2.39 2007-09-30 11:29:51
356    2.68 2007-09-30 11:31:51
357    2.32 2007-09-30 11:33:52
358    2.33 2007-09-30 11:35:52
359    2.50 2007-09-30 11:37:51
360    2.55 2007-09-30 11:39:51
361    3.16 2007-09-30 11:41:51
362    3.24 2007-09-30 11:43:51
363    3.90 2007-09-30 11:45:51
364    5.13 2007-09-30 11:47:51
365    3.94 2007-09-30 11:49:51
366    3.18 2007-09-30 11:51:51
367    4.54 2007-09-30 11:53:51
368    5.78 2007-09-30 11:55:51
369    4.72 2007-09-30 11:57:51
370    3.66 2007-09-30 11:59:51
371    3.22 2007-09-30 12:01:51
372    3.38 2007-09-30 12:03:51
373    3.96 2007-09-30 12:05:51
374    4.82 2007-09-30 12:07:51
375    4.09 2007-09-30 12:09:51
376    5.56 2007-09-30 12:11:51
377    6.60 2007-09-30 12:13:52
378    5.36 2007-09-30 12:15:52
379    6.04 2007-09-30 12:17:51
380    5.56 2007-09-30 12:19:51
381    4.60 2007-09-30 12:21:52
382    4.08 2007-09-30 12:23:51
383    4.44 2007-09-30 12:25:51
384    5.23 2007-09-30 12:27:52
385    3.16 2007-09-30 12:29:52
386    3.10 2007-09-30 12:31:52
387    3.50 2007-09-30 12:33:52
388    3.38 2007-09-30 12:35:52
389    3.65 2007-09-30 12:37:52
390    3.68 2007-09-30 12:39:52
391    4.40 2007-09-30 12:41:52
392    4.80 2007-09-30 12:43:52
393    5.44 2007-09-30 12:45:52
394    4.52 2007-09-30 12:47:52
395    3.64 2007-09-30 12:49:52
396    3.42 2007-09-30 12:51:52
397    3.74 2007-09-30 12:53:52
398    4.78 2007-09-30 12:55:52
399    4.03 2007-09-30 12:57:52
400    3.78 2007-09-30 12:59:52
401    4.52 2007-09-30 13:01:52
402    4.02 2007-09-30 13:03:52
403    4.07 2007-09-30 13:05:52
404    3.78 2007-09-30 13:07:52
405    4.04 2007-09-30 13:09:52
406    4.00 2007-09-30 13:11:52
407    4.29 2007-09-30 13:13:52
408    4.57 2007-09-30 13:15:52
409    4.83 2007-09-30 13:17:52
410    5.24 2007-09-30 13:19:52
411    6.96 2007-09-30 13:21:52
412    6.83 2007-09-30 13:23:52
413    8.58 2007-09-30 13:26:09
414    6.34 2007-09-30 13:28:09
415    8.34 2007-09-30 13:30:09
416    7.14 2007-09-30 13:32:09
417    5.26 2007-09-30 13:34:09
418    5.04 2007-09-30 13:36:09
419    5.96 2007-09-30 13:38:09
420    5.71 2007-09-30 13:40:09
421    7.16 2007-09-30 13:42:09
422    6.50 2007-09-30 13:44:09
423    5.54 2007-09-30 13:46:09
424    4.76 2007-09-30 13:48:09
425    4.87 2007-09-30 13:50:09
426    4.73 2007-09-30 13:52:09
427    4.74 2007-09-30 13:54:09
428    4.14 2007-09-30 13:56:09
429    4.74 2007-09-30 13:58:09
430    5.52 2007-09-30 14:00:09
431    4.86 2007-09-30 14:02:09
432    5.20 2007-09-30 14:04:09
433    6.98 2007-09-30 14:06:09
434    5.43 2007-09-30 14:08:09
435    4.90 2007-09-30 14:10:09
436    5.06 2007-09-30 14:12:09
437    6.74 2007-09-30 14:14:09
438    5.56 2007-09-30 14:16:09
439    5.22 2007-09-30 14:18:09
440    5.44 2007-09-30 14:20:09
441    4.84 2007-09-30 14:22:09
442    4.84 2007-09-30 14:24:09
443    4.50 2007-09-30 14:26:09
444    5.98 2007-09-30 14:28:09
445    4.90 2007-09-30 14:30:09
446    5.22 2007-09-30 14:32:09
447    6.41 2007-09-30 14:34:09
448    5.20 2007-09-30 14:36:09
449    4.74 2007-09-30 14:38:09
450    5.54 2007-09-30 14:40:09
451    5.04 2007-09-30 14:42:09
452    4.78 2007-09-30 14:44:09
453    4.87 2007-09-30 14:46:09
454    5.95 2007-09-30 14:48:09
455    5.10 2007-09-30 14:50:09
456    5.18 2007-09-30 14:52:09
457    4.94 2007-09-30 14:54:09
458    4.94 2007-09-30 14:56:09
459    5.01 2007-09-30 14:58:09
460    5.12 2007-09-30 15:00:09
461    4.92 2007-09-30 15:02:09
462    5.34 2007-09-30 15:04:09
463    5.14 2007-09-30 15:06:09
464    5.07 2007-09-30 15:08:09
465    5.35 2007-09-30 15:10:09
466    5.24 2007-09-30 15:12:09
467    5.50 2007-09-30 15:14:09
468    6.51 2007-09-30 15:16:09
469    7.31 2007-09-30 15:18:09
470    6.99 2007-09-30 15:20:09
471    6.91 2007-09-30 15:22:09
472    5.69 2007-09-30 15:24:09
473    5.75 2007-09-30 15:26:09
474    6.08 2007-09-30 15:28:09
475    6.94 2007-09-30 15:30:09
476    5.60 2007-09-30 15:32:09
477    5.08 2007-09-30 15:34:09
478    4.66 2007-09-30 15:36:09
479    5.52 2007-09-30 15:38:09
480    4.48 2007-09-30 15:40:09

postgresql average truncate
person A.Amidi    schedule 15.01.2013    source источник
comment
Почему вы хотите выбрать одно точное время из каждых 10 минут?   -  person Ihor Romanchenko    schedule 15.01.2013
comment
Какой будет правильный вывод для ваших выборочных данных?   -  person Quassnoi    schedule 15.01.2013
comment
@Quassnoi, результат должен быть среднечасовым, начиная с 28.09.2007, 23:49:59 и заканчивая 29.09.2007, 23:40. Среднее значение следует рассчитывать по шести значениям температуры с 10-минутными интервалами: (ЧЧ-1):50, ЧЧ:00, ЧЧ:10, ЧЧ:20, ЧЧ:30, ЧЧ:40. Проблема в том, что данные не дискретизируются на одних и тех же частотах. Другими словами, временные интервалы не равны точно двум минутам. Итак, в таблице у меня есть как нечетное, так и четное время мгновенно.   -  person A.Amidi    schedule 15.01.2013
comment
@Amidi: Я имел в виду, не могли бы вы посчитать пару записей вручную, чтобы нам было с чем сравнивать?   -  person Quassnoi    schedule 15.01.2013
comment
@Quassnoi, да: на 11 часов должно быть 2,518333333 12 часов = 4,01 13 часов = 5,491666667   -  person A.Amidi    schedule 16.01.2013
comment
Ваше требование к усреднению выглядит не очень четко определенным. Вы согласились бы просто взять среднее значение всех измерений в течение данного часа?   -  person mvp    schedule 16.01.2013
comment
@Amidi Ваши примерные значения для (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40?   -  person Ihor Romanchenko    schedule 16.01.2013
comment
@IgorRomanchenko, да и нет. Когда когда-либо существуют одни и те же минуты, функция должна использовать их, в противном случае используйте минуту позже.   -  person A.Amidi    schedule 16.01.2013

Ответы (1)


arrow_upward
1
arrow_downward

Задачу можно упростить до:

1) Через каждые 10 минут выбирайте первое (самое раннее) значение, но большее, чем '1 minute' (чтобы исключить такие значения, как 13:30:09 ).

2) В наборе из предыдущего шага получите среднее значение за каждый час со смещением на '10 minutes'.

Этот запрос:

SELECT ambtemp,
       dt,
       row_number() OVER (PARTITION BY date_trunc('hour',dt), 
                                       date_part('minute',dt)::int / 10
                          ORDER BY dt) as rank
FROM your_table
WHERE date_part('minute',dt)::int % 10 > 0

даст вам самое раннее значение, но больше, чем '1 minute' каждые 10 минут (hh:m1 или hh:m2 каждые 10 минут).

Затем, используя первый запрос в качестве подзапроса:

SELECT date_trunc('hour',dt + interval '10 minutes') as hour,
       avg(ambtemp)
FROM (first query here) sub
WHERE rank = 1
GROUP BY 1;

ОБНОВЛЕНИЕ: Вот полный запрос:

SELECT date_trunc('hour',dt + interval '10 minutes') as hour,
       avg(ambtemp)
FROM (SELECT ambtemp,
             dt,
             row_number() OVER (PARTITION BY date_trunc('hour',dt), 
                                             date_part('minute',dt)::int / 10
                                ORDER BY dt) as rank
      FROM table1

      -- delete this WHERE to get (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40
      -- with this WHERE it gets  (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41
      WHERE date_part('minute',dt)::int % 10 > 0          
     ) sub
WHERE rank = 1
GROUP BY 1
ORDER BY 1;

А вот SQLFiddle с данными вашего примера.

Приведенный выше запрос относится к (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41. Если вам нужен запрос для (HH-1):50, HH:00, HH:10, HH:20, HH:30, HH:40, просто удалите отмеченную строку.

Я проверил запрос, и он возвращает правильные результаты (и для варианта запроса (HH-1):50, HH:00, ... они равны результатам вашего примера).

person Ihor Romanchenko    schedule 15.01.2013
comment
@Amidi Смотрите мое обновление к ответу. Я протестировал запрос, и он возвращает ожидаемые результаты. - person Ihor Romanchenko; 16.01.2013
comment
Это не работает для 13 часов, где в 1-часовом интервале существуют как нечетные, так и четные мгновенные значения времени. Код должен понимать, что если четное число не может быть идентифицировано, добавьте 1 минуту, а затем продолжайте с 10-минутными интервалами. - person A.Amidi; 16.01.2013
comment
@Amidi Амиди, вы пробовали оба варианта запроса (с WHERE caluse и без него)? Вариант без внутреннего предложения WHERE дает 5.491666666667 для 13 часов (результат, который вы хотите) - person Ihor Romanchenko; 16.01.2013
comment
@Amidi Скажи мне, что ты не понимаешь в запросе. Я не могу объяснить вам все о языке SQL. - person Ihor Romanchenko; 16.01.2013
comment
Спасибо за ваше внимание. Я не могу понять, как он выбирает 13:21 как нечетное время, а затем переходит к 13:30, которое является четным. - person A.Amidi; 16.01.2013
comment
давайте продолжим обсуждение в чате - person Ihor Romanchenko; 16.01.2013