From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: alan <alan.miller3@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Trigger or Function
Date: Sat, 30 Jul 2011 13:01:25 +1200
Message-ID: <4E3357E5.9080307@archidevsys.co.nz> (raw)
In-Reply-To: <6e65efcf-2a22-4995-8f27-2c1b8ff2a54a@cq10g2000vbb.googlegroups.com>
References: <cf38a540-20ef-45d7-b593-2a6ec3337a95@j15g2000yqf.googlegroups.com>
<CAM9pMnP5aCEcjNPOGOJKuuDhL9OO9ZKojQmeWv4n3xm7LQ1Ltw@mail.gmail.com>
<6e65efcf-2a22-4995-8f27-2c1b8ff2a54a@cq10g2000vbb.googlegroups.com>
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.
>
> testdb=# select * from daily_vals;
> rid | date | host | value
> -----+------------+--------+-------------
> 1 | 2011-07-01 | hosta | 100.0000
> 2 | 2011-07-02 | hosta | 200.0000
> 3 | 2011-07-03 | hosta | 400.0000
> 4 | 2011-07-04 | hosta | 500.0000
> 5 | 2011-07-05 | hosta | 100.0000
> 6 | 2011-07-06 | hosta | 700.0000
> 7 | 2011-07-07 | hosta | 200.0000
> 8 | 2011-07-08 | hosta | 100.0000
> 9 | 2011-07-09 | hosta | 100.0000
> 10 | 2011-07-10 | hosta | 100.0000
> 11 | 2011-07-01 | hostb | 5.7143
> 12 | 2011-07-02 | hostb | 8.5714
> 13 | 2011-07-03 | hostb | 11.4286
> 14 | 2011-07-04 | hostb | 8.5714
> 15 | 2011-07-05 | hostb | 2.8571
> 16 | 2011-07-06 | hostb | 1.4286
> 17 | 2011-07-07 | hostb | 1.4286
>
>
> 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 I query the view just like a regular table.
> the rolling average is calulated from the previuous 6 rows (for each
> host).
>
> testdb=# select * from weekly_average;
> rid | date | host | value | rolling_average
> -----+------------+--------+----------+------------------
> 1 | 2011-07-01 | hosta | 100.0000 | 100.0000
> 2 | 2011-07-02 | hosta | 200.0000 | 300.0000
> 3 | 2011-07-03 | hosta | 400.0000 | 700.0000
> 4 | 2011-07-04 | hosta | 500.0000 | 1200.0000
> 5 | 2011-07-05 | hosta | 100.0000 | 1300.0000
> 6 | 2011-07-06 | hosta | 700.0000 | 2000.0000
> 7 | 2011-07-07 | hosta | 200.0000 | 1400.0000
> 8 | 2011-07-08 | hosta | 100.0000 | 1400.0000
> 9 | 2011-07-09 | hosta | 100.0000 | 1200.0000
> 10 | 2011-07-10 | hosta | 100.0000 | 600.0000
> 11 | 2011-07-01 | hostb | 5.7143 | 5.7143
> 12 | 2011-07-02 | hostb | 8.5714 | 14.2857
> 13 | 2011-07-03 | hostb | 11.4286 | 25.7143
> 14 | 2011-07-04 | hostb | 8.5714 | 34.2857
> 15 | 2011-07-05 | hostb | 2.8571 | 37.1428
> 16 | 2011-07-06 | hostb | 1.4286 | 38.5714
> 17 | 2011-07-07 | hostb | 1.4286 | 40.0000
>
> Alan
>
>
>
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
);
Cheers,
Gavin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, alan.miller3@gmail.com
Subject: Re: Trigger or Function
In-Reply-To: <4E3357E5.9080307@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox