Basic Requirements for SQL Window Functions

An example of a window function is RANK or a moving AVG, though also
include ROW_NUMBER or CUME_DIST. They are a different kind of aggregate
introduced by/included in SQL:2003, which require a "sliding window" of
rows.

The SQL window functions seem to require an ordering for most of their
operations. It is possible that that could be provided by a sort node in
the execution plan. It also seems that this might have some effect on
the MAX/MIN handling issue - I raise this now in case there is some
inter-relationship.

I've started another thread to avoid opening Pandora's box again, but..

Earlier discussions around MAX/MIN handling mention this....

On Thu, 2004-11-11 at 15:24, Tom Lane wrote:

"Zeugswetter Andreas DAZ SD" <ZeugswetterA@spardat.at> writes:

How are you planning to represent the association between MIN/MAX and
particular index orderings in the system catalogs?

Don't we already have that info to decide whether an index handles
an "ORDER BY" without a sort node ?

We know how to determine that an index matches an ORDER BY clause.
But what has an aggregate called MAX() got to do with ORDER BY? Magic
assumptions about operators named "<" are not acceptable answers; there
has to be a traceable connection in the catalogs.

As a real-world example of why I won't hold still for hard-wiring this:
a complex-number data type might have btree opclasses allowing it to be
sorted either by real part or by absolute value. One might then define
max_real() and max_abs() aggregates on the type. It should be possible
to optimize such aggregates the same way as any other max() aggregate.

Are we OK to say that window functions will always need a sort node?

Is there an optimization that anyone can see that might lead us away
from that requirement, and if so do we need to solve the problem
described above?

--
Best Regards, Simon Riggs