I realized it's simpler to create separate variables (ex, Sum(x) and SUM(Y)) and put them in an inner table. It's one of those things you look at with new fresh eyes the next days and it just makes sense.
On 5 April 2018 at 14:40, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Wed, Apr 4, 2018 at 7:32 PM, David Rowley <david.rowley@2ndquadrant.com> > wrote: >> >> > Working as documented: >> > >> > "A window function call always contains an OVER clause directly >> > following >> > the window function's name and argument(s)" >> >> Yeah, how else would the window function know which window clause it >> belongs to? >> >> If you'd done: SELECT (LEAD(col) - LAG(col)) OVER (...) could >> PostgreSQL just assume that you meant to link both the lead and lag to >> the same over clause? > > > Well, if there is only a single aggregate function in the expression there > isn't any ambiguity. If there happened to be more than one the system could > emit a parsing error saying as much. While likely more user-friendly I > don't imagine its worth the headache in the parser.
Perhaps, but I guess it would be pretty hard to know what's an aggregate and what's a window function when there are multiple.
Consider:
SELECT (SUM(x) - SUM(y)) OVER w1 FROM t WINDOW w1 AS (...);
Is SUM(x) an aggregate or a window function? how about SUM(y)? one of them must be since there's an OVER clause.
OVER is also quite like FILTER, so someone may expect us to also support:
SELECT (SUM(x) - SUM(y)) FILTER(WHERE x > 0) FROM t;
So I think we're pretty good to leave this untouched.
