Re: range intervals in window function frames

[sent you a personal email by mistake - slightly corrected query posted
to the list]

Le dimanche 12 décembre 2010 à 10:55 -0500, Daniel Popowich a écrit :
> 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);

> 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;
>

This?

select t1.ts, t1.value, (select avg(t2.value) from (select value from
sample where (t1.ts-ts)::INTERVAL <= interval '5 minutes' and ts<t1.ts)
as t2) from sample t1;

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique