Re: order by and aggregate

Richard Huxton wrote:

> On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote:
>
> >Hi
> >I created my own aggregate function working as max(sum(value))
> >It adds positive and negative values and finds maximum of this sum.
> >To work properly this function needs data to be sorted.
>
>
> I'm not sure that an aggregate function should require data to be sorted
> before processing. Could you show details of your function - there may
> be a
> way to rewrite it to handle unsorted data.

Standard Postgresql aggregate functions don't need sorted data, but my
function needs. Look at the data:
<value> <sum>
3 3
-2 1
6 7 *** max_sum=7
-3 4
2 6

For example, if you inverse your data, you have:
<value> <sum>
2 2
-3 -1
6 5
-2 3
3 6 *** max_sum=6

As you see, data order is very important in this aggregate.

The function is very easy:
CREATE OR REPLACE FUNCTION maxsum_counter(_int4, int4) RETURNS _int4 AS '
DECLARE
old_val ALIAS for $1;
curr_val ALIAS for $2;
new_max int4;
new_sum int4;
BEGIN
new_sum=old_val[1]+curr_val;
if new_sum > old_val[1] then
new_max=new_sum;
else
new_max=old_val[2];
end if;
return ''{'' || new_sum || '','' || new_max || ''}'';
END;
' LANGUAGE 'plpgsql';

OR REPLACE FUNCTION extract_maxsum(_int4) RETURNS "int4" AS '
DECLARE
old_val ALIAS for $1;
BEGIN
return old_val[2];
END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE maxsum int4;
CREATE AGGREGATE maxsum(
BASETYPE = int4,
SFUNC = maxsum_counter,
STYPE = _int4,
FINALFUNC = extract_maxsum,
INITCOND = '{0,0}');

> >select
> > maxsum(value)
> >from some_table
> > order by some_field
> >
> >doesn't work:
> >ERROR: Attribute some_table.some_field must be GROUPed or used in an
> >aggregate function
>
>
> The "order by" isn't necessarily handled before calculating maxsum()
> anyway.

Nice point.
Anyway it doesn't matter, because it isn't handled at all.

Tomasz Myrta