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