MySQL多表按Type匹配实现线性插值计算更新Fvalue字段

涉及表结构与初始样例数据 #

待计算表（表1） #

表1存储业务原始数据，Fvalue字段待计算填充：

+--+--------+-------+-----+
|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） #

表2存储不同Type下，不同面积区间对应的基准系数Factor：

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

Fvalue字段计算规则 #

• 两表关联必须满足Type字段完全匹配
• 若表1记录的Acreage恰好等于表2中某条基准记录的Acreage，直接取对应Factor作为Fvalue
• 若表1记录的Acreage落在表2两个相邻Acreage基准值区间内，取区间上下最接近的两个基准值对应的Factor做线性插值计算，结果作为Fvalue
• 若Acreage大于表2同Type下最大的基准值（样例中为999），直接取Factor=1作为Fvalue

预期计算结果 #

计算完成后表1数据应符合如下结果：

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

实现逻辑与问题排查 #

初始UPDATE实现 #

参考通用MySQL插值方案编写的关联更新逻辑如下，针对大于1的Acreage值可正常计算：

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;

后续新增小于1的小数测试场景，测试表结构与数据如下：

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

运行上述UPDATE逻辑后，该测试场景下所有记录计算结果异常。

根因定位与修复 #

异常根因：创建测试用表2时误将Acreage字段定义为INT类型，所有小于1的小数值基准点存入时被MySQL自动截断为整数1，导致相邻区间匹配完全失效，最终计算结果错误。

修复方案：将表2的Acreage字段类型修改为支持小数的DECIMAL(10,2)（或其他符合精度要求的小数类型），重新插入全量基准数据后，原有插值逻辑即可覆盖所有面积场景，正确计算Fvalue值。

内容的提问来源于stack exchange，提问作者Ethan Graybeal