Make a plpgsql function which will iterate over the rows on which the
moving average is to be done (FOR row IN SELECT), of course use the
correct order, then use an array as a FIFO, add a row to the moving
average and push it, pop the old one and substract it.
Roundoff errors will bite your
I personally use 3 seperate triggers on most occasions, depending on how
different the action for each seperate action is, it's just easier for me and my
people to logically distinguish the functions that way, but the example in the
7.4 documentation for triggers is given using the form that i wrot
On Mon, 24 Jan 2005 08:32 pm, Alban Hertroys wrote:
> [EMAIL PROTECTED] wrote:
> > CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS '
> > DECLARE
> > bar_record RECORD;
> > x INTEGER;
> > y DOUBLE PRECISION := 0;
> > BEGIN
> > IF TG_OP = ''INSERT'' THEN
> > y := y + NEW.ba
[EMAIL PROTECTED] wrote:
CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS '
DECLARE
bar_record RECORD;
x INTEGER;
y DOUBLE PRECISION := 0;
BEGIN
IF TG_OP = ''INSERT'' THEN
y := y + NEW.bar;
...
RETURN NEW;
ELSIF TG_OP = ''DELETE'' THEN
x := 0;
...
Unless I'm grossly misunderstanding the problem i think that a trigger written
in PL/pgsql would work fine. Something like this:
CREATE TABLE foo (
foo_id SERIAL primary key,
foo TEXT);
CREATE TABLE bar (
foo_id INTEGER references foo,
bar_id SERIAL primary key,
bar DOUBLE PRECISION NOT NULL);
"Dann Corbit" <[EMAIL PROTECTED]> writes:
> If someone wanted to put arbitrary aggregates into PostgreSQL, I would
> suggest something akin to the "RED BRICK" API, or better yet, the ATLAS
> API:
I also found a good reference for the DB2's SQL2003 Standard OLAP functions:
http://publib.boulder.i
If someone wanted to put arbitrary aggregates into PostgreSQL, I would
suggest something akin to the "RED BRICK" API, or better yet, the ATLAS
API:
http://magna.cs.ucla.edu/atlas/
---(end of broadcast)---
TIP 8: explain analyze is your friend
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> If you're feeling adventurous, you might look at Oracle's documentation
> on their analytic functions and see if you can come up with something
> generic for PostgreSQL.
I think the hard part of doing even a simple implementation is precisely the
poin
On Fri, Jan 21, 2005 at 12:53:45AM -0500, Greg Stark wrote:
> "Vanole, Mike" <[EMAIL PROTECTED]> writes:
>
> > I need to calculate a moving average and I would like to do it with SQL,
> > or a Pg function built for this purpose. I'm on Pg 7.4. Is this possible
> > in Pg without a bunch of self joi
"Vanole, Mike" <[EMAIL PROTECTED]> writes:
> I need to calculate a moving average and I would like to do it with SQL,
> or a Pg function built for this purpose. I'm on Pg 7.4. Is this possible
> in Pg without a bunch of self joins, or is there a funtion available?
Unfortunately moving averages f
Also, if you don't need an exact moving average, you might consider a
weighted mean. Something like:
mean = mean * 0.9 + new_value * 0.1
Much easier to maintain than a moving average.
On Thu, Jan 20, 2005 at 08:40:24PM -0800, Dann Corbit wrote:
> Why not use a cursor?
>
>
>
> P.S.
>
> A mov
Title: Message
Why not use a cursor?
P.S.
A moving average will look much better if
you Hahn the tails.
To do a normal 7 point moving average, you take (x[i]+ x[i+1]+ x[i+2]+ x[i+3]+ x[i+4]+
x[i+5]+ x[i+6])/7 as point xprime[i] and (y[i]+ y[i+1]+ y[i+2]+ y[i+3]+ y[i+4]+
y[i+5]+ y[i+6
12 matches
Mail list logo
