Search Postgresql Archives

range intervals in window function frames

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux