Message-ID: <3E198E18.5090408@klaster.net>
Date: Mon, 06 Jan 2003 15:09:28 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: order by and aggregate
References: <3E197A39.8070808@klaster.net>
	<200301061341.32945.dev@archonet.com>
In-Reply-To: <3E197A39.8070808@klaster.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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