【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,'-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;