Re: more support for various frame types of window functions

On Mon, Nov 09, 2009 at 06:39:54PM +0900, Hitoshi Harada wrote:
> 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;

This is very, very exciting. Is there a public repository people can
check out? In particular, I'm curious about how to handle ROWS vs.
RANGE, e.g.:

avg(t) OVER (...
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS smooth_five_points

vs.

avg(t) OVER (...
RANGE BETWEEN
INTERVAL '2 day' PRECEDING AND
INTERVAL '2 day' FOLLOWING) AS five_day_average

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

First, it's wonderful to hear you're working on this. :)

Second, it's tradition on the PostgreSQL project to start with a
slow(ish) and correct implementation, then make it faster. NTFs or
other speed boosts, while nice to have, would not be needed for a
first production implementation. After all, the alternatives without
the native ones are usually slow, buggy, unstable, or combinations of
all three.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate