Thread: PostgreSQL 8.4 Window functions

PostgreSQL 8.4 Window functions

From
"Paolo Saudin"
Date:
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