> My first approach would be to remove WeekAvg and MonthAvg from the
> table and create a view which calculates appropriate values.
Thanks Robert, I had to upgrade to 9.0.4 to use the extended windowing
features.
Here is how I set it up. If anyone sees an issue, please let me know.
I'm new to postgres.
Basically, my "daily_vals" table contains HOST, DATE, & VALUE columns.
What I wanted was a way to automatically populate a 4th column
called "rolling_average", which would be the sum of <n> preceding
columns.
testdb=# select * from daily_vals;
rid | date | host | value
-----+------------+--------+-------------
1 | 2011-07-01 | hosta | 100.0000
2 | 2011-07-02 | hosta | 200.0000
3 | 2011-07-03 | hosta | 400.0000
4 | 2011-07-04 | hosta | 500.0000
5 | 2011-07-05 | hosta | 100.0000
6 | 2011-07-06 | hosta | 700.0000
7 | 2011-07-07 | hosta | 200.0000
8 | 2011-07-08 | hosta | 100.0000
9 | 2011-07-09 | hosta | 100.0000
10 | 2011-07-10 | hosta | 100.0000
11 | 2011-07-01 | hostb | 5.7143
12 | 2011-07-02 | hostb | 8.5714
13 | 2011-07-03 | hostb | 11.4286
14 | 2011-07-04 | hostb | 8.5714
15 | 2011-07-05 | hostb | 2.8571
16 | 2011-07-06 | hostb | 1.4286
17 | 2011-07-07 | hostb | 1.4286
I created a view called weekly_average using this VIEW statement.
CREATE OR REPLACE
VIEW weekly_average
AS SELECT *, sum(value) OVER (PARTITION BY host
ORDER BY rid
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as rolling_average FROM daily_vals;
The I query the view just like a regular table.
the rolling average is calulated from the previuous 6 rows (for each
host).
testdb=# select * from weekly_average;
rid | date | host | value | rolling_average
-----+------------+--------+----------+------------------
1 | 2011-07-01 | hosta | 100.0000 | 100.0000
2 | 2011-07-02 | hosta | 200.0000 | 300.0000
3 | 2011-07-03 | hosta | 400.0000 | 700.0000
4 | 2011-07-04 | hosta | 500.0000 | 1200.0000
5 | 2011-07-05 | hosta | 100.0000 | 1300.0000
6 | 2011-07-06 | hosta | 700.0000 | 2000.0000
7 | 2011-07-07 | hosta | 200.0000 | 1400.0000
8 | 2011-07-08 | hosta | 100.0000 | 1400.0000
9 | 2011-07-09 | hosta | 100.0000 | 1200.0000
10 | 2011-07-10 | hosta | 100.0000 | 600.0000
11 | 2011-07-01 | hostb | 5.7143 | 5.7143
12 | 2011-07-02 | hostb | 8.5714 | 14.2857
13 | 2011-07-03 | hostb | 11.4286 | 25.7143
14 | 2011-07-04 | hostb | 8.5714 | 34.2857
15 | 2011-07-05 | hostb | 2.8571 | 37.1428
16 | 2011-07-06 | hostb | 1.4286 | 38.5714
17 | 2011-07-07 | hostb | 1.4286 | 40.0000
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
