Postgresql Recursion Query

postgresqlwindow functions

I have a table like so:

effective_from  archived      cal
2020-01-19      2020-01-20    15.3
2020-01-13      2020-01-19    42.2
2020-01-17      2020-01-18    13.6
2020-01-16      2020-01-17    11.2
2020-01-15      2020-01-16    7.2
2020-01-14      2020-01-15    7.2
2020-01-13      2020-01-15    8.6
2020-01-13      2020-01-14    4.2
2020-01-12      2020-01-13    3.7

I'd like to write a query that returns a table that has the most recent effective_from row, as determined by the archived row. The most recent effective_from row is the one with the most recent archived date. There are three rows with an effective_from date of Jan-13, but the one I'm interested in is the one where archived = 2020-01-19, cal = 42.2

I can do it in a brute force way, where I order the table by archived and then do a select and union on each row in turn based on the effective_from column:

SELECT effective_from, archived, cal FROM stack_question ORDER BY archived ASC;

(SELECT * FROM stack_question WHERE effective_from <= '12-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '14-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '15-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '16-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '17-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '19-Jan-2020' ORDER BY archived DESC LIMIT 1) ORDER BY effective_from ASC;

This gives me the expected result:

2020-01-12  2020-01-13 3.7
2020-01-13  2020-01-19 42.2
2020-01-19  2020-01-20 15.3

But of course that isn't actually a viable way of doing it. I could always write a function in pg/PLSQL or whatever, but I feel there's an efficient way to do this with just a SELECT. CTEs/recursion seem like the most promising approach, but I can't quite wrap my head around how it would look for my example based on what I've read.

Does anyone have any rough pointers on the best approach here?

I've put my test case on dbfiddle.

Best Answer

Thanks for the fiddle, I extended it a bit: fiddle

select distinct effective_from, archived, cal
from (
    select s1.effective_from
         , s1.archived
         , s1.cal
         , row_number() over (partition by s2.effective_from 
                              order by s1.archived desc) as rn
    from stack_question s1 
    join stack_question s2 
        on s1.effective_from < s2.archived
) as t
where rn = 1
;

I'm in a bit of a hurry, so I don't time to investigate further, but it should give you a start. The idea is to enumerate the rows from s2 to get archive

Related Question