Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 4F3ABB5DBC1
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 29 Jul 2011 22:01:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 39635-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 30 Jul 2011 01:01:37 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mbx.knossos.net.nz (mbx.knossos.net.nz [202.160.48.10])
	by mail.postgresql.org (Postfix) with ESMTP id B5417B5DBBE
	for <pgsql-performance@postgresql.org>;
	Fri, 29 Jul 2011 22:01:36 -0300 (ADT)
Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz
	[60.234.150.59]) (authenticated bits=0)
	by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id p6U11Qji013098
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT);
	Sat, 30 Jul 2011 13:01:29 +1200
Message-ID: <4E3357E5.9080307@archidevsys.co.nz>
Date: Sat, 30 Jul 2011 13:01:25 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110621 Fedora/3.1.11-1.fc14
	Lightning/1.0b3pre Thunderbird/3.1.11
MIME-Version: 1.0
To: alan <alan.miller3@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Trigger or Function
References: 
 <cf38a540-20ef-45d7-b593-2a6ec3337a95@j15g2000yqf.googlegroups.com>
	<CAM9pMnP5aCEcjNPOGOJKuuDhL9OO9ZKojQmeWv4n3xm7LQ1Ltw@mail.gmail.com>
	<6e65efcf-2a22-4995-8f27-2c1b8ff2a54a@cq10g2000vbb.googlegroups.com>
In-Reply-To: 
 <6e65efcf-2a22-4995-8f27-2c1b8ff2a54a@cq10g2000vbb.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201107/232
X-Sequence-Number: 44444

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