Re: more support for various frame types of window functions

On Mon, Nov 09, 2009 at 11:20:39PM +0900, Hitoshi Harada wrote:
> 2009/11/9 David Fetter <david@fetter.org>:
> > 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?
> Not so far as always. The step is quite small so I don't believe we
> need developing repository but I'll create it when needed.

Thanks :)

> >  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
> 
> I've not finished reading spec completely, but in the first frame
> starts at exactly 2 rows before current row and ends at exactly 2
> rows after current row. The latter is a bit more complicated but it
> means the frame starts at the beginning of peers whose value in
> ORDER BY clause is current row value - 2 days and so on.

That's pretty much it.  The spec may have some other things to say
about corner cases, NULLs, etc.

> > First, it's wonderful to hear you're working on this. :)
> Thanks. I hope it will be done until 8.5 release.

Will you be at the JPUG 10th anniversary?

Might code be there in time for that?

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