On 24/07/11 03:58, alan wrote:
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
The above gives just the rolling sum, you need to divide by the number
of rows in the sum to get the average (I assume you want the arithmetic
mean, as the are many types of average!).
CREATE OR REPLACE
VIEW weekly_average
AS SELECT
*,
round((sum(value) OVER mywindow / LEAST(6, (row_number() OVER
mywindow))), 4) AS rolling_average
FROM daily_vals
WINDOW mywindow AS
(
PARTITION BY host
ORDER BY rid
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);
Cheers,
Gavin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
