CODEWITHSUNDEEP
sqloracle-sqldeveloperinterpolation
Ladislao Vidal
Ladislao Vidal

Reputation: 15

Oracle SQL Interpolation

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

Upvotes: 1

Views: 790

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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;

Upvotes: 2

Related Questions