From: dev@archonet.com
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: order by and aggregate
Date: Mon, 6 Jan 2003 15:59:51 -0000 (GMT)
Message-ID: <1086.192.168.1.16.1041868791.squirrel@mainbox.archonet.com> (raw)
In-Reply-To: <3E198E18.5090408@klaster.net>
References: <3E197A39.8070808@klaster.net>
<200301061341.32945.dev@archonet.com>
<3E198E18.5090408@klaster.net>
> 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.
Ah - so it's maximum of a running-total rather than a sum.
AFAIK you are out of luck with aggregate functions. The order data is
supplied to them is *not* defined - the "order by" operates just before
results are output. Not much you can do about this, the whole basis of
relational DBs are sets and sets don't have any idea of ordering.
However, there are three options:
You could define a cursor (or a table-function in 7.3) which would handle
the order-by and then calculate the running-total on the fly. You then
just need a standard max(running_total) call to pick out the value.
Actually, if you use the cursor you might need to implement the max() in
the application.
Alternatively, you could add a running_total column and use a trigger to
ensure the value is kept up to date.
Finally, you could do the work in the application.
Difficult to say which is the best for you. If you have 7.3, don't need
these figures often and do a lot of updates/inserts I'd recommend option
1. If you're using 7.2, don't do a lot of inserts and want the figures
frequently I'd choose option 2.
HTH
- Richard Huxton
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: dev@archonet.com, jasiek@klaster.net
Subject: Re: order by and aggregate
In-Reply-To: <1086.192.168.1.16.1041868791.squirrel@mainbox.archonet.com>
* 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