Hello,
not very smart, but something like following should do the job:
h_m v
1.00 x
1.25 x
1.50 x
1.75 x
2.00 x
2.25 x
2.50 x
2.75 x
3.00 x
3.25 x
3.50 x
3.75 x
4.00 x
4.25 x
4.50 x
4.75 x
...
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-owner@xxxxxxxxxxxxxx on behalf of Scara Maccai
Sent: Fri 1/30/2009 1:45 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
