Postgres Pro
Downloads
Home   >   mailing lists

Thread: Nested window functions not permitted

Nested window functions not permitted

From
Oliver Elphick
Date:
I tried to do this:

SELECT     p.company, p.start, p.yearend, p.idnum,
           s.pdno, s.pdend,
           CASE WHEN nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) OVER w IS NULL
                THEN p.start
                ELSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) + '1 day'::INTERVAL
           END AS spdstart
  FROM     period AS p,
           subperiod AS s
  WHERE    p.company = s.company AND p.yearend = s.yearend
  WINDOW   w AS (PARTITION BY p.start ORDER BY s.pdno)
  ORDER BY p.yearend, s.pdno;

in order to get these results:

 company |   start    |  yearend   | idnum | pdno |   pdend    |  spdstart
---------+------------+------------+-------+------+------------+-----------
 GBS     | 2014-02-01 | 2015-01-31 |     1 |    1 | 2014-04-30 | 2014-02-01
 GBS     | 2014-02-01 | 2015-01-31 |     1 |    2 | 2014-07-31 | 2014-05-01
 GBS     | 2014-02-01 | 2015-01-31 |     1 |    3 | 2014-10-31 | 2014-08-01
 GBS     | 2014-02-01 | 2015-01-31 |     1 |    4 | 2015-01-31 | 2014-11-01
 GBS     | 2015-02-01 | 2016-01-31 |     2 |    1 | 2015-04-30 | 2015-02-01
 GBS     | 2015-02-01 | 2016-01-31 |     2 |    2 | 2015-07-31 | 2015-05-01
 GBS     | 2015-02-01 | 2016-01-31 |     2 |    3 | 2015-10-31 | 2015-08-01
 GBS     | 2015-02-01 | 2016-01-31 |     2 |    4 | 2016-01-31 | 2015-11-01
(8 rows)

but apparently nesting window functions is not permitted (in 9.3.10).
Is this possible in later versions of PG? or is there any other way to
look at a value from the previous row?

Re: Nested window functions not permitted

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> I tried to do this:
> SELECT     p.company, p.start, p.yearend, p.idnum,
>            s.pdno, s.pdend,
>            CASE WHEN nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) OVER w IS NULL
>                 THEN p.start
>                 ELSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) + '1 day'::INTERVAL

Um, don't you just want lead() or lag()?

            regards, tom lane

Re: Nested window functions not permitted

From
Oliver Elphick
Date:
On Sun, 2015-11-08 at 17:50 -0500, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > I tried to do this:
> > SELECT     p.company, p.start, p.yearend, p.idnum,
> >            s.pdno, s.pdend,
> >            CASE WHEN nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) OVER w IS NULL
> >                 THEN p.start
> >                 ELSE nth_value(s.pdend,(row_number() OVER w)::INTEGER -1) + '1 day'::INTERVAL
>
> Um, don't you just want lead() or lag()?
>
>             regards, tom lane

So I do!  Thanks.