I'm not sure if it can be finished until the start of the next CF, but
I've been working on $subject. This work intends to extend current
limited frame types of window functions such like below;
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- ORDER BY x RANGE BETWEEN CURRENT ROW AND f FOLLOWING
- ORDER BY x RANGE BETWEEN p PRECEDING AND f FOLLOWING
where "p" and "f" are values that indicate preceding/following frame
boundary offsets from current row (or peer). With this feature, you
can calculate something like "moving average" by SQL.
Frame types that won't be introduced in this work includes:
- EXCLUDE clause
The hardest point is that aggregates must be re-initialized as rows
exit from current frame, which doesn't occur in 8.4 design. One of the
solution for this is to let aggregates have "negative trans functions"
(NTF), and some comments in nodeWindowAgg.c say about it, which
current aggregate system doesn't have. But my work doesn't introduce
this mechanism because
1) "negative trans function" doesn't do anything in normal aggregate
2) forcing that to everyone who writes his/her own aggregate is quite
hard and incompatible to older releases
3) so, we must at least support aggregates that don't have NTF even if
it will be introduced in the future
That means moving average is initialized again on frame-off situation
as the frame moves down. I know that may kill it's performance but
reasons above result in my proposing design.
If you have better ideas please feel free to tell me, and any comments welcomed.
Regards,
--
Hitoshi Harada
