Oracle SQL analytical query

Question

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 1

Here is a query that will give you the values that are missing. It uses two regular joins to locate the previous and next record where an odometer value is available.

SELECT
    d.last_update_date,
    d0.odometer 
        + (d1.odometer - d0.odometer) * ( d.last_update_date - d0.last_update_date ) 
        / ( d1.last_update_date - d0.last_update_date ) odometer
FROM
    demo d
    INNER JOIN demo d0 ON d0.last_update_date = (
        SELECT MAX(last_update_date) 
        FROM demo 
        WHERE odometer IS NOT NULL AND last_update_date < d.last_update_date
    )
    INNER JOIN demo d1 ON d1.last_update_date = (
        SELECT MIN(last_update_date) 
        FROM demo 
        WHERE odometer IS NOT NULL AND last_update_date > d.last_update_date
    )
WHERE d.odometer IS NULL;

This DB Fiddle demo returns :

 LAST_UPDATE_DATE | ODOMETER
 :--------------- | ----------:
 08-OCT-18        | 48491.17
 15-OCT-18        | 51744.97
 21-OCT-18        | 55425.15
 22-OCT-18        | 56266.17

The value on October 8 seems to be the exactly the one you expect.

---

If you are looking to actually update the table to add the missing values, you can use the Oracle MERGE syntax, as shown is this db fiddle :

MERGE INTO demo target 
USING (
    SELECT
        d.last_update_date,
        d0.odometer 
            + (d1.odometer - d0.odometer) * ( d.last_update_date - d0.last_update_date ) 
            / ( d1.last_update_date - d0.last_update_date ) odometer
    FROM
        demo d
        INNER JOIN demo d0 ON d0.last_update_date = (
            SELECT MAX(last_update_date) 
            FROM demo 
            WHERE odometer IS NOT NULL AND last_update_date < d.last_update_date
        )
        INNER JOIN demo d1 ON d1.last_update_date = (
            SELECT MIN(last_update_date) 
            FROM demo 
            WHERE odometer IS NOT NULL AND last_update_date > d.last_update_date
        )
    WHERE d.odometer IS NULL
) src ON (src.last_update_date = target.last_update_date)
WHEN MATCHED THEN UPDATE SET target.odometer = src.odometer;

Answer 2

Here's how I would have done it. It may help in other similar problems (linear interpolation) where the "value" cannot be assumed to be increasing with time. For an odometer, that assumption makes perfect sense, and Gordon Linoff's solution is simpler; I offer this solution for other applications where the "quantity" may go down as well as up over time.

with
  sample_data(last_updated_date, odometer) as (
    select to_date('05-OCT-18', 'dd-MON-rr'), 47174.77 from dual union all
    select to_date('08-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('12-OCT-18', 'dd-MON-rr'), 50246.37 from dual union all
    select to_date('15-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('19-OCT-18', 'dd-MON-rr'), 53743.11 from dual union all
    select to_date('21-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('22-OCT-18', 'dd-MON-rr'), null     from dual union all
    select to_date('25-OCT-18', 'dd-MON-rr'), 58789.22 from dual
  )
, prep(last_updated_date, odometer, prev_date, next_date, prev_odo, next_odo) as (
    select last_updated_date, odometer,
           case when odometer is null
                then max(nvl2(odometer, last_updated_date, null))
                     over (order by last_updated_date) end,
           case when odometer is null
                then min(nvl2(odometer, last_updated_date, null))
                     over (order by last_updated_date 
                     rows between 1 following and unbounded following) end,
           last_value(odometer ignore nulls) over (order by last_updated_date),
           first_value(odometer ignore nulls) over (order by last_updated_date 
                                rows between 1 following and unbounded following)
    from   sample_data
  )
select   last_updated_date,
         nvl( odometer,
              round(prev_odo + (next_odo - prev_odo) * 
                   (last_updated_date - prev_date) / (next_date - prev_date), 2)
            ) as odometer
from     prep
order by last_updated_date
;

OUTPUT

LAST_UPDATED_DATE   ODOMETER
----------------- ----------
05-OCT-18           47174.77
08-OCT-18           48491.17
12-OCT-18           50246.37
15-OCT-18           51744.97
19-OCT-18           53743.11
21-OCT-18           55425.15
22-OCT-18           56266.17
25-OCT-18           58789.22

Answer 3

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;

Answer 4

You can use OUTER APPLY (as of Oracle 12c) to get the previous and next row with an ODOMETER. Then use a formula for linear interpolation.

select
  demo.last_updated_date,
  coalesce
  (
    demo.odometer,
    prev.odometer + ( (next.odometer - prev.odometer) *
                      (demo.last_updated_date - prev.last_updated_date) /
                      (next.last_updated_date - prev.last_updated_date) )
  ) as odometer
from demo
outer apply
(
  select *
  from demo d
  where d.last_updated_date < demo.last_updated_date
  and d.odometer is not null
  order by d.last_updated_date desc
  fetch first row only
) prev
outer apply
(
  select *
  from demo d
  where d.last_updated_date > demo.last_updated_date
  and d.odometer is not null
  order by d.last_updated_date desc
  fetch first row only
) next
order by demo.last_updated_date;

Result (rounded):

LAST_UPDATED_DATE | ODOMETER
------------------+---------
05-OCT-18         | 47174.77
08-OCT-18         | 48916.94
12-OCT-18         | 50246.37
15-OCT-18         | 52217.80
19-OCT-18         | 53743.11
21-OCT-18         | 55425.15
22-OCT-18         | 56266.17
25-OCT-18         | 58789.22

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=54cc9d4b7dd5793e1c0025627fd929de