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