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(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

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> The SQL window functions seem to require an ordering for most of their
> operations.

AFAICS, the entire concept of a "window" implies the input is ordered
in some way; what operations would they provide that don't require this?

> It is possible that that could be provided by a sort node in
> the execution plan.

Either sort or indexscan, but you'd certainly need one or the other.

regards, tom lane