【SQL】填充空值为最近的不为空的数据

需求：一个数据集，用相近日期的数值去填充，比如10月2的用10月1的填充，10月4-6的用10月3的填充

 解决方案：

1.开窗函数，lead

with tmp1 as 
(select date,retail from tenant_zhuolie_test.qqq where retail is not null)
,tmp2 as (
select date,retail,lead(date,1,'9999-12-31') over (order by date) as date1 from tmp1
)
,tmp3 as (select a.date,b.retail
from tenant_zhuolie_test.qqq a ,tmp2 b
where a.retail is null and (a.date between b.date and b.date1)
union all 
select date,retail from tenant_zhuolie_test.qqq where retail is not null)
select date,retail from tmp3 order by date

2.利用generate_series函数进行填充

with tmp as (select date,retail,lead(date,1,'2022-10-13') over (order by date) as end_date from tenant_zhuolie_test.qqq 
where retail is not null)
select generate_series(date,end_date-1,'1 days')::date,
retail
from tmp;