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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: BUG #15143: Window Functions – Paranthese not allowed before OVER term
Дата
Msg-id CAKFQuwZq0N5ki5h7=zbEeJEgn2mkERxGqK5pxWHEnfTrirjr4w@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #15143: Window Functions – Paranthese not allowed before OVER term  (PG Bug reporting form <noreply@postgresql.org>)
Ответы Re: BUG #15143: Window Functions – Paranthese not allowed before OVER term  (David Rowley <david.rowley@2ndquadrant.com>)
Список pgsql-bugs
Дерево обсуждения
On Wed, Apr 4, 2018 at 4:41 PM, PG Bug reporting form <noreply@postgresql.org> wrote:
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

​Working as documented:

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

https://www.postgresql.org/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:

https://www.postgresql.org/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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15143: Window Functions – Paranthese not allowed before OVER term
Следующее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #14999: pg_rewind corrupts control file global/pg_control