Как применить агрегатную функцию только к смежным строкам?

На PostgreSQL 9.4 я пытаюсь добиться того, что я буду называть "агрегатной функцией" для некоторых смежных строк. Пример:

Входные данные: 

recipe  prod1   prod2   timestamp
0       5       4       2015-07-02 08:10:34.357
0       2       7       2015-07-02 08:13:45.352
0       7       0       2015-07-02 08:16:22.098
1       3       2       2015-07-02 08:22:14.678
1       9       4       2015-07-02 08:22:56.123
2       2       6       2015-07-02 08:26:37.564
2       1       7       2015-07-02 08:27:33.109
2       0       8       2015-07-02 08:31:11.687
0       3       5       2015-07-02 08:40:01.345
1       4       2       2015-07-02 08:42:23.210

Желаемый результат: 

recipe  prod1_sum   prod2_avg   timestamp_first             timestamp_last
0       14          3.6666      2015-07-02 08:10:34.357     2015-07-02 08:16:22.098
1       12          3           2015-07-02 08:22:14.678     2015-07-02 08:22:56.123
2       3           7           2015-07-02 08:26:37.564     2015-07-02 08:31:11.687
0       3           5           2015-07-02 08:40:01.345     2015-07-02 08:40:01.345
1       4           2           2015-07-02 08:42:23.210     2015-07-02 08:42:23.210

В основном, одна выходная строка для каждой "группы" смежных строк (при сортировке таблицы по столбцу timestamp) с одним и тем же значением "рецепта". В выходных данных prod1_sum-сумма prod1 в "группе", prod2_avg-среднее значение prod2 в той же" группе", и 2 последних столбца соответственно являются первыми и последние метки времени в группе. Очевидно, что существует несколько различных групп с одним и тем же значением "рецепта", и я действительно хочу получить выходную строку для каждой из них.

На данный момент у меня есть уродливый способ получения этого, основанный на нескольких запросах и большой обработке данных за пределами БД, чего я действительно хочу избежать, и который не стоит показывать.

Моя проблема на самом деле заключается в "группировке" строк. Я знаю, как создать агрегатную функцию, которая делала бы то, что я хочу, если бы я мог применить это к каждой группе индивидуально. Я заглянул в функции windows, но, похоже, это сгруппирует все значения по рецепту, не согласующемуся с принципом "непрерывных строк", который я должен уважать.

1 2
sql postgresql aggregate-functions window-functions

1 ответ:

Вы можете использовать следующий запрос: 

SELECT recipe, SUM(prod1) AS prod1_sum,
       AVG(prod2) AS prod2_avg, 
       MIN(timestamp) AS timestamp_first, MAX(timestamp) AS timestamp_last
FROM (       
   SELECT recipe, prod1, prod2, timestamp,
          ROW_NUMBER() OVER (ORDER BY timestamp) 
          - 
          ROW_NUMBER() OVER (PARTITION BY recipe 
                             ORDER BY timestamp) AS grp
   FROM mytable ) t
GROUP BY recipe, grp
ORDER BY timestamp_first

Хитрость здесь заключается в использовании функции окна ROW_NUMBER для идентификации островов непрерывных recipe значений: grp вычисляемое поле делает именно это.

Демо здесь

3