> 2013/10/22 Merlin Moncure <mmoncure@gmail.com> >> > With a standard loop, I loop n times, and each times I only need the >> > current >> > row plus the previous row which I put in memory, thus O(n). >> >> For posterity, the above is incorrect. Since the aggregate is ordered >> through the window function, it gets executed exactly once per output >> row. It behaves exactly like a loop. You know this because there is >> no array in the aggregate state. >>
> just out of pure curiosity, > is it always the case or is it due to this particular aggregate?
It is always the case. Generally speaking, aggregates, especially user defined aggregates, are run once per input row. In this case the main utility of window functions is to order the aggregate execution calls and (especially) allow intermediate output per input row, instead of per aggregate grouping.
On Tue, Oct 22, 2013 at 6:01 PM, Robert James <srobertjames@gmail.com> wrote: > Wow, this is an excellent discussion - and I must admit, a bit beyond > my abilities. Is there a consensus as to the best approach to adopt? > Is Elliot's the best?
For this *specific* problem, I would give Elliot's (extremely clever) query the nod on the basis that it does not require any supporting infrastructure, which is always nice. That being said, once you start getting the mojo of user defined aggregates + window functions it starts to become clear that it's a cleaner way of doing many types of things that are normally handled by loops. merlin