Обсуждение: BUG #15143: Window Functions – Paranthese not allowed before OVER term

The following bug has been logged on the website:

Bug reference:      15143
Logged by:          David
Email address:      david@daily-harvest.com
PostgreSQL version: 9.6.6
Operating system:   Mac / DBeaver
Description:

```SELECT *,
(P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id ORDER
BY P2.received_at DESC) AS time_diff
--((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
(PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
time_diff_minutes
FROM javascript.pages P2```

For the second line, I have to remove the parentheses around (P2.received_at
- LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
importantly, I can't seem to get the 3rd line (currently commented out) to
run because of this issue of Postgres seeming to not allow parentheses
before the OVER

On 5 April 2018 at 12:23, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form
>> SELECT *,
>> (P2.received_at - LAG(P2.received_at)) OVER (PARTITION BY anonymous_id
>> ORDER
>> BY P2.received_at DESC) AS time_diff
>> --((EXTRACT(EPOCH FROM (P2.received_at - LEAD(P2.received_at)))/60)) OVER
>> (PARTITION BY P2.anonymous_id ORDER BY P2.received_at DESC) AS
>> time_diff_minutes
>> FROM javascript.pages P2```
>>
>> For the second line, I have to remove the parentheses around
>> (P2.received_at
>> - LAG(P2.received_at)) for it to run. Which doesn't make sense. And more
>> importantly, I can't seem to get the 3rd line (currently commented out) to
>> run because of this issue of Postgres seeming to not allow parentheses
>> before the OVER
>
>
> 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?

If you want to shrink the syntax down a bit, then you can define your
WINDOW clauses at the end of the query:

select lead(...) over w,lag(...) over w from table window w as
(partition by ... order by ...);

This might make it easier to read if you're embedding the window
functions in other expressions.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services