Hello, all!
I first posted this to pgsql-general, but didn't get a definitive
answer to my question concerning if a window function feature is
scheduled or being worked on for 9.x.
----------
I need to do moving averages over time series data and was hoping
window functions could solve the problem for me, but it doesn't look
like 8.4 or even 9.0 implementations are quite there, yet.
Currently, if I have this table:
create table sample ( ts timestamp, value integer ); create index sample_ts on sample (ts);
and say I want a moving average of value over a fixed interval of five
minutes (note that this could mean varying numbers of records in each
"frame"), then I can do this:
select *, avg_over_interval(ts, interval '5 min') from sample order by ts;
Where avg_over_interval() is defined like this:
create or replace function avg_over_interval(timestamp, interval) returns numeric as $$ select avg(value)
fromsample where (($1-$2) <= ts) and (ts <= $1); $$ language sql;
What I would LIKE to do is this:
select *, avg(ts) over(order by ts range (interval '5 min') preceding) from sample order by ts;
Which is way cleaner and, I assume, more efficient.
Questions:
1) Is there active work on window functions with frames over interval ranges?
2) If not, how can I help with that?
3) Until the functionality is in 9.x, can I make what I'm doing more efficient? Is there a better way to do this
withoutwindow functions? (I tried an inline subquery instead of the function call, but it was twice as slow as
thefunction.)
Thanks all for you help.
Dan