Re: SQL:2003 Window Functions for postgresql 8.3?

On Thu, Aug 24, 2006 at 02:26:53PM -0400, AgentM wrote:
> Could someone elaborate on the window functions? This page http://
> en.wikipedia.org/wiki/SELECT has some examples but they make it seem
> like the functions are an overly-verbose LIMIT statement. So what's
> the benefit?

Look for more sources, but they're kinda cool.

The main thing I want to use them for is for cumulative output. Think
of a table with data like this:

Foo | 3
Bar | 6
Baz | 5
Blah | 6

What you want is an output that goes down the table and gives a
cumulative percentage. First row is 3/20, second 9/20, etc... In normal
SQL this is painful, with selfjoins and such.

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.

A "window" can be specified in a number of ways, such as "two rows back
to two rows ahead" or from the beginning or end of output, so you can
easily do averages covering the surrounding week (if you had daily
data). A window is an ordered set, rather than the usual unordered sets
SQL usually has.

The standard has much more detail, but this is just a taste.

Hope this helps,
--
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.