Cédric Villemain-2 wrote
> And also, first_value is a *window* function, not a simple aggregate
> function...
Per the documentation any aggregate function can be used with a WINDOW
declaration. The logical question is why are window aggregates special so
that the reverse cannot be true? In other words why is not every function
simply defined as a normal aggregate that can be used in both contexts?
> 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 ?
Undefined due to incorrect specificity of the ORDER BY definition. The
window version has the same issue.
The window aggregates should simply treat the entire input set as the
relevant frame - basically the same output as would result from
(simplistically):
SELECT window_agg(...)
FROM (
SELECT id, window_agg(...) OVER (ORDER BY id ASC) ORDER BY id ASC
) agg
ORDER BY id DESC LIMIT 1
Admittedly this really only makes sense for first_value, last_value, and
nth_value; the other window aggregates can return valid values but to have
meaning they really need to be output in a windowing context.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Why-can-t-I-use-windowing-functions-over-ordered-aggregates-tp5760233p5760358.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.