I have 2 tables in my database and I need an interpolation funtion to create a report. The top tables look like:
Table 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|
-- -------- ------- -----
Table 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|
-------- ------- -----
I need to check each Acreage in table 1 on the Acreage in table 2 but the Types have to match as well. If the Acreage is between values I have to use a function that uses the closest value below and above, pull the average between the two numbers, and always round up.
I took inspiration from this post How to do an interpolation in MySQL but I can't quite get it right. I'm not sure how to join the two tables in the calculations since I have decimal values in my table 1 Acreage column.
I created a demo using SQL Fiddle http://sqlfiddle.com/#!9/ed60a9/1
It should look something like this when correct
-- -------- ------- -----
|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;
My updated Fiddle example can be found here http://sqlfiddle.com/#!9/477c1c
New testing tables
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)
;
Edit 2* Solved.
I overlooked the fact that I created my table2 Acreage column using Int, and the reason it wasn't working on values lower than 1 was because....they were being changed in the table to 1.
CodePudding user response：
In MySQL 8.0.14 or newer you can use lateral queries to find the border rows. Then interpolation is trivial.
For example:
select
a.id,
a.acreage,
case when lo.type is not null and hi.type is not null
then lo.factor (a.acreage - lo.acreage)
/ (hi.acreage - lo.acreage)
* (hi.factor - lo.factor)
end as fvalue,
a.type
from table1 a,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage <= a.acreage
order by b.acreage desc
limit 1
) lo,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage > a.acreage
order by b.acreage
limit 1
) hi
Result:
id acreage fvalue type
--- -------- ------------- ----
1 16.24 1.1504000000 1
2 2.17 2.7357500000 1
3 138.00 1.0000000000 3
4 138.00 1.0000000000 1
5 142.47 1.0000000000 3
6 6.16 1.7112800000 2
7 14.80 1.3292000000 2
8 26.01 1.0000000000 1
9 26.01 1.2473800000 3
10 1.45 6.5835000000 3
See running example at db<>fiddle.
If you want to update the table you can combine the query above into an UPDATE statement, as in:
update table1 y
join (
select
a.id,
case when lo.type is not null and hi.type is not null
then lo.factor (a.acreage - lo.acreage)
/ (hi.acreage - lo.acreage)
* (hi.factor - lo.factor)
end as fvalue
from table1 a,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage <= a.acreage
order by b.acreage desc
limit 1
) lo,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage > a.acreage
order by b.acreage
limit 1
) hi
) x on x.id = y.id
set y.fvalue = x.fvalue
See Update fiddle.