pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by and aggregate
9+ messages / 4 participants
[nested] [flat]
* order by and aggregate
@ 2003-01-06 12:44 Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-01-06 12:44 UTC (permalink / raw)
To: pgsql-sql
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.
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
I found I can obey this using subselect:
select
maxsum(X.value)
from
(select value
from some_table
order by some_field) X
I can't create subselect, because I want to change this query into a
view. In my case postgresql doesn't use indexes on subselect inside a view.
What should I do?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 13:41 Richard Huxton <dev@archonet.com>
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2003-01-06 13:41 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
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.
> 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.
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 14:09 Tomasz Myrta <jasiek@klaster.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-01-06 14:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 15:59 dev@archonet.com
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 1 reply; 9+ messages in thread
From: dev@archonet.com @ 2003-01-06 15:59 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 16:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-01-06 16:34 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> I found I can obey this using subselect:
> select
> maxsum(X.value)
> from
> (select value
> from some_table
> order by some_field) X
> I can't create subselect, because I want to change this query into a
> view. In my case postgresql doesn't use indexes on subselect inside a view.
I don't understand what you think is wrong with this solution. It works
fine for me:
regression=# create view vv as
regression-# select max(unique1) from (select unique1 from tenk1
regression(# order by unique2) x;
CREATE VIEW
regression=# explain select * from vv;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Subquery Scan vv (cost=523.00..523.00 rows=1 width=8)
-> Aggregate (cost=523.00..523.00 rows=1 width=8)
-> Subquery Scan x (cost=0.00..498.00 rows=10000 width=8)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..498.00 rows=10000 width=8)
(4 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 16:35 Tomasz Myrta <jasiek@klaster.net>
parent: dev@archonet.com
0 siblings, 0 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-01-06 16:35 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 16:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-01-06 16:36 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> 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
But if the input data is sorted into increasing order, then the largest
running sum value is always at the end. Therefore max(sum()) is
equivalent to sum(); therefore you do not need the special aggregate,
nor the ordering.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 16:59 Tomasz Myrta <jasiek@klaster.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-01-06 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
Tom Lane wrote:
> Tomasz Myrta writes:
>
> >Standard Postgresql aggregate functions don't need sorted data, but my
> >function needs. Look at the data:
> >
> >3 3
> >-2 1
> >6 7 *** max_sum=7
> >-3 4
> >2 6
>
>
> But if the input data is sorted into increasing order, then the largest
> running sum value is always at the end. Therefore max(sum()) is
> equivalent to sum(); therefore you do not need the special aggregate,
> nor the ordering.
>
> regards, tom lane
Sorting data by "value" was only an example,
In my case data is sorted by another key and has mixed positive and
negative values.
Tomasz Myrta
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: order by and aggregate
@ 2003-01-06 17:07 Tomasz Myrta <jasiek@klaster.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tomasz Myrta @ 2003-01-06 17:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> I don't understand what you think is wrong with this solution. It works
> fine for me:
>
> regression=# create view vv as
> regression-# select max(unique1) from (select unique1 from tenk1
> regression(# order by unique2) x;
> CREATE VIEW
> regression=# explain select * from vv;
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------
> Subquery Scan vv (cost=523.00..523.00 rows=1 width=8)
> -> Aggregate (cost=523.00..523.00 rows=1 width=8)
> -> Subquery Scan x (cost=0.00..498.00 rows=10000 width=8)
> -> Index Scan using tenk1_unique2 on tenk1
> (cost=0.00..498.00 rows=10000 width=8)
> (4 rows)
>
OK, your view is ok.
I think, you don't remember my questions about "joining views" and
"sub-select with aggregate" dated on 2002-10-23, so I qoute your answer
on that question (joining views):
>I think this is the same issue that Stephan identified in his response
>to your other posting ("sub-select with aggregate"). When you write
> FROM x join y using (col) WHERE x.col = const
>the WHERE-restriction is only applied to x. I'm afraid you'll need
>to write
> FROM x join y using (col) WHERE x.col = const AND y.col = const
>Ideally you should be able to write just
> FROM x join y using (col) WHERE col = const
>but I think that will be taken the same as "x.col = const" :-(
I have the same problem here, query is a bit complicated and has
some_table join (subselect) X using (some_field) which doesn't work.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-01-06 17:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-06 12:44 order by and aggregate Tomasz Myrta <jasiek@klaster.net>
2003-01-06 13:41 ` Richard Huxton <dev@archonet.com>
2003-01-06 14:09 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-06 15:59 ` dev@archonet.com
2003-01-06 16:35 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-06 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-06 16:59 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-06 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-06 17:07 ` Tomasz Myrta <jasiek@klaster.net>
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