Re: Trigger or Function

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!