Re: SQL:2003 Window Functions for postgresql 8.3?

On Thu, Aug 24, 2006 at 02:47:20PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > The main thing I want to use them for is for cumulative output.
> > ...
> > With window functions you define for each row a "window" which is from
> > the beginning of the table to that row and then sum the values, for
> > each row. Then you just divide by the total, nice.
>
> Egad. Wouldn't that involve O(N) memory and O(N^2) operations?
> Perhaps an extremely smart optimizer could improve this using knowledge
> of the specific aggregates' behaviors, but for "black box" aggregates
> it sounds pretty unworkable.

Yeah well, what's more important: speed or the fact you can write it at
all. Currently you'd do it with a self join, which is at least as
expensive.

For windows running from the beginning, it's just a matter of
outputting at each step of the aggregate, that's not hard. I beleive
the window definitions are clear enough that you can place an upper
bound on the number of rows you have to remember.

An important point is, once a tuple has left the "window" it never comes
back. Thus a tuple is in the "window" for a specific range of source
tuples. Tuples leave the window in the same order they entered.

The conditions of a range are basically one of:

- fixed number of rows from beginning of table
So

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

is the whole frame. You can use CURRENT ROW

- fixed number of rows relative to current row

Like 10 PRECEDING includes the previous ten rows.

- all rows within a certain "range" relative to current sort key. Like
a fix number of days ahead or behind a date type.

Ofcourse, if user say they want the last 7 days and you come toa
seciton of the table where this happens to match a lot of rows, well,
tough.

But it's not necessarily a huge amount of data, though you're going to
run any aggregate *lots* times...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.