Обсуждение: can somebody execute this query on Oracle 11.2g and send result?
Hello, I can't to install Oracle, and need to know result. CREATE TABLE foo(a varchar(10), b varchar(10)); INSERT INTO foo VALUES('aaa',','); INSERT INTO foo VALUES('bbb',';'); INSERT INTO foo VALUES('ccc','+'); SELECT listagg(a,b) FROM foo; Thank you Pavel Stehule
On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
That's not how listagg works.
The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by clause) [OVER partition clause]
If a delimiter is defined, it must be a constant.
Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa,bbb,ccc
Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa;bbb;ccc
Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa+bbb+ccc
-- Hello,
I can't to install Oracle, and need to know result.
CREATE TABLE foo(a varchar(10), b varchar(10));
INSERT INTO foo VALUES('aaa',',');
INSERT INTO foo VALUES('bbb',';');
INSERT INTO foo VALUES('ccc','+');
SELECT listagg(a,b) FROM foo;
That's not how listagg works.
The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by clause) [OVER partition clause]
If a delimiter is defined, it must be a constant.
Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa,bbb,ccc
Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa;bbb;ccc
Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa+bbb+ccc
Jonah H. Harris
2010/1/29 Jonah H. Harris <jonah.harris@gmail.com>: > On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> Hello, >> >> I can't to install Oracle, and need to know result. >> >> CREATE TABLE foo(a varchar(10), b varchar(10)); >> >> INSERT INTO foo VALUES('aaa',','); >> INSERT INTO foo VALUES('bbb',';'); >> INSERT INTO foo VALUES('ccc','+'); >> >> SELECT listagg(a,b) FROM foo; > > That's not how listagg works. > > The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by > clause) [OVER partition clause] > If a delimiter is defined, it must be a constant. > > Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo; > Result: aaa,bbb,ccc > > Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo; > Result: aaa;bbb;ccc > > Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo; > Result: aaa+bbb+ccc > Thank You very much Pavel > -- > Jonah H. Harris >
Jonah H. Harris escribió: > The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by > clause) [OVER partition clause] > If a delimiter is defined, it must be a constant. > > Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo; > Result: aaa,bbb,ccc So that's how Oracle supports ordered aggregates? Interesting -- we just got that capability but using a different syntax. Hmm, the SQL:200x draft also has <within group specification> which seems the standard way to do the ORDER BY stuff for aggregates ... Should we change the syntax? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
2010/1/29 Alvaro Herrera <alvherre@commandprompt.com>: > Jonah H. Harris escribió: > >> The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by >> clause) [OVER partition clause] >> If a delimiter is defined, it must be a constant. >> >> Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo; >> Result: aaa,bbb,ccc > > So that's how Oracle supports ordered aggregates? Interesting -- we > just got that capability but using a different syntax. Hmm, the > SQL:200x draft also has <within group specification> which seems the > standard way to do the ORDER BY stuff for aggregates ... Should we > change the syntax? Oracle syntax is little bit longer, but it is safer. What is a standard? Regards Pavel Stehule p.s. if it is only syntactic suger, then can't be a problem. Pavel > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Alvaro Herrera <alvherre@commandprompt.com> writes: > So that's how Oracle supports ordered aggregates? Interesting -- we > just got that capability but using a different syntax. Hmm, the > SQL:200x draft also has <within group specification> which seems the > standard way to do the ORDER BY stuff for aggregates ... Should we > change the syntax? No. The syntax we are using is also standard. As best I can tell, WITHIN GROUP means something different --- the spec only defines it for rank functions (RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST) and it's basically a shorthand form of a window function call. I find it doubtful that it's actually necessary in Oracle's version of listagg ... regards, tom lane
On Fri, Jan 29, 2010 at 11:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eh?
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
Defines:
I find it doubtful that it's actually necessary in Oracle's version
of listagg ...
Eh?
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
Defines:
LISTAGG (measure_expr [, 'delimiter_expr'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
--
Jonah H. Harris
On Fri, Jan 29, 2010 at 12:09 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
SQL Server's listagg is similar to the PG implementation. It seems Oracle thinks people would prefer to order the list and for that reason, made their listagg a rank function type. Having done quite a bit of work generating delimited lists/arrays based on ordering in PG, I generally agree that it's what I would generally want.
On Fri, Jan 29, 2010 at 11:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:I find it doubtful that it's actually necessary in Oracle's version
of listagg ...
Eh?
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
Defines:LISTAGG (measure_expr [, 'delimiter_expr'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
SQL Server's listagg is similar to the PG implementation. It seems Oracle thinks people would prefer to order the list and for that reason, made their listagg a rank function type. Having done quite a bit of work generating delimited lists/arrays based on ordering in PG, I generally agree that it's what I would generally want.
--
Jonah H. Harris
"Jonah H. Harris" <jonah.harris@gmail.com> writes: >> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm >> >> Defines: >> >> *LISTAGG* (measure_expr [, 'delimiter_expr']) >> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause] Hmph. I don't know what would possess them to model their function on the rank-function syntax extension rather than ARRAY_AGG. The latter seems a lot closer to the functionality that's actually needed. I'm still trying to wrap my brain around what the spec says about the rank-function syntax, but it's notable that the order-by clause is tightly tied to the aggregate input value(s) --- the sort expressions have to have the same number and types as the inputs. Which is certainly not very sensible for listagg. Can anyone figure out exactly what SQL:2008 10.9 rule 6 is actually saying? The references to VE1..VEk in the scalar subquery seem to me to be semantically invalid. They would be sensible if this were a window function, but it's an aggregate, so I don't understand what row they'd be evaluated with respect to. regards, tom lane
2010/1/30 Tom Lane <tgl@sss.pgh.pa.us>: > "Jonah H. Harris" <jonah.harris@gmail.com> writes: >>> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm >>> >>> Defines: >>> >>> *LISTAGG* (measure_expr [, 'delimiter_expr']) >>> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause] > > Hmph. I don't know what would possess them to model their function on > the rank-function syntax extension rather than ARRAY_AGG. The latter > seems a lot closer to the functionality that's actually needed. I'm > still trying to wrap my brain around what the spec says about the > rank-function syntax, but it's notable that the order-by clause is > tightly tied to the aggregate input value(s) --- the sort expressions > have to have the same number and types as the inputs. Which is > certainly not very sensible for listagg. > > Can anyone figure out exactly what SQL:2008 10.9 rule 6 is actually saying? > The references to VE1..VEk in the scalar subquery seem to me to be > semantically invalid. They would be sensible if this were a window > function, but it's an aggregate, so I don't understand what row they'd > be evaluated with respect to. As far as I know <hypothetical set function> is used to do "what-if" analysis. rank(val1) within group (order by sk1) chooses the rank value so that val1 is equivalent to or just greater than sk1 when you calculate rank() over (partition by group order by sk1) within the group. So this is actually an aggregate and in 10.9 rule 6 it extracts only one row from all results of rank() (WHERE MARKER = 1) which is calculated with all rows within the group + argument value list. Again, the argument of this kind of functions should be constant during aggregate (at least it looks like so to me). SELECT salary FROM emp;salary -------- 300 500 700 SELECT rank(530) WITHIN GROUP(ORDER BY salary), rank(200) WITHIN GROUP(ORDER BY salary) FROM emp;rank | rank ------+------ 3 | 1 Googling web, there's been the syntax in Oracle for some time. So I'd bet Oracle crews hated to invent new syntax for listagg() because ordered aggregate can be represented by *existing* WITHIN GROUP syntax although the spec distinguish them. I don't think we should change ordered aggregate syntax we have just introduced, but one of choices is to support both of them. In other words, the queries can be the same: SELECT array_agg(val ORDER BY sk) FROM ... SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ... P.S. I don't have Oracle to try with so I misunderstood something. Regards, -- Hitoshi Harada
Hitoshi Harada <umi.tanuki@gmail.com> writes: > As far as I know <hypothetical set function> is used to do "what-if" > analysis. rank(val1) within group (order by sk1) chooses the rank > value so that val1 is equivalent to or just greater than sk1 when you > calculate rank() over (partition by group order by sk1) within the > group. Hmm. I found this in SQL:2008 4.15: The hypothetical set functions are related to the window functions RANK, DENSE_RANK, PERCENT_RANK, and CUME_DIST, anduse the same names, though with a different syntax. These functions take an argument A and an ordering of a valueexpression VE. VE is evaluated for all rows of the group. This collection of values is augmented with A; the resulting collection is treated as a window partition of the corresponding window function whose window ordering is theordering of the value expression. The result of the hypothetical set function is the value of the eponymous windowfunction for the hypothetical "row" that contributes A to the collection. It appears that the syntax is meant to be hypothetical_function(A) WITHIN GROUP (VE) However this really ought to imply that A contains no variables of the current query, and I don't see such a restriction mentioned anywhere --- maybe an oversight in the spec? If A does contain a variable then there is no unique value to append as the single additional row. I still say that Oracle are completely wrong to have adopted this syntax for listagg, because per spec it does something different than what listagg needs to do. In particular it should mean that the listagg argument can't contain variables --- which is what they want for the delimiter, perhaps, but not for the expression to be concatenated. > In other words, the queries can be the same: > SELECT array_agg(val ORDER BY sk) FROM ... > SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ... One more time: THOSE DON'T MEAN THE SAME THING. If we ever get around to implementing the hypothetical set functions, we would be very unhappy to have introduced such a bogus equivalence. regards, tom lane
2010/2/1 Tom Lane <tgl@sss.pgh.pa.us>: > Hitoshi Harada <umi.tanuki@gmail.com> writes: >> In other words, the queries can be the same: > >> SELECT array_agg(val ORDER BY sk) FROM ... >> SELECT array_agg(val) WITHIN GROUP (ORDER BY sk) FROM ... > > One more time: THOSE DON'T MEAN THE SAME THING. If we ever get > around to implementing the hypothetical set functions, we would > be very unhappy to have introduced such a bogus equivalence. I completely agree. Although Oracle's syntax can express ordered aggregate, by introducing such syntax now it will be quite complicated to implement hypothetical functions for those syntactic restrictions and design in the future. Regards, -- Hitoshi Harada