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
|
| Список | pgsql-bugs |
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)"
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:
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 по дате отправления: