[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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
