2

I need to build linear interpolation into an SQL query, using a joined table containing lookup values (more like lookup thresholds, in fact). As I am relatively new to SQL scripting, I have searched for an example code to point me in the right direction, but most of the SQL scripts I came across were for interpolating between dates and timestamps and I couldn't relate these to my situation.

Basically, I have a main data table with many rows of decimal values in a single column, for example:

Main_Value
0.33
0.12
0.56
0.42
0.1

Now, I need to yield interpolated data points for each of the rows above, based on a joined lookup table with 6 rows, containing non-linear threshold values and the associated linear normalized values:

Threshold_Level     Normalized_Value
0                            0
0.15                         20
0.45                         40
0.60                         60
0.85                         80
1                           100

So for example, if the value in the Main_Value column is 0.45, the query will lookup its position in (or between) the nearest Threshold_Level, and interpolate this based on the adjacent value in the Normalized_Value column (which would yield a value of 40 in this example).

I really would be grateful for any insight into building a SQL query around this, especially as it has been hard to track down any SQL examples of linear interpolation using a joined table.

It has been pointed out that I could use some sort of rounding, so I have included a more detailed table below. I would like the SQL query to lookup each Main_Value (from the first table above) where it falls between the Threshold_Min and Threshold_Max values in the table below, and return the 'Normalized_%' value:

    Threshold_Min   Threshold_Max   Normalized_%
    0.00                0.15             0
    0.15                0.18             5
    0.18                0.22             10
    0.22                0.25             15
    0.25                0.28             20
    0.28                0.32             25
    0.32                0.35             30
    0.35                0.38             35
    0.38                0.42             40
    0.42                0.45             45
    0.45                0.60             50
    0.60                0.63             55
    0.63                0.66             60
    0.66                0.68             65
    0.68                0.71             70
    0.71                0.74             75
    0.74                0.77             80
    0.77                0.79             85
    0.79                0.82             90
    0.82                0.85             95
    0.85                1.00             100

For example, if the value from the Main_Value table is 0.52, it falls between Threshold_Min 0.45 and Threshold_Max 0.60, so the Normalized_% returned is 50%. The problem is that the Threshold_Min and Max values are not linear. Could anyone point me in the direction of how to script this?

Jon
  • 21
  • 3
  • Please expand on your expected results based on the given main data table example. It doesn't sound like you're actually trying to use linear interpolation here - looks more like rounding. – Nick Vaccaro Feb 23 '12 at 14:13
  • Thanks @Norla, I have added a detailed table to the original question - I think that rounding would be a better solution, as you suggest! – Jon Feb 24 '12 at 13:18
  • @heikkim - I'm using SQL Server 2005 – Jon Feb 24 '12 at 13:19
  • @Jon Glad to help. I've worked with linear interpolation in SQL - it sucks. : D – Nick Vaccaro Feb 24 '12 at 14:34

1 Answers1

0

Assuming you want the Main_Value and the nearest (low and not high) or equal Normalized_Value, you can do it like this:

select t1.Main_Value, max(t2.Normalized_Value) as Normalized_Value
from #t1 t1
inner join #t2 t2 on t1.Main_Value >= t2.Threshold_Level
group by t1.Main_Value

Replace #t1 and #t2 by the correct tablenames.

aF.
  • 64,980
  • 43
  • 135
  • 198
  • Thanks @aF, I've updated my original question with a more detailed table - if I created 2 seperate lookup tables for Threshold_Min and Threshold_Max, then I could use the script by adding `INNER JOIN on t1.Main_Value >= t1.Threshold_Min AND <= t2. Threshold_Max`? – Jon Feb 24 '12 at 13:21