On 01/08/11 19:18, Robert Klemme wrote:
> 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
>
<Chuckle> Your fix is much more elegant and efficient, though both
approaches work!