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@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
