create table test ( when date, foo numeric, bar numeric, alice numeric, bob numeric);
insert into test values ('2016-01-01',1,2,3,4); insert into test values ('2016-01-02',5,6,7,8); insert into test values ('2016-01-03',9,10,11,12); insert into test values ('2016-01-04',13,14,15,16); insert into test values ('2016-01-05',17,18,19,20);
What I would like to do is the following :
(1) Given "select alice,bob from test where foo=1 and bar=2" I would like to return the values of alice, bob *and* the value of foo four days later (i.e. "17" in the above example).
(2) But there may be times where there are insufficient data points, and so I would want to retrieve the last available value (e.g. "select alice,bob from test where foo=9 and bar=10", there is 4 days hence, therefore it would return the last available, i.e. "17" in this example, even though that is only 2 days hence).
select alice,bob,(select foo from test as t where t.when>=test.when and t.when<=test.when+('4 day'::interval) order by when desc limit 1 ) from test where foo=1 and bar=2;
The "t.when>=test.when" part's purpose is not to select too many records. It works without this but you get better performance if there are many records in your table.
