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" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
