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

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

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.

Cedric,

> 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 ?
>

Ah, right, I see what you mean. Yeah, I was doing queries without peer
rows, so it looked the same to me, and it uses some of the same
machinery. But of course it's not completely the same.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com