Hi all,
I am trying to calculate an 8 hour moving average using the new
Window functions without success. Here is what I am trying to do :
-- create test table
CREATE TABLE temperatures
(
fulldate timestamp NOT NULL PRIMARY KEY,
value numeric
);
-- inserts
INSERT INTO temperatures
select '2009-07-01 00:00:00'::timestamp + interval '1 hour' * s.a as
fulldate,
round(cast(random() as numeric), 1) as value from
generate_series(0,23) as s(a)
;
-- selects
select * from temperatures order by fulldate;
-- window function
SELECT fulldate, value, avg(value) OVER () FROM temperatures ORDER BY
fulldate; SELECT fulldate, value, avg(value) OVER (ORDER BY fulldate) FROM
temperatures ORDER BY fulldate; SELECT fulldate, value, round(avg(value)
OVER (ORDER BY fulldate RANGE UNBOUNDED PRECEDING), 2) as value FROM
temperatures ORDER BY fulldate;
-- not supported
SELECT fulldate, value, round(avg(value) OVER (ORDER BY fulldate RANGE -8
PRECEDING), 2) as value FROM temperatures ORDER BY fulldate;
Is there any way to PARTITION on a subset of rows (in this case 8) ?
-- expected result
-- date time value moving-average
2009-07-01 00:00:00 0,3
2009-07-01 01:00:00 0,1
2009-07-01 02:00:00 0,5
2009-07-01 03:00:00 0,1
2009-07-01 04:00:00 0,2
2009-07-01 05:00:00 0,7
2009-07-01 06:00:00 0,9
2009-07-01 07:00:00 0,7 0,44
2009-07-01 08:00:00 0 0,4
2009-07-01 09:00:00 0,9 0,5
2009-07-01 10:00:00 0,8 0,54
2009-07-01 11:00:00 0,4 0,58
2009-07-01 12:00:00 0,6 0,63
2009-07-01 13:00:00 0,4 0,59
2009-07-01 14:00:00 0,7 0,56
2009-07-01 15:00:00 0,2 0,5
2009-07-01 16:00:00 0,2 0,53
2009-07-01 17:00:00 0,5 0,48
2009-07-01 18:00:00 0,7 0,46
2009-07-01 19:00:00 0 0,41
2009-07-01 20:00:00 0,4 0,39
2009-07-01 21:00:00 0,9 0,45
2009-07-01 22:00:00 0,4 0,41
2009-07-01 23:00:00 0,7 0,48
0,51
0,52
0,48
0,6
0,67
0,55
0,7
Thanks in advance
Paolo Saudin
