Oracle SQL Interpolation

Question

I am having trouble solving a problem of linear interpolation with Oracle SQL. I start with data like the one in the following table:

 Bucket        My Value
  1         22,506,791,194.47 
  7         12,506,791,194.47 
  15        2,506,791,194.47 
  30        1,605,711,104.47 
  90       -3,128,740,962.21

Where the Bucket is in days. The whole point is that I need to find the exact day (using interpolation) when My Value will turn negative , I know it is at some point betweeen 30 and 90 days if I interpolate.

I saw another example here How can I perform linear interpolation using oracle SQL?

But it is not the same since I need to find a particular value (The 0)

Thanks

What is the algorithm that you want to implement? Do you just want to calculate a linear least squares line that best fits the data and to determine where that approximation has a value of 0? — Justin Cave, Oct 25 '16 at 21:30
That's it a simple linear OLS would work, though I prefer to interpolate. The point is to get as close as posible to the day the data passes the x axis. Assuming x as the days — Ladislao Vidal, Oct 25 '16 at 21:38
There are multiple possible interpolation algorithms. I would expect that a least squares line fit would produce a more accurate result than doing a simple estimate from the point just above 0 and the point just below 0. I would expect that the other data points would add some information. But that is a guess, I don't know your data. You presumably do. Gordon's answer appears correct if you want to ignore all but two points. — Justin Cave, Oct 26 '16 at 17:09

score 2 · Answer 1 · answered Oct 25 '16 at 21:31

It would be something like this:

select (abs(prev_value) / (value - prev_value)) * (bucket - prev_bucket) + prev_bucket
from (select t.*,
             lag(value) over (order by bucket) as prev_value,
             lag(bucket) over (order by bucket) as prev_bucket
      from t
     ) t
where prev_value < 0 and value > 0;

Oracle SQL Interpolation

1 Answers1