需求:一个数据集,用相近日期的数值去填充,比如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;