Выберите данные для 15-минутных окон - PostgreSQL

Question

Хорошо, у меня есть такая таблица в PostgreSQL:

timestamp              duration

2013-04-03 15:44:58    4
2013-04-03 15:56:12    2
2013-04-03 16:13:17    9
2013-04-03 16:16:30    3
2013-04-03 16:29:52    1
2013-04-03 16:38:25    1
2013-04-03 16:41:37    9
2013-04-03 16:44:49    1
2013-04-03 17:01:07    9
2013-04-03 17:07:48    1
2013-04-03 17:11:00    2
2013-04-03 17:11:16    2
2013-04-03 17:15:17    1
2013-04-03 17:16:53    4
2013-04-03 17:20:37    9
2013-04-03 17:20:53    3
2013-04-03 17:25:48    3
2013-04-03 17:29:26    1
2013-04-03 17:32:38    9
2013-04-03 17:36:55    4

И я хотел бы получить следующий вывод:

timestampwindowstart = 2013-04-03 15:44:58

duration    count
1           0
2           1
3           0
4           1
9           0

timestampwindowstart = 2013-04-03 15:59:58

duration    count
1           0
2           0
3           0
4           0
9           1

timestampwindowstart = 2013-04-03 16:14:58

duration    count
1           1
2           0
3           1
4           0
9           0

timestampwindowstart = 2013-04-03 16:29:58

duration    count
1           2
2           0
3           0
4           0
9           1

так далее...

Таким образом, в основном он циклически просматривает временные метки в 15-минутных окнах и выводит различные значения длительности вместе с их частотой (счет). Значение timestampwindowstart является самой ранней отметкой времени для окна (т.е. timestampwindowfinish = timestampwindowstart + 15 минут)

Это так, я могу затем построить гистограммы 15-минутного интервала...

Я пытался читать, но мне немного сложно разобраться, и у меня не так много времени...

Спасибо за любую помощь!

Answer 1

Быстрый и грязный способ: http://sqlfiddle.com/ Я назвал свою колонку tstamp вместо вашего timestamp

with t as (
  select
    generate_series(mitstamp,matstamp,'15 minutes') as int,
    duration
  from
    (select min(tstamp) mitstamp, max(tstamp) as matstamp from tmp) a,
    (select duration from tmp group by duration) b
)

select
  int as timestampwindowstart,
  t.duration,
  count(tmp.duration)
from
   t
   left join tmp on 
         (tmp.tstamp >= t.int and 
          tmp.tstamp < (t.int + interval '15 minutes') and 
          t.duration = tmp.duration)
group by
  int,
  t.duration
order by
  int,
  t.duration

Краткое объяснение:

1. Расчет минимальной и максимальной отметки времени
2. Генерация 15 минутных интервалов между минимальным и максимальным
3. Результаты перекрестного объединения с уникальными значениями длительности
4. Исходные данные левого соединения (левое соединение важно, потому что это сохранит все возможные комбинации в выходных данных и будет null где длительность не существует для данного интервала.
5. Агрегированные данные. count(null)=0

Если у вас есть больше таблиц и алгоритм должен быть применен к их объединению. Предположим, у нас есть три таблицы tmp1, tmp2, tmp3 все с колоннами tstamp а также duration, Мы можем расширить предыдущее решение:

with 

tmpout as (
  select * from tmp1 union all
  select * from tmp2 union all
  select * from tmp3
)

,t as (
  select
    generate_series(mitstamp,matstamp,'15 minutes') as int,
    duration
  from
    (select min(tstamp) mitstamp, max(tstamp) as matstamp from tmpout) a,
    (select duration from tmpout group by duration) b
)

select
  int as timestampwindowstart,
  t.duration,
  count(tmp.duration)
from
   t
   left join tmpout on 
         (tmp.tstamp >= t.int and 
          tmp.tstamp < (t.int + interval '15 minutes') and 
          t.duration = tmp.duration)
group by
  int,
  t.duration
order by
  int,
  t.duration

Вы должны действительно знать with предложение в PostgreSQL. Это бесценная концепция для любого анализа данных в PostgreSQL.