Re: Efficiently selecting single row from a select with window functions row_number, lag and lead

On 1/1/16, Andrew Bailey <hazlorealidad(at)gmail(dot)com> wrote:
> I would like to do the following:
>
> select id, row_number() over w as rownum, lag(id, 1) over w as prev,
> lead(id, 1) over w as next from route where id=1350 window w as (order by
> shortname, id asc rows between 1 preceding and 1 following) order by
> shortname, id ;
>
> However this gives the result
> 1350;1;;
>
> The following query gives the result I am expecting
>
> select * from (select id, row_number() over w as rownum,
> lag(id, 1) over w as prev, lead(id, 1) over w as next
> from route window w as (order by shortname, id
> rows between 1 preceding and 1 following) order by shortname, id) as s
> where id=1350
>
> 1350;3;1815;1813
>
> The explain plan is
> "Subquery Scan on s (cost=0.14..15.29 rows=1 width=32)"
> " Filter: (s.id = 1350)"
> " -> WindowAgg (cost=0.14..13.51 rows=143 width=12)"
> " -> Index Only Scan using route_idx on route (cost=0.14..10.29
> rows=143 width=12)"
>
> as it makes use of the index created as follows
>
> CREATE INDEX route_idx
> ON route
> USING btree
> (shortname COLLATE pg_catalog."default", id);
>
> I believe that the index has all the data that is needed to obtain the
> results in a single query.
> Is it possible to write the query as a single select and if so how?
>
> Thanks in advance
>
> Andrew Bailey
>

It works as expected.

You can read it at
http://www.postgresql.org/docs/current/static/tutorial-window.html
---
The rows considered by a window function are those of the "virtual
table" produced by the query's FROM clause as filtered by its WHERE,
GROUP BY, and HAVING clauses if any. For example, a row removed
because it does not meet the WHERE condition is not seen by any window
function.
---

So your WHERE clause from the first query selects the only row to a
"virtual table", and lead and lag works with the table contains the
only one row and doesn't have any other before and after it.

In the second query subselect selects ALL rows to the "virtual table",
lead and lag fill values and WHERE in the external select gets a
single row filled by subselect.

It is also in the documentation:
"If there is a need to filter or group rows after the window
calculations are performed, you can use a sub-select."

Unfortunately it is impossible to give an access to window function to
rows not selected by a current query.

--
Best regards,
Vitaly Burovoy