Re: Trigger or Function - Mailing list pgsql-performance

> 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