How can I perform linear interpolation using oracle SQL?

Question

I am trying to use Oracle 11g (11.1 in dev, 11.2 in production) for numeric analysis, specifically linear interpolation on a table which has three columns of interest: a timestamp, a deviceid, and value.

The value columns holds data from the device (with id deviceid), taken at the time given in the timestamp. For example, this is bogus data, but it gives the idea:

     time       |  deviceid  |  value   
----------------|------------|-----------
 01:00:00.000   |  001       | 1.000
 01:00:01.000   |  001       | 1.030
 01:00:02.000   |  001       | 1.063 
 01:00:00.050   |  002       | 553.10
 01:00:01.355   |  002       | 552.30
 01:00:02.155   |  002       | 552.43

The timestamps from device 001 do not match the timestamps of device 002, but I need to have the values from both device 001 and 002 in one row, with one timestamp, matching the timestamp for device 001. What I want to end up with is something like this:

     time       |  device 001  |  device 002   
----------------|--------------|------------
 01:00:00.000   |  1.000       |  null
 01:00:01.000   |  1.030       |  552.520
 01:00:02.000   |  1.063       |  552.405

Where the value for device 002 was linearly interpolated based on the values for device 002 gathered at the two closest timestamps on either side of each timestamp for device 001. The null occurs because I don't have two timestamps for device 002 on either side of 01:00:00.000, and I don't want to extrapolate the value.

From what I understand I can use percentile_cont to do this, but I don't understand the examples I have seen online. For example, where would the percentile used by percentile_cont come from?

Thanks in advance for your help!

Thanks for the answer so far. I'm very new to Oracle analytic functions and my data is not as tidy as the example I gave. For example, device1 has several values for every one value on device2. The values increment in approximately tenths of a second for one device, and thirds of a second for the other device. Editing the question to give more detail... — GLaDOS, Sep 14 '11 at 18:47

score 3 · Accepted Answer · answered Sep 09 '11 at 21:43

I'm not sure how you'd use PERCENTILE_CONT to do the interpolation you ask for, but with the help of a different analytic function you can achieve what you want.

Firstly, we'll create the following function, which converts INTERVAL DAY TO SECOND values into seconds:

CREATE OR REPLACE FUNCTION intvl_to_seconds(
    p_interval INTERVAL DAY TO SECOND
) RETURN NUMBER DETERMINISTIC
AS
BEGIN
  RETURN EXTRACT(DAY FROM p_interval) * 24*60*60
       + EXTRACT(HOUR FROM p_interval) * 60*60
       + EXTRACT(MINUTE FROM p_interval) * 60
       + EXTRACT(SECOND FROM p_interval);
END;
/

With this function we can use a query such as the following:

SELECT d1.time,
       d1.value AS value1,
       q2.prev_value + intvl_to_seconds(d1.time - q2.prev_time) * (q2.next_value - q2.prev_value)/intvl_to_seconds(q2.next_time - q2.prev_time) AS value2
  FROM devices d1
  LEFT OUTER JOIN (SELECT d2.time AS prev_time,
                          d2.value AS prev_value,
                          LEAD(d2.time, 1) OVER (ORDER BY d2.time) AS next_time,
                          LEAD(d2.value, 1) OVER (ORDER BY d2.time) AS next_value
                     FROM devices d2
                    WHERE d2.deviceid = 2) q2
               ON d1.time BETWEEN q2.prev_time AND q2.next_time
 WHERE d1.deviceid = 1;

I took your data above, set the date component of the timestamps to today, and I got the following results when I ran the query above:

TO_CHAR(D1.TIME)                          VALUE1     VALUE2
------------------------------------- ---------- ----------
09-SEP-11 01.00.00.000000                      1
09-SEP-11 01.00.01.000000                   1.03 552.517625
09-SEP-11 01.00.02.000000                  1.063 552.404813

(I added a TO_CHAR around d1.time to cut down on excessive spacing in SQL*Plus.)

If you're using DATEs instead of TIMESTAMPs, you don't need the function: you can just subtract the dates.

An alternative to writing a function is to use `CAST(timestamp_column AS DATE)`. That can avoid a significant number of SQL-to-PL/SQL-and-back context switches. — Adam Musch, Sep 12 '11 at 16:54
... and nope, I can't. I lose the fractional seconds data if I cast to date. — GLaDOS, Sep 14 '11 at 18:36
I have been working on trying to convert the timestamp to something like number of milliseconds since Jan 1 1970; I don't actually have permission on the db to create functions. — GLaDOS, Sep 14 '11 at 23:35
@GLaDOS: I used a function to make the query look a little neater. If you don't have permission to create a function, you can replace the calls to the function with the various calls to `EXTRACT` instead. — Luke Woodward, Sep 15 '11 at 07:10
@GLaDOS: I used a LEFT OUTER JOIN to generate the null value as in the first row of your expected output data, since I assumed that was what you wanted. — Luke Woodward, Sep 15 '11 at 18:12
@Luke Woodward: I want null values rather than extrapolating data where there is no endpoint time for device 2. But, when I run this, all calculations for the inner select are null, not just those beyond endpoints. — GLaDOS, Sep 15 '11 at 22:00
Finally got what the problem was. Didn't know there was a problem until a bug was opened on the code using the query. See the answer below for my final solution. The date range was not included in the inital question; and therein lay the problem. — GLaDOS, Oct 19 '11 at 18:09

GLaDOS · Answer 2 · 2011-10-19T18:07:54.193

I am using a modified version of @Luke Woodward's query:

SELECT d1.time,
   d1.value AS value1,
   q2.prev_value + 
   (EXTRACT( SECOND FROM (d1.time - q2.prev_time)) +
    EXTRACT( MINUTE FROM (d1.time - q2.prev_time)) * 60 ) 
    * (q2.next_value - q2.prev_value)/
      (EXTRACT ( SECOND FROM (q2.next_time - q2.prev_time)) + 
      EXTRACT ( MINUTE FROM (q2.next_time - q2.prev_time)) * 60)  AS value2
FROM devices d1
LEFT OUTER JOIN (SELECT d2.time AS prev_time,
                      d2.value AS prev_value,
                      LEAD(d2.time, 1) OVER (ORDER BY d2.time) AS next_time,
                      LEAD(d2.value, 1) OVER (ORDER BY d2.time) AS next_value
                 FROM devices d2
                WHERE d2.deviceid = 2
                      and time between '20100914 000000' and '20100915 000000'
                ) q2
           ON d1.time BETWEEN q2.prev_time AND q2.next_time
 WHERE d1.deviceid = 1;

but the interpolated values are always coming up as null, even though there is data for device 2 in the date range.

Note, I had to add a date range for the query in q2 which is perhaps why the normal join loses the outer data.

I don't get null values for the interpolated data if I use a normal join, but in using a normal join, I lose the data for the device 1 outside the endpoints for device 2 (the interpolated device in q2). Suggestions?

Final solution below... the order got switched... may get switched again... so final solution I used is the one that says "The final solution ..." — GLaDOS, Oct 19 '11 at 21:09

score 0 · Answer 3 · answered Oct 19 '11 at 18:21

The final solution with the date range:

SELECT
    d1.time,
    d1.value AS value1,
    q2.prev_value + 
    (EXTRACT( SECOND FROM (d1.time - q2.prev_time)) +
     EXTRACT( MINUTE FROM (d1.time - q2.prev_time)) * 60 ) 
     * (q2.next_value - q2.prev_value)/
       (EXTRACT ( SECOND FROM (q2.next_time - q2.prev_time)) + 
        EXTRACT ( MINUTE FROM (q2.next_time - q2.prev_time)) * 60
    )  AS value2
FROM devices d1
LEFT OUTER JOIN (
    SELECT d2.time AS prev_time,
           d2.value AS prev_value,
           LEAD(d2.time, 1) OVER (ORDER BY d2.time) AS next_time,
           LEAD(d2.value, 1) OVER (ORDER BY d2.time) AS next_value
    FROM devices d2
    WHERE d2.deviceid = 2
    AND time BETWEEN '20100914 000000' AND '20100915 000000'
) q2
ON d1.time BETWEEN q2.prev_time AND q2.next_time
WHERE d1.deviceid = 1
AND time BETWEEN '20100914 000000' AND '20100915 000000';

How can I perform linear interpolation using oracle SQL?

3 Answers3

Linked