From: | Scara Maccai <m_lists(at)yahoo(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | complex custom aggregate function |
Date: | 2009-01-30 08:35:53 |
Message-ID: | 302763.7397.qm@web24603.mail.ird.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
quarter value AVGn
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2009-01-30 08:50:59 | Re: ssl to more than one server |
Previous Message | Dave Page | 2009-01-30 08:20:35 | Re: md5 doesn't work (Was Re: Pet Peeves?) |