Thread: Moving avg using SQL

How can I do a moving avg by only using SQL?

In response to ????????? :
> How can I do a moving avg by only using SQL?

Which version do you have? Since 8.4 we have CTE aka windowing
functions, a simple axample:

test=*#  select n, last_value(n) over mywin , avg(n) over mywin
 from generate_series(1,20) n
 window mywin as (partition by (n-1)/4 rows between unbounded preceding
and unbounded following);

 n  | last_value |         avg
----+------------+---------------------
  1 |          4 |  2.5000000000000000
  2 |          4 |  2.5000000000000000
  3 |          4 |  2.5000000000000000
  4 |          4 |  2.5000000000000000
  5 |          8 |  6.5000000000000000
  6 |          8 |  6.5000000000000000
  7 |          8 |  6.5000000000000000
  8 |          8 |  6.5000000000000000
  9 |         12 | 10.5000000000000000
 10 |         12 | 10.5000000000000000
 11 |         12 | 10.5000000000000000
 12 |         12 | 10.5000000000000000
 13 |         16 | 14.5000000000000000
 14 |         16 | 14.5000000000000000
 15 |         16 | 14.5000000000000000
 16 |         16 | 14.5000000000000000
 17 |         20 | 18.5000000000000000
 18 |         20 | 18.5000000000000000
 19 |         20 | 18.5000000000000000
 20 |         20 | 18.5000000000000000
(20 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

On Fri, 2009-09-04 at 08:03 +0200, A. Kretschmer wrote:
> Which version do you have? Since 8.4 we have CTE aka windowing
> functions, a simple axample:

Minor terminology correction:

CTE stands for Common Table Expression, i.e. WITH [RECURSIVE].

Regards,
    Jeff Davis