就是判断前后两个非空值，然后根据位置填充数据。[list=1]
[*]with t as
[*] (select 1.2 num from dual
[*] union all
[*] select 0 from dual
[*] union all
[*] select 3.6 from dual
[*] union all
[*] select 0 from dual
[*] union all
[*] select 0 from dual
[*] union all
[*] select 7.26 from dual),
[*]t1 as (select rownum id, decode(num, 0, null, num) num from t),
[*]t3 as
[*] (select id, num
[*] from (select rownum id, decode(num, 0, null, num) num from t)
[*] order by id desc)
[*]select id, num,
[*] case when num is not null then num
[*] else to_char(last_num + ((next_num - last_num) / max_interval) * max_level)
[*] end result
[*] from (select id, num, last_id, last_num, next_num, max_interval,
[*] row_number() over(partition by max_interval order by id) max_level
[*] from (select id, num, last_id, last_num, next_num,
[*] case
[*] when num is not null then 0
[*] else last_value(max_interval ignore nulls) over(order by id desc) -
[*] last_value(max_interval ignore nulls) over(order by id)
[*] end max_interval
[*] from (select id, num, last_id, last_num, next_num,
[*] case
[*] when last_num = next_num then last_id
[*] else null
[*] end max_interval
[*] from (select t1.id, t1.num, t2.id last_id,
[*] last_value(t1.num ignore nulls) over(order by t1.id) last_num,
[*] last_value(t3.num ignore nulls) over(order by t3.id desc) next_num
[*] from t1, t1 t2, t3
[*] where t1.id = t2.id
[*] and t1.id = t3.id)))
[*] order by id);

[i]复制代码
ID NUM RESULT
---------- ---------------------------------------- ----------------------------------------
1 1.2 1.2
2 2.4
3 3.6 3.6
4 4.82
5 6.04
6 7.26 7.26