mysql实现线性插值法,MySQL的PHP​​线性插值表

I have a table titled "psytable_moist_air" shown below:

I'm trying to develop a MySQL statement that will interpolate a value that may be between records. (EDIT: If it is easier to do the math in PHP I'm open for that solution too!]

Example: I would like to know the "hda" value where "T" = 17.8.

Notice there is no record where "T"=17.8. However, because this table is linearly related, I can get the "hda" value where "T"=17 and the "hda" value where "T"=18 and do a linear interpolation of the values.

The result math would look like this:

(Get the "hda" value where T=17 : hda = 17.102)

(Get the "hda" value where T=18 : hda = 18.108)

EDIT:

The only way I can think of is to do two MySQL statements to grab the smaller and larger values:

SELECT MAX(`T`), MAX(`hda`) FROM `psytable_moist_air` WHERE `T`<17.8

SELECT MIN(`T`), MIN(`hda`) FROM `psytable_moist_air` WHERE `T`>17.8

Then I would use these two values to do the interpolation. This works but seems pretty inefficient. Can anyone come up with a better solution??

If anyone has any advice it would be much appreciated!

.

解决方案

I believe the best way to do this would be to use the floor function.

See the following;

SET @x = 17.8;

SET @x0 = FLOOR(@x);

SET @x1 = FLOOR(@x) + 1;

SET @y0 = (SELECT `hda` FROM `psytable_moist_air` WHERE `T` = @x0);

SET @y1 = (SELECT `hda` FROM `psytable_moist_air` WHERE `T` = @x1);

SET @y = @y0 + (@y1 - @y0) * ((@x - @x0) / (@x1 - @x0));

Select @y ,@x0 ,@x1, @y0 ,@y1;