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