Re: ordered aggregates using WITHIN GROUP (was Re: can somebody execute this query on Oracle 11.2g and send result?)

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