From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Robert Klemme <shortcutter@googlemail.com>
Cc: alan <alan.miller3@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trigger or Function
Date: Mon, 01 Aug 2011 21:26:01 +1200
Message-ID: <4E367129.2050903@archidevsys.co.nz> (raw)
In-Reply-To: <CAM9pMnNb4m4edWXsy+vjNY1WvBjTwK7Z5bBETq2OQ+j+AUPUKQ@mail.gmail.com>
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>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, shortcutter@googlemail.com, alan.miller3@gmail.com
Subject: Re: Trigger or Function
In-Reply-To: <4E367129.2050903@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox