From: Tomasz Myrta <jasiek@klaster.net>
To: dev@archonet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: order by and aggregate
Date: Mon, 06 Jan 2003 17:35:54 +0100
Message-ID: <3E19B06A.1080002@klaster.net> (raw)
In-Reply-To: <3E197A39.8070808@klaster.net>
References: <3E197A39.8070808@klaster.net>
<200301061341.32945.dev@archonet.com>
<3E198E18.5090408@klaster.net>
<1086.192.168.1.16.1041868791.squirrel@mainbox.archonet.com>
dev@archonet.com wrote:
> Ah - so it's maximum of a running-total rather than a sum.
Sorry, my english still has a lot of black-holes :-(
> 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.
You are right, but if it is possible to use some tricks, why not to use
them?
> 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.
All of these options look a bit difficult.
Currently I use pl/pgsql function and query with sorted subselect:
select maxsum(X.val) from (select val from some_table order by key) X
It isn't a very big problem for me. I was just wondering if I can change
this pl/pgsql function in a view. Few weeks ago I asked on
pgsql-performance about views and subselects. The conclusion was that
postgresql planner doesn't work well when joining subselects - it wastes
time on querying all rows of subselect.
1. I think it could rather slow down than speed up my solution.
2. I can't store this value, because each time data range changes.
3. I want to do as much as possible inside postgres.
I think I will have to stay with pl/pgsql function and sorted subquery.
Thanks for your help,
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: <3E19B06A.1080002@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