Re: Windowing functions vs aggregates

Greg Stark <stark(at)enterprisedb(dot)com> writes:
> However, I'm kind of confused by that result. Why does the range
> "between unbounded preceding and current row" seem to be doing the
> average of the whole result set?

That's what it's supposed to do. "Current row" really includes all peers
of the current row in the window frame ordering, and since you didn't
specify any ORDER BY clause, all the rows are peers. If you put in
"order by s" you'll get the result you were expecting:

regression=# select s,(avg(s) OVER (range between unbounded preceding
and current row)) from foo;
s | avg
---+--------------------
1 | 2.5000000000000000
2 | 2.5000000000000000
3 | 2.5000000000000000
4 | 2.5000000000000000
(4 rows)

regression=# select s,(avg(s) OVER (order by s range between unbounded preceding

and current row)) from foo;
s | avg
---+------------------------
1 | 1.00000000000000000000
2 | 1.5000000000000000
3 | 2.0000000000000000
4 | 2.5000000000000000
(4 rows)

I suppose the SQL committee defined it like that to try to reduce the
implementation dependency of the results.

regards, tom lane