Nested window functions not permitted

Поиск
Список
Период
Сортировка
От Oliver Elphick
Тема Nested window functions not permitted
Дата
Msg-id 1447022259.4444.28.camel@phoenix
обсуждение исходный текст
Ответы Re: Nested window functions not permitted  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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?




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

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Error when test on DBT2 Postgresql
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Nested window functions not permitted