By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table data (date date, day0 int, day1 int, day2 int, day3 int);
insert into data
values
('2020-01-01',3,2,1,-1),
('2020-01-02',2,3,1,0),
('2020-01-03',4,1,-1,2),
('2020-01-04',5,2,0,1);
4 rows affected
select t.date,
y.day0 - t.day0 as day0,
y.day2 - t.day1 as day1,
y.day3 - t.day2 as day2,
'NaN'::numeric as day3
from data t
join data y on y.date = t.date - 1
where t.date = date '2020-01-02';
| date | day0 | day1 | day2 | day3 |
|---|---|---|---|---|
| 2020-01-02 | 1 | -2 | -2 | NaN |