Re: order by and aggregate

dev(at)archonet(dot)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