I have some table which has some rows where data is not exists, due to some biz reason I can not show the null or 0 to user during those days, so need to keep previous value of that table.
create table foo (ID VARCHAR(10), foo_value int, foo_date date);
insert into foo (
values
('234534', 100, '2017-01-01'),
('234534', 200, '2017-01-02'),
('234534', 300, '2017-01-03'),
('234534', 180, '2017-01-08')
);
I want the data like below when I query a table, the missed date should be added with value of previous date
ID | foo_value | foo_date -----------+-----------------+------------ 234534 | 100 | 2017-01-01 234534 | 200 | 2017-02-01 234534 | 300 | 2017-03-01 234534 | 300 | 2017-04-01 234534 | 300 | 2017-05-01 234534 | 300 | 2017-06-01 234534 | 180 | 2017-07-01
I am using JPA to query the table
@Query(value = “SLECT * FROM Foo where ID=:uid”) Lits getFoo(String uid);
Advertisement
Answer
Recursive CTEs are a pretty easy way to fill-in-the-gaps like this:
with recursive cte as (
select f.id, f.foo_value, f.foo_date,
lead(f.foo_date, 1, f.foo_date) over (partition by f.id order by f.foo_date) - interval '1 day' as next_date
from foo f
union all
select cte.id, cte.foo_value, (cte.foo_date + interval '1 day')::date, cte.next_date
from cte
where cte.foo_date < cte.next_date
)
select *
from cte;
They make it easy to retain the values you want from the previous row.
The most efficient method, though, is probably to use generate_series() — but within each row:
with f as (
select f.id, f.foo_value, f.foo_date,
coalesce(lead(f.foo_date) over (partition by f.id order by f.foo_date) - interval '1 day', f.foo_date) as next_date
from foo f
)
select f.id, f.foo_value, gs.dte
from f left join lateral
generate_series(f.foo_date, f.next_date, interval '1 day') gs(dte)
Here is a db<>fiddle.