oracle++sql分析,Oracle SQL分析查询

您可以使用累计最大值和最小值获得上一行和下一行(这假设里程表仅沿一个方向).其余的只是算术插值的算术：

select d.last_updated_date, d.odometer,

(case when d.odometer is not null then d.odometer

else prev_o + (next_o - prev_o) * (last_updated_date - prev_lud) / (next_lud - prev_lud)

end)

from (select d.*,

max(case when odometer is not null then last_updated_date end) over (order by last_updated_date) as prev_lud,

max(odometer) over (order by last_updated_date) as prev_o,

min(case when odometer is not null then last_updated_date end) over (order by last_updated_date desc) as next_lud,

min(odometer) over (order by last_updated_date desc) as next_o

from demo d

) d;