Access a window's frame_end row from a window function
От | Clodoaldo Neto |
---|---|
Тема | Access a window's frame_end row from a window function |
Дата | |
Msg-id | CA+Z73LGq=TK_7w5m8nz=+FxSTuxb=wrWUkL9jgtX9_GngCO9rQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Access a window's frame_end row from a window function
Re: Access a window's frame_end row from a window function |
Список | pgsql-general |
Suppose there is the need to get the average of a value v over a 6 hours time window starting 7 hours before the current row's time. create table t (ts timestamp, v integer); insert into t (ts, v) values ('2013-01-01 00:46', 2), ('2013-01-01 03:54', 4), ('2013-01-01 06:28', 4), ('2013-01-01 11:19', 2), ('2013-01-01 14:44', 1), ('2013-01-01 15:56', 5), ('2013-01-01 18:01', 4), ('2013-01-01 19:40', 0), ('2013-01-01 20:38', 5), ('2013-01-01 21:22', 0); I can do it with a correlated subquery: select ts, v, ( select avg(v) from t s where ts between t.ts - interval '7 hours' and t.ts - interval '1 hour' ) average from t order by ts ; ts | v | average ---------------------+---+-------------------- 2013-01-01 00:46:00 | 2 | 2013-01-01 03:54:00 | 4 | 2.0000000000000000 2013-01-01 06:28:00 | 4 | 3.0000000000000000 2013-01-01 11:19:00 | 2 | 4.0000000000000000 2013-01-01 14:44:00 | 1 | 2.0000000000000000 2013-01-01 15:56:00 | 5 | 1.5000000000000000 2013-01-01 18:01:00 | 4 | 2.6666666666666667 2013-01-01 19:40:00 | 0 | 3.3333333333333333 2013-01-01 20:38:00 | 5 | 3.3333333333333333 2013-01-01 21:22:00 | 0 | 2.5000000000000000 But if I could access a window's frame_end row as a record from a window function: select ts, avg(case when ts between frame_end.ts - interval '7 hours' and frame_end.ts - interval '1 hour' then v else null end ) over(order by ts) from t order by ts I'm naively posting this as I have no idea how complex would it be to add this feature. Would it perform better than the correlated subquery? Regards, Clodoaldo
В списке pgsql-general по дате отправления: