Aggregate ORDER BY patch
in [PgSql]
|
From: Tom Lane on 13 Nov 2009 12:09 Andrew Gierth <andrew(a)tao11.riddles.org.uk> writes: > "Peter" == Peter Eisentraut <peter_e(a)gmx.net> writes: > Peter> This is exactly the syntax that is in the spec AFAICT. > Right. The spec defines this syntax for array_agg and xmlagg (only). Cool, I had forgotten that they added that in the latest revisions. I withdraw the complaint that this patch goes too far beyond the spec. > But it would be entirely unreasonable, the way postgres works, to > implement ORDER BY for only specific aggregates. Quite. This is another instance of the thing I complained of before, that the SQL committee likes to define the behavior of specific aggregates instead of inducing a generic aggregate-behavior definition. So we're on our own to extract one, and this proposal seems pretty reasonable to me: it's useful and it's consistent with the query-level behavior of DISTINCT and ORDER BY. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andres Freund on 13 Nov 2009 12:23 On Friday 13 November 2009 16:35:08 Tom Lane wrote: > Greg Stark <gsstark(a)mit.edu> writes: > > On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas > > > > <heikki.linnakangas(a)enterprisedb.com> wrote: > >> Andrew Gierth wrote: > >>> Herewith a patch to implement agg(foo ORDER BY bar) with or without > >>> DISTINCT, etc. > >> > >> What does that mean? Aggregate functions are supposed to be commutative, > >> right? > > > > We certainly have non-commutative agggregates currently, notably > > array_agg() > > Right. The fact that none of the standard aggregates are > order-sensitive doesn't mean that it's not useful to have user-defined > ones that are. Currently we suggest fetching from an ordered sub-select > if you want to use an aggregate that is input order sensitive. This > patch just provides an alternative (and equally nonstandard) notation > for that. > > I'm not entirely convinced that adding ORDER BY here is a good idea, > partly because it goes so far beyond the spec and partly because it's > not going to be easily optimizable. But I can see that there is a > use-case. The spec supports the ORDER BY syntax for the xmlagg aggregate... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Gierth on 13 Nov 2009 13:02 >>>>> "Heikki" == Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: >> No artificial restrictions are imposed on what syntactical >> combinations are allowed. However, ORDER BY is not allowed with >> aggregates used as window functions (as per the existing >> restriction on DISTINCT). Heikki> How is this different from window functions? Window functions return a row for each row of input, aggregates don't. The reason I didn't tackle the case of aggregate functions used as window functions is that the spec allows constructs like this: array_agg(a order by b) over (order by c) which can't be represented using the aggregate-as-window-function mechanism as it currently stands, since you'd have to re-sort the window each time. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 13 Nov 2009 13:26 2009/11/14 Andrew Gierth <andrew(a)tao11.riddles.org.uk>: >>>>>> "Heikki" == Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: > > >> No artificial restrictions are imposed on what syntactical > >> combinations are allowed. However, ORDER BY is not allowed with > >> aggregates used as window functions (as per the existing > >> restriction on DISTINCT). > > Heikki> How is this different from window functions? > > Window functions return a row for each row of input, aggregates don't. > > The reason I didn't tackle the case of aggregate functions used as > window functions is that the spec allows constructs like this: > > array_agg(a order by b) over (order by c) > > which can't be represented using the aggregate-as-window-function > mechanism as it currently stands, since you'd have to re-sort the > window each time. > Now I'm about to send my patch to introduce more frame types, aggregate cache mechanism in window functions may be broken sometimes, and it is *possible* to put order-by clause in argument list if we prepare tuplesort as in nodeAgg. But I don't see useful cases and it seems so hard task that I'm not sold. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 13 Nov 2009 13:31
2009/11/14 Tom Lane <tgl(a)sss.pgh.pa.us>: > Andrew Gierth <andrew(a)tao11.riddles.org.uk> writes: >> "Peter" == Peter Eisentraut <peter_e(a)gmx.net> writes: >> Peter> This is exactly the syntax that is in the spec AFAICT. > >> Right. The spec defines this syntax for array_agg and xmlagg (only). > > Cool, I had forgotten that they added that in the latest revisions. > I withdraw the complaint that this patch goes too far beyond the spec. > >> But it would be entirely unreasonable, the way postgres works, to >> implement ORDER BY for only specific aggregates. > > Quite. This is another instance of the thing I complained of before, > that the SQL committee likes to define the behavior of specific > aggregates instead of inducing a generic aggregate-behavior definition. > So we're on our own to extract one, and this proposal seems pretty > reasonable to me: it's useful and it's consistent with the query-level > behavior of DISTINCT and ORDER BY. It's not only in aggregates but also window function as well as plain functions like substring(x from t). In window functions, IGNORE NULLS is defined in spec for those first_vlaue(), last_value(), lead(), lag(), etc. but not for generic use. I'm +1 for an approach to apply them for generic cases. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |