Re: BUG #15143: Window Functions – Paranthese not allowed before OVER term

On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15143
> Logged by: David
> Email address: david(at)daily-harvest(dot)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
>

​Working as documented:

"​A window function call always contains an OVER clause directly following
the window function's name and argument(s)"

/docs/10/static/tutorial-window.html

When trying to create expressions using the result of a window function it
is sometime necessary to move window function computation into a subquery
and perform the calculations in the outer layer.

The formal syntax is defined here:

/docs/10/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

Note, the tutorial probably could use updating since a FILTER clause can be
inserted in between the function invocation and the OVER...but only a
FILTER clause.

David J.