Re: SQL:2003 Window Functions for postgresql 8.3?

Clinging to sanity, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) mumbled into her beard:
> 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.

Doing this *efficiently* presumably isn't in the cards for 8.2 :-).

The way that I'd do this sort of thing right now would be by writing a
set-returning stored proc that walks through tuples in some order.

Returning, let's say, the sum up to the current row shouldn't require
special amounts of memory.

sum := 0;

select * into rec from my_table order by trans_on loop
sum += rec.amount;
ret.sum := sum;
-- set ret.* to rec.*
return next ret;
end loop;

At worst, that should cost O(N) memory; no need to cost O(N^2)
operations...
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/emacs.html
"I've seen a look in dogs' eyes, a quickly vanishing look of amazed
contempt, and I am convinced that basically dogs think humans are
nuts." -- John Steinbeck