Re: Why can't I use windowing functions over ordered aggregates?

Le vendredi 21 juin 2013 03:32:33, Josh Berkus a écrit :
> Hackers,
>
> So, I can create a custom aggregate "first" and do this:
>
> SELECT first(val order by ts desc) ...
>
> And I can do this:
>
> SELECT first_value(val) OVER (order by ts desc)
>
> ... but I can't do this:
>
> SELECT first_value(val order by ts desc)
>
> ... even though under the hood, it's the exact same operation.

First I'm not sure it is the same, in a window frame you have the notion of
peer-rows (when you use ORDER BY).

And also, first_value is a *window* function, not a simple aggregate
function...

See this example:
# create table foo (i int, t timestamptz);
# insert into foo select n, now() from generate_series(1,10) g(n);
# select i, first_value(i) over (order by t desc) from foo;
# select i, first_value(i) over (order by t desc ROWS between 0 PRECEDING and
UNBOUNDED FOLLOWING) from foo;

What do you expect "SELECT first(val order by ts desc)" to output ?

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation