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

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)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, and use the same names, though
with a different syntax. These functions take an argument A and an
ordering of a value expression 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 the ordering of the value expression.
The result of the hypothetical set function is the value of the
eponymous window function 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