Hello all!
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) from sample 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 without window
functions? (I tried to come up with a subquery in my
select-list, but couldn't come up with legal syntax to reference
the static value of ts for the current row in the subselect.)
Thanks all for you help.
Dan
