How to do an interpolation in MySQL

Question

I have two tables in a MySQL database and I need am interpolation function but I’m not sure how do approach this. The two tables look like:

Table 1 looks something like…

+----------+-------+
| speed    | Calc  |
+----------+-------+
|    3     |       |
|    4     |       |
|    5     |       |
|    6     |       |
|    7     |       |
|    8     |       |
|    9     |       |
|    10    |       |
|    11    |       |
|    12    |       |
|    13    |       |
|    14    |       |
|    15    |       |
+----------+-------+

Table 2 looks like:

+----------+---------+
| speed    |binspeed |
+----------+---------+
|    3     | 29.1835 |
|    5     | 16.7992 |
|    10    | 10.5918 |
|    15    | 8.4319  |
+----------+---------+

So pretty much I need to check for each speed in table 1. If the speed matches the avgBinSpeed in table 2 then I set Calc = binspeed. If the speed is between values, I need to use a function than uses the next lower and higher values such as calc = lower binspeed + ((table1.speed- lower avgBinSpeed value)/(next avgBinSpeed value))*(next binspeed – lower binspeed) as the example below for speed 3 and 4.

If (Table1.speed = 3)
(update table1 T
Inner join table2 X
On t.speed = x.speed
Set Oregon = binspeed);

If (Table1.speed = 4)
(update table1 T
Inner join table2 X
On t.speed = x.spedd
Set Oregon = (29.1835+((4-3)/(5-3))*( 16.7921-29.1835));

This is what I don’t know how to do. Since 4 its between the avgBinSpeed of 3 and 5 I need to use the binspeed of 3 (29.1835) + ((speed (4) – speed (3))/( avgBinSpeed (5) - avgBinSpeed (3))) * (binspeed of 5 (16.7992) – binspeed of 3 (29.1835). How can I approach this problem so I can get something like the table below?

+----------+-------+
| speed    | Calc  |
+----------+-------+
|    3     | 29.18352307 |
|    4     | 22.98782107 |
|    5     | 16.79211907 |
|    6     | 15.55207188 |
|    7     | 14.31202469 |
|    8     |       |
|    9     |       |
|    10    |       |
|    11    |       |
|    12    |       |
|    13    |       |
|    14    |       |
|    15    |       |
+----------+-------+

Answer 1

You can do this with this statement:

SELECT
    t1.speed,
    CASE 
        WHEN t1.speed = t.speed1 THEN t.binspeed1 
        WHEN (t1.speed > t.speed1 AND t1.speed < t.speed2) THEN
           (t.binspeed1 + ((t1.speed - t.speed1)/(t.speed2-t.speed1))*(t.binspeed2 - t.binspeed1))
        ELSE NULL 
    END calc 
FROM
    t1
INNER JOIN (
    SELECT
      s.speed speed1,
      s.binspeed binspeed1,
      s1.speed speed2,
      s1.binspeed binspeed2
  FROM
      example s
  LEFT JOIN example s1 ON (s.speed < s1.speed)
  WHERE NOT EXISTS (
    SELECT 1
    FROM example as s2
    WHERE s.speed < s2.speed
    AND
        s2.speed < s1.speed
    )
  OR s1.speed IS NULL
) t
ON
    t1.speed >= t.speed1 AND t1.speed < t.speed2;

Demo with the Subselect that generates the table with the next higher row. You can use this to update your table:

UPDATE t1
INNER JOIN (
    SELECT
      s.speed speed1,
      s.binspeed binspeed1,
      s1.speed speed2,
      s1.binspeed binspeed2
  FROM
      example s
  LEFT JOIN example s1 ON (s.speed < s1.speed)
  WHERE NOT EXISTS (
    SELECT 1
    FROM example as s2
    WHERE s.speed < s2.speed
    AND
        s2.speed < s1.speed
    )
  OR s1.speed IS NULL
) t
ON
    t1.speed >= t.speed1 AND t1.speed < t.speed2
SET calc =         
    CASE 
        WHEN t1.speed = t.speed1 THEN t.binspeed1 
        WHEN (t1.speed > t.speed1 AND t1.speed < t.speed2) THEN
           (t.binspeed1 + ((t1.speed - t.speed1)/(t.speed2-t.speed1))*(t.binspeed2 - t.binspeed1))
        ELSE NULL 
    END;

Updated Demo with UPDATE statement

Answer 2

this query will build the data and then do the calculations for you. try it out :)

SET @a := null;
SET @d := null;
SET @bin := null;
set @e := null;

UPDATE table1 temp,
(   SELECT 
        speed, 
        IF(
            base_col IS NULL, 
            (old_binspeed +((speed - old_speed)/(new_speed - old_speed) ) * (new_binspeed - old_binspeed)), 
            base_col
        ) AS update_col
    FROM
    (   SELECT *, 
        IF(binspeed IS NULL, test, binspeed) AS new_binspeed,
        IF(binspeed IS NULL, @BIN, @BIN := binspeed) AS old_binspeed,
        IF(test1 IS NULL, @D, @D := test1) AS new_speed,
        IF(test1 IS NULL, @E, @E := speed) AS old_speed,
        @A := binspeed AS base_col
        FROM
        (    SELECT t1.speed, t2.binspeed,
                (SELECT binspeed FROM table2 WHERE speed > t1.speed ORDER BY speed LIMIT 1) test,
                (SELECT speed FROM table2 WHERE speed > t2.speed ORDER BY speed LIMIT 1) test1
            FROM table1 t1
            LEFT JOIN table2 t2 ON t2.speed = t1.speed
        )t4
    ORDER BY speed
    ) tt
)temp1
SET temp.Calc = temp1.update_col 
    WHERE temp.speed = temp1.speed
;

here is a DEMO