Oracle SQL analytical query Oracle SQL analytical query oracle oracle

Oracle SQL analytical query


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;


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 odometerfrom     preporder by last_updated_date;

OUTPUT

LAST_UPDATED_DATE   ODOMETER----------------- ----------05-OCT-18           47174.7708-OCT-18           48491.1712-OCT-18           50246.3715-OCT-18           51744.9719-OCT-18           53743.1121-OCT-18           55425.1522-OCT-18           56266.1725-OCT-18           58789.22


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 ) odometerFROM    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;