From: Tomasz Myrta <jasiek@klaster.net>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: order by and aggregate
Date: Mon, 06 Jan 2003 15:09:28 +0100
Message-ID: <3E198E18.5090408@klaster.net> (raw)
In-Reply-To: <3E197A39.8070808@klaster.net>
References: <3E197A39.8070808@klaster.net>
<200301061341.32945.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, dev@archonet.com
Subject: Re: order by and aggregate
In-Reply-To: <3E198E18.5090408@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox