Re: more support for various frame types of window functions - Mailing list pgsql-hackers

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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