Skip site navigation (1) Skip section navigation (2)

complex custom aggregate function

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: (view raw, whole thread or download thread mbox)
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.


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?




pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2009-01-30 08:50:59
Subject: Re: ssl to more than one server
Previous:From: Dave PageDate: 2009-01-30 08:20:35
Subject: Re: md5 doesn't work (Was Re: Pet Peeves?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group