After a couple of hours reading the SQL:2008 spec, I've come to some
conclusions about the semantics that are demanded for window functions.
Anyone want to to check my work?
* If window functions are used together with aggregates or grouping,
the grouping and regular aggregation happens first, and then windowing
is done on the output rows (group rows). See section 7.12 rules 11,12.
* Therefore, the argument of a window function can contain aggregate
functions, and cannot reference ungrouped columns (both unlike regular
aggregates). Conversely, an ordinary aggregate's argument can't
contain a window function.
* Also, window specifications can contain aggregate functions, and
cannot reference ungrouped columns; this is because they represent
another layer of grouping/ordering on top of the GROUP BY if any.
(An explicit ORDER BY, if any, happens last of all.)
* It is not very clear whether a window function's argument can contain
another window function. 7.11 forbids window specifications from
containing window functions, which is sensible, but I can't find any
such statement about the arguments. The present patch forbids nesting
window functions, and I'm fine with imposing that as an implementation
restriction even if it's not in the spec; but can anyone find it in the
spec?
* Unlike aggregates, there doesn't seem to be any concept of a window
function being attached to an outer-level query --- in fact 6.10 rule
4 says that a window function's argument can't contain outer references
at all. That seems excessively strong, but it does seem that there is
no point in a "winlevelsup" field in WindowFunc, nor in implementing
any infrastructure for outer-level window functions.
* The last part of section 4.14 states that two different window
functions must be evaluated against the same sorted row ordering if
they have syntactically identical partition and ordering clauses,
even if the windows are otherwise distinct (in particular there
could be different framing clauses). Since we don't currently implement
framing clauses the latter is not too interesting, but it's still true
that the patch as I currently have it doesn't fully meet that
requirement: if you intentionally specify separate but equivalent named
window definitions, it won't be smart enough to fold them together,
and you could end up with extra sorts happening and possibly a different
ordering of sort-peer rows. How worried are we about that?
regards, tom lane