hive-sql 连续数据有null的用紧邻两行非空平均值填充

with tmp as (
          select 'A' as breed, '2023-03-03' as dt, 4521 as value
union all select 'A' as breed, '2023-03-04' as dt, null as value
union all select 'A' as breed, '2023-03-05' as dt, null as value
union all select 'A' as breed, '2023-03-06' as dt, 4430 as value
union all select 'B' as breed, '2023-03-03' as dt, 4470 as value
union all select 'B' as breed, '2023-03-04' as dt, null as value
union all select 'B' as breed, '2023-03-05' as dt, null as value
union all select 'B' as breed, '2023-03-06' as dt, 4310 as value
union all select 'C' as breed, '2023-03-06' as dt, 4470 as value
union all select 'C' as breed, '2023-03-07' as dt, null as value
union all select 'C' as breed, '2023-03-08' as dt, null as value
union all select 'C' as breed, '2023-03-09' as dt, null as value
union all select 'C' as breed, '2023-03-10' as dt, null as value
union all select 'C' as breed, '2023-03-11' as dt, null as value
union all select 'C' as breed, '2023-03-12' as dt, null as value
union all select 'C' as breed, '2023-03-13' as dt, null as value
union all select 'C' as breed, '2023-03-14' as dt, 4480 as value
) 
select *
,nvl(value,(first_value(value,true) over (partition by breed  order by dt rows between unbounded preceding and current row) +
last_value(value,true) over (partition by breed  order by dt rows between current row and unbounded following))/2
) from tmp