Basic Requirements for SQL Window Functions

[Simon Riggs <simon(at)2ndquadrant(dot)com>]

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(at)spardat(dot)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