On Wed, Sep 22, 2010 at 6:03 AM, Dennis Björklund <d...@zigo.dhs.org> wrote: > In > > http://www.postgresql.org/docs/9.0/static/tutorial-window.html > > it say > > "Although avg will produce the same result no matter what order it > processes the partition's rows in, this is not true of all window > functions. When needed, you can control that order using ORDER BY within > OVER." > > While it's true that avg() produce the same result no matter what order. A > ORDER BY clause will affect what rows are included in the computation and > thus change the result (the default window frame is > RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). So one can not in > general add an ORDER BY to the example in the tutorial and get the same > result as without an ORDER BY. > > Maybe we can find some better wording of the above?
Yeah, that doesn't seem right. rhaas=# create table foo (a integer); CREATE TABLE rhaas=# insert into foo values (1); INSERT 0 1 rhaas=# insert into foo values (2); INSERT 0 1 rhaas=# insert into foo values (3); INSERT 0 1 rhaas=# select a, avg(a) over () from foo; a | avg ---+-------------------- 1 | 2.0000000000000000 2 | 2.0000000000000000 3 | 2.0000000000000000 (3 rows) rhaas=# select a, avg(a) over (order by a) from foo; a | avg ---+------------------------ 1 | 1.00000000000000000000 2 | 1.5000000000000000 3 | 2.0000000000000000 (3 rows) But I confess that I'm sort of murky on how ORDER affects the window frame, or how to rephrase this more sensibly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
