如何在 MYSQL 中使用多个表进行插值

我的数据库中有2个表，我需要一个插值函数来创建报告.最上面的表如下所示:

表1

+--+--------+-------+-----+
|ID| Acreage| Fvalue| Type|
+--+--------+-------+-----+
| 1|   16.24|   null|    1|
| 2|    2.17|   null|    1|
| 3|  138.00|   null|    3|
| 4|  138.00|   null|    1|
| 5|  142.47|   null|    3|
| 6|    6.16|   null|    2|
| 7|   14.80|   null|    2|
| 8|   26.01|   null|    1|
| 9|   26.01|   null|    3|
|10|    1.45|   null|    3|
+--+--------+-------+-----+

表2

+--------+-------+-----+
| Acreage| Factor| Type|
+--------+-------+-----+
|       0|   3.35|    1|
|       1|   3.35|    1|
|       3|    2.3|    1|
|       5|   1.92|    1|
|      10|   1.42|    1|
|      15|    1.2|    1|
|      20|      1|    1|
|     999|      1|    1|
|       0|   2.22|    2|
|       1|   2.22|    2|
|       3|   1.97|    2|
|       5|   1.76|    2|
|      10|   1.55|    2|
|      15|   1.32|    2|
|      20|   1.07|    2|
|      22|      1|    2|
|     999|      1|    2|
|       0|   6.93|    3|
|       1|   6.93|    3|
|       3|   5.39|    3|
|       5|   4.05|    3|
|      10|   2.51|    3|
|      15|   2.08|    3|
|      20|   1.69|    3|
|      25|   1.31|    3|
|      30|      1|    3|
|     999|      1|    3|
+--------+-------+-----+

我需要判断表1中的每个面积和表2中的面积，但类型也必须匹配.如果面积介于两个值之间，我必须使用一个函数，该函数使用最接近的上下值，将两个数字之间的平均值取出来，然后始终向上取整.

我从这篇How to do an interpolation in MySQL篇文章中获得灵感

我使用SQL Fiddle创建了一个演示

正确时应该是这样的

+--+--------+-------+-----+
|ID| Acreage| Fvalue| Type|
+--+--------+-------+-----+
| 1|   16.24|  1.151|    1|
| 2|    2.17|  2.736|    1|
| 3|  138.00|      1|    3|
| 4|  138.00|      1|    1|
| 5|  142.47|      1|    3|
| 6|    6.16|  1.712|    2|
| 7|   14.80|  1.330|    2|
| 8|   26.01|      1|    1|
| 9|   26.01|  1.248|    3|
|10|    1.45|  6.584|    3|
+--+--------+-------+-----+


Edit*

I ended up getting this to work, so I'm building around it instead.


```drop table if exists t1;

insert into t1 values
(1,16.24,null,1),
(2,2.17,null,1),
(3,138.00,null,3),
(4,138.00,null,1),
(5,142.47,null,3),
(6,6.16,null,2),
(7,14.80,null,2),
(8,26.01,null,1),
(9,26.01,null,3),
(10,1.45,null,3)
;

insert into example values
(0,3.35,1),
(1,3.35,1),
(3,2.3,1),
(5,1.92,1),
(10,1.42,1),
(15,1.2,1),
(20,1,1),
(999,1,1),
(0,2.22,2),
(1,2.22,2),
(3,1.97,2),
(5,1.76,2),
(10,1.55,2),
(15,1.32,2),
(20,1.07,2),
(22,1,2),
(999,1,2),
(0,6.93,3),
(1,6.93,3),
(3,5.39,3),
(5,4.05,3),
(10,2.51,3),
(15,2.08,3),
(20,1.69,3),
(25,1.31,3),
(30,1,3),
(999,1,3);

UPDATE t1
INNER JOIN (
    SELECT
      s.Acreage Acreage1,
      s.Factor Factor1,
      s1.Acreage Acreage2,
      s1.Factor Factor2,
      s1.Type Type1,
      s.Type Type2
  FROM
      example s
  LEFT JOIN example s1 ON (s.Acreage < s1.Acreage)  and s.Type = s1.Type
  WHERE NOT EXISTS (
    SELECT 1
    FROM example as s2
    WHERE s.Acreage < s2.Acreage
    AND
        s2.Acreage < s1.Acreage
    )
  OR s1.Acreage IS NULL
) t
ON
    t1.Acreage >= t.Acreage1 AND t1.Acreage < t.Acreage2  and t1.Type = t.Type1
SET Fvalue =         
    CASE 
        WHEN t1.Acreage = t.Acreage1 THEN t.Factor1 
        WHEN (t1.Acreage > t.Acreage1 AND t1.Acreage < t.Acreage2) THEN
           (t.Factor1 + ((t1.Acreage - t.Acreage1)/(t.Acreage2-t.Acreage1))*(t.Factor2 - t.Factor1))
        ELSE NULL 
    END;

我更新的小提琴示例可以在这里找到

新测试表

create table table1 (ID int, Acreage decimal(10,2), Fvalue decimal(8, 4), Type int);
insert into table1 values
(1,0.75,null,6),
(2,0.42,null,6),
(3,0.66,null,6),
(4,0.62,null,6),
(5,0.51,null,6),
(6,0.46,null,6),
(7,0.66,null,6),
(8,0.72,null,6),
(9,0.5,null,6),
(10,0.05,null,6)

;

create table table2 (Acreage int, Factor decimal(8, 4), Type int);
insert into table2 values
(999,1,6),
(1,1,6),
(0.9,0.95,6),
(0.8,0.9,6),
(0.7,0.85,6),
(0.6,0.8,6),
(0.5,0.75,6),
(0.4,0.7,6),
(0.3,0.65,6),
(0.2,0.6,6),
(0.1,0.6,6)

;

编辑2*已解决.

我忽略了这样一个事实，即我使用Int创建了table2面积列，而它不处理小于1的值的原因是....他们在表中被改为1.