В ожидании 9.0 - дополнение к фреймам window функций

Перевод Waiting for 9.0 – extended frames for window functions с select * from depesz;

(В январе 2010 г. команда разработчиков решила, что следующая версия PostgreSQL будет нумероваться 9.0, а не 8.5)

12 февраля Tom Lane принял патч Hitoshi Harada:

Расширение набора опций фреймов поддерживаемых window функциями.

Патч позволяет фреймам начинаться с текушей строки (CURRENT ROW) (в режиме либо RANGE либо ROW), и также добавляет поддержку ROWS n PRECEDING и ROWS n FOLLOWING для начальной и конечной точек. (PRECEDING/FOLLOWING для RANGE ещё не готово - граматика работает, но это всё что пока есть)

Hitoshi Harada, проверено Pavel Stehule

Описание вполне понятное, но давайте посмотрим что же это есть в точности.

Во первых, создадим простую тестовую таблицу:

create table test (

id serial primary key,

some_grouping int4,

some_value int4

);

Теперь добавим случайных данных:

insert into test (some_grouping, some_value)

select i, random() * 100 + 50 as j

from generate_series(1,2) i, generate_series(1,10) k;

Данные не впечатляют, но достаточны для требуемого результата:

select * from test order by id asc;

id | some_grouping | some_value

----+---------------+------------

1 | 1 | 94

2 | 1 | 86

3 | 1 | 77

4 | 1 | 86

5 | 1 | 146

6 | 1 | 145

7 | 1 | 68

8 | 1 | 71

9 | 1 | 84

10 | 1 | 105

11 | 2 | 126

12 | 2 | 88

13 | 2 | 90

14 | 2 | 50

15 | 2 | 104

16 | 2 | 68

17 | 2 | 61

18 | 2 | 107

19 | 2 | 127

20 | 2 | 112

(20 rows)

Если вы не знакомы с window функциями и особенно с фреймами, давайте рассмотрим простой пример:

SELECT

id,

some_grouping,

some_value,

sum(some_value) OVER (

PARTITION BY some_grouping

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as sum_per_group,

sum(some_value) OVER (

PARTITION BY some_grouping

ORDER BY id

RANGE UNBOUNDED PRECEDING

) as cumulative_sum_per_group

FROM

test

ORDER BY some_grouping, id;

id | some_grouping | some_value | sum_per_group | cumulative_sum_per_group

----+---------------+------------+---------------+--------------------------

1 | 1 | 94 | 962 | 94

2 | 1 | 86 | 962 | 180

3 | 1 | 77 | 962 | 257

4 | 1 | 86 | 962 | 343

5 | 1 | 146 | 962 | 489

6 | 1 | 145 | 962 | 634

7 | 1 | 68 | 962 | 702

8 | 1 | 71 | 962 | 773

9 | 1 | 84 | 962 | 857

10 | 1 | 105 | 962 | 962

11 | 2 | 126 | 933 | 126

12 | 2 | 88 | 933 | 214

13 | 2 | 90 | 933 | 304

14 | 2 | 50 | 933 | 354

15 | 2 | 104 | 933 | 458

16 | 2 | 68 | 933 | 526

17 | 2 | 61 | 933 | 587

18 | 2 | 107 | 933 | 694

19 | 2 | 127 | 933 | 821

20 | 2 | 112 | 933 | 933

(20 rows)

Ранее было сложно вычислять скользящее среднее, приходилось, например, делать так:

SELECT

id,

some_grouping,

some_value,

(

lag(some_value, 1) over (grouping)

+

some_value

+

lead(some_value, 1) over (grouping)

) / 3 as rolling_average

FROM

test

WINDOW grouping AS (

PARTITION BY some_grouping

ORDER BY id

)

ORDER BY some_grouping, id;

Получаем более-менее то что надо:

id | some_grouping | some_value | rolling_average

----+---------------+------------+-----------------

1 | 1 | 94 | [null]

2 | 1 | 86 | 85

3 | 1 | 77 | 83

4 | 1 | 86 | 103

5 | 1 | 146 | 125

6 | 1 | 145 | 119

7 | 1 | 68 | 94

8 | 1 | 71 | 74

9 | 1 | 84 | 86

10 | 1 | 105 | [null]

11 | 2 | 126 | [null]

12 | 2 | 88 | 101

13 | 2 | 90 | 76

14 | 2 | 50 | 81

15 | 2 | 104 | 74

16 | 2 | 68 | 77

17 | 2 | 61 | 78

18 | 2 | 107 | 98

19 | 2 | 127 | 115

20 | 2 | 112 | [null]

(20 rows)

Как можно заметить, первая и последняя строки каждого окна не имеют скользящего среднего, т.е. оно равно null. Это потому что для первых строк lag(some_value) равен null, а для последних lead(some_value) равен null.

А теперь посмотрим как всё будет с новым патчем Hitoshi Harada:

SELECT

id,

some_grouping,

some_value,

cast(

avg(some_value) over (

grouping

rows between 1 preceding and 1 following

)

as int4

) as rolling_average

FROM

test

WINDOW grouping AS (

PARTITION BY some_grouping

ORDER BY id

)

ORDER BY some_grouping, id;

И результат:

id | some_grouping | some_value | rolling_average

----+---------------+------------+-----------------

1 | 1 | 94 | 90

2 | 1 | 86 | 86

3 | 1 | 77 | 83

4 | 1 | 86 | 103

5 | 1 | 146 | 126

6 | 1 | 145 | 120

7 | 1 | 68 | 95

8 | 1 | 71 | 74

9 | 1 | 84 | 87

10 | 1 | 105 | 95

11 | 2 | 126 | 107

12 | 2 | 88 | 101

13 | 2 | 90 | 76

14 | 2 | 50 | 81

15 | 2 | 104 | 74

16 | 2 | 68 | 78

17 | 2 | 61 | 79

18 | 2 | 107 | 98

19 | 2 | 127 | 115

20 | 2 | 112 | 120

(20 rows)

Это очень клёво.

Я конечно мог бы написать запрос без использования патча, который бы корректно считал значения для первой и последней строки:

SELECT

id,

some_grouping,

some_value,

(

coalesce( lag(some_value, 1) over (grouping), 0 )

+

some_value

+

coalesce( lead(some_value, 1) over (grouping), 0 )

) / (

1

+

case when lag(some_value, 1) over (grouping) is null then 0 else 1 end

+

case when lead(some_value, 1) over (grouping) is null then 0 else 1 end

) as rolling_average

FROM

test

WINDOW grouping AS (

PARTITION BY some_grouping

ORDER BY id

)

ORDER BY some_grouping, id;

Это определённо не здорово и станет кошмаром очень скоро, когда диапазон станет больше 3 строк.

Другие опции, которые добавляет патч кроме BETWEEN n PRECEDING AND n FOLLOWING, можно найти в dev doc.