Postgress在查询过程中用以前的值填充缺失的行值

Question

我有一些表，其中有一些行的数据不存在，由于某些商业原因，我在那些日子里不能向用户显示null或0，所以需要保留该表以前的值。

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')
);

当我查询表格时，我想要下面这样的数据，遗漏的日期应该添加上一个日期的值

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

我正在使用JPA查询表

@Query(value＝"SLECT*FROM Foo where ID＝：uid"(Lits getFoo(字符串uid(；

Answer 1

递归CTE是填补此类空白的一种非常简单的方法：

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;

它们可以方便地保留上一行中所需的值。

然而，最有效的方法可能是使用generate_series()——但在每行中：

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)

这里有一个db<gt；不停摆弄

Answer 2

您可以使用generate_series()生成每个id的日期，然后进行横向连接以获得相应的值：

select x.id, f.foo_value, x.foo_date
from (
select f.id, x.foo_date
from foo f
cross join lateral generate_series(min(foo_date), max(food_date), '1 day') as x(foo_date)
group by f.id
) x
cross join lateral (
select foo_value
from foo f
where f.id = x.id and f.foo_date <= x.foo_date
order by f.foo_date desc
limit 1
) f

根据您的数据集，使用left join和窗口函数来产生最后一个非null值可能会更有效：

select id, max(foo_value) over(partition by id, grp) as foo_value, foo_date
from (
select x.id, f.value, x.foo_date, 
count(f.id) over(partition by x.id order by x.foo_date) grp
from (
select f.id, x.foo_date
from foo f
cross join lateral generate_series(min(foo_date), max(food_date), '1 day') as x(foo_date)
group by f.id
) x
left join foo on f.id = x.id and f.food_date = x.foo_date
) t