On 01/08/11 19:18, Robert Klemme wrote:
On Sat, Jul 30, 2011 at 3:01 AM, Gavin Flower
<GavinFlower@xxxxxxxxxxxxxxxxx> 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!
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
