On Thu, Aug 24, 2006 at 02:47:20PM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate.
signature.asc
Description: Digital signature
