From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Nested window functions not permitted |
Date: | 2015-11-08 22:37:39 |
Message-ID: | 1447022259.4444.28.camel@phoenix |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-11-08 22:50:12 | Re: Nested window functions not permitted |
Previous Message | Adrian Klaver | 2015-11-08 14:45:02 | Re: Error when test on DBT2 Postgresql |