Re: [HACKERS] 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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Why can't I use windowing functions over ordered aggregates?
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why can't I use windowing functions over ordered aggregates?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
