Thread: Windowing functions vs aggregates

Cast of aggregate's type works:
# select avg(s)::int4 from foo;

but that doesn't work for with new windowing functions interface:
# select avg(s)::int4 OVER () from foo;
ERROR:  syntax error at or near "OVER"
LINE 1: select avg(s)::int4 OVER () from foo;

Is that intentional?

-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 

2009/4/14 Teodor Sigaev <teodor@sigaev.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 |   22 |   23 |   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.00000000000000002 | 2.00000000000000003 | 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

Teodor Sigaev <teodor@sigaev.ru> writes:
> Cast of aggregate's type works:
> # select avg(s)::int4 from foo;

> but that doesn't work for with new windowing functions interface:
> # select avg(s)::int4 OVER () from foo;
> ERROR:  syntax error at or near "OVER"
> LINE 1: select avg(s)::int4 OVER () from foo;

> Is that intentional?

You would need to put the cast around the whole "foo() over ()"
construct.  That's not a divisible unit.
        regards, tom lane

Greg Stark <stark@enterprisedb.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.50000000000000002 | 2.50000000000000003 | 2.50000000000000004 | 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.000000000000000000002 |     1.50000000000000003 |     2.00000000000000004 |
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