Re: Windowing functions vs aggregates

2009/4/14 Teodor Sigaev <teodor(at)sigaev(dot)ru>:
> select avg(s)::int4 OVER () from foo;

You can put the cast outside the window expression such as:

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

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? This is not related to the cast:

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

I haven't recompiled recently and I do recall some bug fixes a while
back. Was this that? I'm recompiling now.

--
greg