Moving avg 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)
