CODEWITHSUNDEEP
sqloracle-database
CodeMaster
CodeMaster

Reputation: 449

SQL query to interpolate between values

I intend to interpolate (linear interpolation) between values in a column and insert that into a new column using a SQL query. Based on my search online, I suspect LEAD analytic function could be useful. I am new to writing SQL queries. So, any insights on how it can be achieved will be quite helpful.

The sample data set is as described:

Emp  Test_date  Value
---  ---------  -----
A    1/1/2001   null
A    1/2/2001   100
A    1/3/2001   null
A    1/4/2001   80
A    1/5/2001   null
A    1/6/2001   null
A    1/7/2001   75

The idea is to get a fourth column that has values as:

null
100
interpolatedValue1
80
interpolatedValue2
interpolatedValue3
75

Interpolatedvalue1 would be an interpolated value between 100 and 80,

Interpolatedvalue2 would be a linearly interpolated value between 80 and 75.

InterpolatedValue3 would be a linearly interpolated value between Interpolatedvalue2 and 75

Here's how a simple linear interpolation works:

Given two points (V1 at D1), (V3 at D3). What would be the value V2 at D2?

(V3-V1)/(D3-D1) * (D2-D1) + V1

Upvotes: 2

Views: 7304

Answers (3)

Domerich
Domerich

Reputation: 1

here a fix for SQL Impala with ASC interpolation

select emp, test_date, value,
  coalesce(value,
    (next_value - prev_value) -- v3-v1
    / (count(*) over (partition by grp) + 1) -- d3-d1
    * row_number() over (partition by grp order by test_date desc) -- d2-d1, indirectly
    + prev_value -- v1
  ) as interpolated
from (
  select emp, test_date, value,
    last_value(value ignore nulls)
      over (partition by emp order by test_date) as prev_value,
    first_value(value ignore nulls)
      over (partition by emp order by test_date range between current row and unbounded following) as next_value,
    row_number() over (partition by emp order by test_date) -
      row_number() over (partition by emp order by case when value is null then 1 else 0 end, test_date) as grp
  from test.table
) t

order by test_date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can use lag(ignore nulls). You don't specify how to do the interpolation, but a linear interpolation would be:

select emp, test_date,
       coalesce(test_value,
                ( next_tv * (next_td - test_date) +
                  prev_tv * (test_date - prev_td)
                ) / (next_td - prev_td)
               ) as imputed_value
from (select t.*,
             lag(test_value ignore nulls) over (partition by emp order by test_date) as prev_tv,
             lag(case when test_value is not null then test_date end ignore nulls) over (partition by emp order by test_date) as prev_td,
             lead(test_value ignore nulls) over (partition by emp order by test_date) as next_tv,
             lead(case when test_value is not null then test_date end ignore nulls) over (partition by emp order by test_date) as next_td
      from t
     ) t

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191425

This can probably be simplified a bit but gets the answer you wanted, I believe. The slightly tricky bit is getting both the number of days between not-null values (i.e. the size of the gap you're filling) and then the position within that gap:

-- CTE for sample data
with your_table (emp, test_date, value) as (
            select 'A', date '2001-01-01', null from dual
  union all select 'A', date '2001-01-02', 100 from dual
  union all select 'A', date '2001-01-03', null from dual
  union all select 'A', date '2001-01-04', 80 from dual
  union all select 'A', date '2001-01-05', null from dual
  union all select 'A', date '2001-01-06', null from dual
  union all select 'A', date '2001-01-07', 75 from dual
)
-- actual query
select emp, test_date, value,
  coalesce(value,
    (next_value - prev_value) -- v3-v1
    / (count(*) over (partition by grp) + 1) -- d3-d1
    * row_number() over (partition by grp order by test_date desc) -- d2-d1, indirectly
    + prev_value -- v1
  ) as interpolated
from (
  select emp, test_date, value,
    last_value(value ignore nulls)
      over (partition by emp order by test_date) as prev_value,
    first_value(value ignore nulls)
      over (partition by emp order by test_date range between current row and unbounded following) as next_value,
    row_number() over (partition by emp order by test_date) -
      row_number() over (partition by emp order by case when value is null then 1 else 0 end, test_date) as grp
  from your_table
)
order by test_date;

E TEST_DATE       VALUE INTERPOLATED
- ---------- ---------- ------------
A 2001-01-01                        
A 2001-01-02        100          100
A 2001-01-03                      90
A 2001-01-04         80           80
A 2001-01-05              76.6666667
A 2001-01-06              78.3333333
A 2001-01-07         75           75

I've used last_value and first_value instead of lead and lag, but either works. (Lead/lag might be faster on a large data set I suppose). The grp calculation is Tabibitosan.

Upvotes: 6

Related Questions