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
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/