Re: complex custom aggregate function

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(at)postgresql(dot)org on behalf of Scara Maccai
Sent: Fri 1/30/2009 1:45 PM
To: pgsql-general(at)postgresql(dot)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.