mysql 插值_如何在MySQL中进行插值

我在MySQL数据库中有两个表,我需要插值函数,但我不确定如何处理它.这两个表看起来像：

表1看起来像……

+----------+-------+

| speed | Calc |

+----------+-------+

| 3 | |

| 4 | |

| 5 | |

| 6 | |

| 7 | |

| 8 | |

| 9 | |

| 10 | |

| 11 | |

| 12 | |

| 13 | |

| 14 | |

| 15 | |

+----------+-------+

表2看起来像：

+----------+---------+

| speed |binspeed |

+----------+---------+

| 3 | 29.1835 |

| 5 | 16.7992 |

| 10 | 10.5918 |

| 15 | 8.4319 |

+----------+---------+

所以我需要检查表1中的每个速度.如果速度与表2中的avgBinSpeed匹配,那么我设置Calc = binspeed.如果速度介于值之间,我需要使用一个函数而不是使用下一个更低和更高的值,例如calc = lower binspeed((table1.speed- lower avgBinSpeed value)/(next avgBinSpeed value))*(next binspeed – lower binspeed)以下为速度3和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));

这是我不知道该怎么办.因为它在avgBinSpeed 3和5之间我需要使用3的速度(29.1835)((速度(4) – 速度(3))/(avgBinSpeed(5) – avgBinSpeed(3)))*(binspeed of 5(16.7992) – 3(29.1835)的binspeed.

我怎样才能解决这个问题所以我可以得到类似下表的内容？

+----------+-------+

| 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 | |

+----------+-------+

解决方法:

此查询将构建数据,然后为您进行计算.试试看 ：)

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

;

这是一个DEMO

标签：mysql,sql

来源： https://codeday.me/bug/20190528/1173004.html