Oracle SQL analytical query

SQL> select LAST_UPDATED_DATE, ODOMETER from demo;

LAST_UPDA   ODOMETER
--------- ----------
05-OCT-18   47174.77
08-OCT-18
12-OCT-18   50246.37
15-OCT-18
19-OCT-18   53743.11
21-OCT-18
22-OCT-18
25-OCT-18   58789.22

8 rows selected.

I need to detemine the odometer value where its null and this has to be done using SQL. The way I was thinking to do is —

1. get the previous and the next not null value of odometer and the difference between days using which I can calculate the average distance travelled per day.

For example in this case, (50246.37 – 47174.77) / (12-OCT-18 – 05-OCT-18) = ~ 439

2. Now with the average value of per day in place, calculate the difference between in days and multiply that by avg.

For example, (08-OCT-18 – 05-OCT-18) = 3 days and for 3 days 439 * 3 = 1317. So, value for 08-Oct-18 can be 47174.77+1317 = 48491.77

Now, I need help with writing SQL code for this.

Any help would be greatly appreciated.

Answer

You can get the previous and next row using cumulative max and min (this assumes that the odometer only goes in one direction). The rest is just arithmetic for an arithmetic interpolation:

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;