Re: [GENERAL] complex custom aggregate function
I think I've found a solution myself to the moving average problem, so I'm
posting it here in case it works for some others (and so that everybody can
check that I'm doing it right and in the best way...)
Basically I'm
1) saving all the couples (timestamp, double) of an aggregation into an array
using array_accum
2) unnesting the same array to get a table of (timestamp, double) tuples
3) calculating the moving average using the self join trick (found in
Transact SQL) of the table got from 2)
4) getting the max out of the average values.
Before the code, some questions:
1) I tried doing it using python, but the array was passed as a string (I
didn't find it in the docs though...)
2) I didn't understand why the array_enum function is present only for int
arrays in the contrib intagg module; couldn't it be a function with
anyarray as argument? Such as:
CREATE OR REPLACE FUNCTION array_enum(anyarray)
RETURNS setof anyelement
AS 'array_unnest'
LANGUAGE INTERNAL IMMUTABLE STRICT;
3) Am I doing something wrong in the code below? Could I do it better?
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
CREATE TYPE timemax_t AS (
t timestamp,
v double precision
);
CREATE OR REPLACE FUNCTION timemax_array_enum(timemax_t[])
RETURNS setof timemax_t
AS 'array_unnest'
LANGUAGE INTERNAL IMMUTABLE STRICT;
CREATE or replace FUNCTION movingavgmax(a timemax_t[], nquarters int4)
RETURNS double precision
AS $$
DECLARE
timemax_t_row timemax_t;
retval double precision;
BEGIN
select max(mx) into retval from
(
select x..t, AVG(y.v) as mx
from timemax_array_enum(a) as x, timemax_array_enum(a) as y
where
x.t between y.t and y.t+((nquarters-1)*15||' minutes')::interval
group by x.t
having count(y.v)=nquarters
) as subs;
return retval;
END;
$$ LANGUAGE plpgsql;
-- example usage:
select movingavgmax(array_accum((quarter,value)::timemax_t), 3) from test where
id = 10 AND quarter between '2008-12-01 00:00:00' and '2008-12-01 10:00:00' ;
- Messaggio originale -
Da: Scara Maccai m_li...@yahoo.it
A: pgsql-general@postgresql.org
Inviato: Venerdì 30 gennaio 2009, 9:35:53
Oggetto: complex custom aggregate function
Hi all,
I have a table like:
value int,
quarter timestamp
I need an aggregate function that gives back the maximum value using
this algorithm:
AVG of the first hour (first 4 quarters) (AVG0)
same as above, but 1 quarter later (AVG1)
...
same as above, but n quarters later (AVGn)
result: the quarter where AVGn was MAX.
Example:
quartervalueAVGn
2008-01-01 00:00 10
2008-01-01 00:15 15
2008-01-01 00:30 5
2008-01-01 00:45 20- 12.5 ((10+15+5+20)/4)
2008-01-01 01:15 2- 21((15+5+20+2)/4)
2008-01-01 01:30 30- 14.25 ((5+20+2+30)/4))
the result should be ('2008-01-01 00:15', 21)
It would be very easy if the input to the custom aggregate function was
ordered (because I would keep 4 internal counters), but I guess there's
no way of forcing the ordering of the input to the function, right?
So I have to cache all the (quarter,value) couples and give back a
result at the end, right?
Passa a Yahoo! Mail.
La webmail che ti offre GRATIS spazio illimitato,
antispam e messenger integrato.
http://it.mail.yahoo.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complex custom aggregate function
Scara Maccai m_li...@yahoo.it writes: It would be very easy if the input to the custom aggregate function was ordered (because I would keep 4 internal counters), but I guess there's no way of forcing the ordering of the input to the function, right? You can with a subquery. Something like SELECT agg(foo) from (SELECT foo ORDER BY bar) However that will produce one record per grouping. From what I read of your description you want to produce one record per input record. There isn't any efficient way to do that in current Postgres releases -- you would have to have a subquery which executed for every record and retrieved the set of data to aggregate. 8.4 Will have OLAP Window functions which can implement things like moving averages. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complex custom aggregate function
Gregory Stark wrote: From what I read of your description you want to produce one record per input record. Exactly. 8.4 Will have OLAP Window functions which can implement things like moving averages. Using 8.3: could I do it caching all the values somewhere in a custom aggregation function to sort them before giving back the result? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complex custom aggregate function
Hello, not very smart, but something like following should do the job: h_mv 1.00x 1.25x 1.50x 1.75x 2.00x 2.25x 2.50x 2.75x 3.00x 3.25x 3.50x 3.75x 4.00x 4.25x 4.50x 4.75x ... select H,A FROM ( select min(h_q) as H, avg(x) as A group by h_q/1 union all select min(h_q), avg(x) group by (h_q-0.25)/1 union all select min(h_q), avg(x) group by (h_q-0.50)/1 union all select min(h_q), avg(x) group by (h_q-0.75)/1 )foo where A= select max(A) from (foo..) or use ORDER BY A desc LIMIT 1 if a single result is sufficient... HTH, Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org on behalf of Scara Maccai Sent: Fri 1/30/2009 1:45 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] complex custom aggregate function Gregory Stark wrote: From what I read of your description you want to produce one record per input record. Exactly. 8.4 Will have OLAP Window functions which can implement things like moving averages. Using 8.3: could I do it caching all the values somewhere in a custom aggregation function to sort them before giving back the result? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complex custom aggregate function
Oops, I meant group by floor(h_q-x) instead of group by (h_q-x)/1 Marc Mamin select H,A FROM ( select min(h_q) as H, avg(x) as A group by h_q/1 union all select min(h_q), avg(x) group by (h_q-0.25)/1 union all select min(h_q), avg(x) group by (h_q-0.50)/1 union all select min(h_q), avg(x) group by (h_q-0.75)/1 )foo where A= select max(A) from (foo..)
