Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 1758BB5DC01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 06:26:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 82918-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 09:26:10 +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 40B5BB5DC00
	for <pgsql-performance@postgresql.org>;
	Mon,  1 Aug 2011 06:26:09 -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 p719Q1FW030271
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT);
	Mon, 1 Aug 2011 21:26:02 +1200
Message-ID: <4E367129.2050903@archidevsys.co.nz>
Date: Mon, 01 Aug 2011 21:26:01 +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: Robert Klemme <shortcutter@googlemail.com>
CC: 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>
	<4E3357E5.9080307@archidevsys.co.nz>
	<CAM9pMnNb4m4edWXsy+vjNY1WvBjTwK7Z5bBETq2OQ+j+AUPUKQ@mail.gmail.com>
In-Reply-To: 
 <CAM9pMnNb4m4edWXsy+vjNY1WvBjTwK7Z5bBETq2OQ+j+AUPUKQ@mail.gmail.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: 201108/24
X-Sequence-Number: 44471

On 01/08/11 19:18, Robert Klemme wrote:
> On Sat, Jul 30, 2011 at 3:01 AM, Gavin Flower
> <GavinFlower@archidevsys.co.nz>  wrote:
>> 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.
> There seems to be contradiction in the naming here.  Did you mean "avg
> of<n>   preceding columns."?
>
>>> 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 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
>>     );
> Why not
>
> CREATE OR REPLACE
>    VIEW weekly_average
>      AS SELECT *, avg(value) OVER (PARTITION BY host
>              ORDER BY rid
>              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
>            ) as rolling_average FROM daily_vals;
>
> What did I miss?
>
> Kind regards
>
> robert
>
<Chuckle> Your fix is much more elegant and efficient, though both 
approaches work!