Re: WITHIN GROUP patch

>>>>> "Vik" == Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> writes:

Vik> I certainly want it. I do not have a copy of the SQL standard,
Vik> but I have full faith in the Andrew Gierth's claim that this is
Vik> part of it.

For reference, this is how I believe it matches up against the spec
(I'm working from the 2008 final):

10.9 <aggregate function>:

<hypothetical set function> is intended to be implemented in this
patch exactly as per spec.

<inverse distribution function>: the spec defines two of these,
PERCENTILE_CONT and PERCENTILE_DISC:

PERCENTILE_CONT is defined in the spec for numeric types, in which
case it returns an approximate numeric result, and for interval, in
which case it returns interval. Our patch defines percentile_cont
functions for float8 and interval input types, relying on implicit
casting to float8 to handle other numeric input types.

As an extension to the spec, we define a percentile_cont function
that returns an array of percentile values in one call, as suggested
by some users.

PERCENTILE_DISC is defined in the spec for the same types as _CONT.
Our version on the other hand accepts any type which can be sorted,
and returns the same type. This does mean that our version may return
an exact numeric type rather than an approximate one, so this is a
possible slight deviation from the spec.

i.e. our percentile_disc(float8) within group (order by bigint)
returns a bigint, while the spec seems to imply it should return an
approximate numeric type (i.e. float*).

Again, we additionally provide an array version which is not in the
spec.

mode() is not in the spec, we just threw it in because it was easy.

6.10 <window function>

The spec says that <hypothetical set function> is not allowed as a
window function.

The spec does not forbid other <ordered set function>s in a window
function call, but we have NOT attempted to implement this (largely
for the same reasons that DISTINCT and ORDER BY are not implemented
for aggregates as window functions).

Conformance: all the relevant features are parts of T612, "Advanced
OLAP Operations", which we already list in the docs on the unsupported
list with the comment "some forms supported". Maybe that could be
changed now to "most forms supported", but that's a subjective call
(and one we didn't really consider doing in this patch).

--
Andrew (irc:RhodiumToad)