Re: Trigger or Function

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/