Re: Trigger or Function

From: Robert Klemme <shortcutter(at)googlemail(dot)com>
To: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
Cc: alan <alan(dot)miller3(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Trigger or Function
Date: 2011-08-01 07:18:49
Message-ID: CAM9pMnNb4m4edWXsy+vjNY1WvBjTwK7Z5bBETq2OQ+j+AUPUKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Sat, Jul 30, 2011 at 3:01 AM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)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

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Adarsh Sharma 2011-08-01 07:31:30 How to Speed up Insert from Multiple Connections
Previous Message Robert Ayrapetyan 2011-07-31 13:51:47 Performance die when COPYing to table with bigint PK