"Gavin Sherry" <[EMAIL PROTECTED]> writes: > Wow. What a coincidence! Windows are slightly more complex though. As you > probably know, there are two ways of specifying the window frame: by an > absolute number of rows (ROWS N PRECEDING, for example); or, by a 'range' > (RANGE N PRECEDING), where the range, in the case of 'preceding', is > determined by subtracted the range parameter from the value of the current > field -- i.e., the window attribute.
Actually I think there's a third distinct subcase here as well. While in theory "RANGE UNBOUNDED PRECEDING" could be done using the same logic as N PRECEDING I think it makes more sense to treat it as a distinct case because it is amenable to better plans. For RANGE N PRECEDING in the general case we need to reapply the window aggregates over the entire window partition for every record. There may be a property some window aggregate functions have of being able to "remove" the effects of an state transition which allows for an optimization (for example avg() which keeps a running sum and count can easily subtract the old tuple being aged out of the window). But not all aggregates can do this. RANK() I believe will need to resort the entire window partition for every record. However for RANGE UNBOUNDED PRECEDING we can apply a different plan. Keep the state variable for each window aggregate around for the entire time. For each record apply the state transition function then apply the FINAL function to generate the result for that record but keep the state variable as it was for the next record. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster