双线性插值在SQL

Question

对于一个项目我做我需要根据2种尺寸（“自1900年以来天及产量）

内插的值（利率）目前，我有下面的代码，其中f是插值：

declare @rates table (
    days int, 
    yield int, 
    rate decimal(18,6) 
) 

insert into @rates (days,yield,rate) 
values (1,30,0.1), 
(1,90,0.2), 
(3,30,0.2), 
(null,3,90,0.4) 

declare @data table(
    id int, 
    days int, 
    yield int) 

insert into @data(id,date,days,yield) values 
(1,2,60) 


select r.* 
-- calculation below does not work if x or y ends up being the same 
    -- (because they all cancel each other out) 
,finalinterp = ((f11/((x2 - x1)*(y12 - y11)))*(x2 - x)*(y12 - y)) 
+ ((f21/((x2 - x1)*(y22 - y21)))*(x - x1)*(y22 - y)) 
+ ((f12/((x2 - x1)*(y12 - y11)))*(x2 - x)*(y - y11)) 
+ ((f22/((x2 - x1)*(y22 - y21)))*(x - x1)*(y - y21)) 

from 
(
select id,d.days as x,isnull(x1,x2) x1 ,isnull(x2,x1) x2,d.yield as y, 
ISNULL(y11,y12) y11,ISNULL(y12,y11) y12,ISNULL(y21,y22) y21,ISNULL(y22,y21) y22, 
r11.rate as f11, 
r12.rate as f12, 
r21.rate as f21, 
r22.rate as f22 

from @data d 
cross apply 
( 
    select MAX(r.days) as x1 from @rates r1 
    where r.days <= d.days 
) xt1 
cross apply 
(
    select MIN(r.days) as x2 from @rates r1 
    where days >= d.days 
) xt2 
cross apply 
(
    select MAX(yield) as y11 from @rates r1 
    where r1.days = isnull(x1,x2) 
    and yield <= d.yield 
) yt1 
cross apply 
(
    select MIN(yield) as y12 from @rates r1 
    where r1.days = isnull(x1,x2) 
    and yield >= d.yield 
) yt2 
cross apply 
(
    select MAX(yield) as y21 from @rates r1 
    where r1.days = isnull(x2,x1) 
    and yield <= d.yield 
) yt3 
cross apply 
(
    select MIN(yield) as y22 from @rates r1 
    where r1.days = isnull(x2,x1) 
    and yield >= d.yield 
) yt4 
left outer join @rates r11 on r11.mdays = isnull(x1,x2) and r11.yield = ISNULL(y11,y12) 
left outer join @rates r12 on r12.mdays = isnull(x1,x2) and r12.yield = ISNULL(y12,y11) 
left outer join @rates r21 on r21.mdays = isnull(x2,x1) and r21.yield = ISNULL(y21,y22) 
left outer join @rates r22 on r22.mdays = isnull(x2,x1) and r22.yield = ISNULL(y22,y21) 
) r 

目前这个工程的一个适当的解释值但是如果值确实存在（例如，如果我设置data.yield = 90或data.days = 1），因此不需要进行插值当它试图用零来除数时，它就会崩溃。

有人可以弄清楚如何使它在这种情况下工作吗？

还有没有一个更有效的方法呢？在现实世界中有其他表在同一个查询的整体混搭所以更简洁越好

感谢以下

Answer 1

答案有兴趣的人。没有经过性能测试。

如果x小于x1，则x1值将被用于x> x2和y等等。

declare @rates table (
    mdate datetime, 
    mdays int, 
    yield int, 
    rate decimal(18,6) 
) 

insert into @rates (mdate,mdays,yield,rate) 
values (null,1,30,0.23), 
    (null,1,90,0.36), 
    (null,31,30,0.25), 
    (null,31,90,0.37) 

declare @data table(
    did int, 
    ddate datetime, 
    ddays int, 
    yield int) 


insert into @data(did,ddate,ddays,yield) values 
(1,null,32,30) 


select r2.*, 
f = ((f11/(isnull(nullif(x2 - x1,0),1) * isnull(nullif(y12 - y11,0),1))) * isnull(convert(float,nullif(x2 - x,0)),0.5) * isnull(convert(float,nullif(y12 - y,0)),0.5)) 
+ ((f21/(isnull(nullif(x2 - x1,0),1) * isnull(nullif(y22 - y21,0),1))) * isnull(convert(float,nullif(x - x1,0)),0.5) * isnull(convert(float,nullif(y22 - y,0)),0.5)) 
+ ((f12/(isnull(nullif(x2 - x1,0),1) * isnull(nullif(y12 - y11,0),1))) * isnull(convert(float,nullif(x2 - x,0)),0.5) * isnull(convert(float,nullif(y - y11,0)),0.5)) 
+ ((f22/(isnull(nullif(x2 - x1,0),1) * isnull(nullif(y22 - y21,0),1))) * isnull(convert(float,nullif(x - x1,0)),0.5) * isnull(convert(float,nullif(y - y21,0)),0.5)) 
from 
    (
     select 
     case when x > x2 then x2 
      when x < x1 then x1 
      else x end as x, 
     case when y > y22 then y22 
      when y < y11 then y11 
      else y end as y, 
     x1,x2,y11,y12,y21,y22,f11,f12,f21,f22 
    from 
    (
     select did,ddays as x,isnull(x1,x2) x1 ,isnull(x2,x1) x2,d.yield as y,ISNULL(y11,y12) y11,ISNULL(y12,y11) y12,ISNULL(y21,y22) y21,ISNULL(y22,y21) y22, 
     r11.rate as f11, 
     r12.rate as f12, 
     r21.rate as f21, 
     r22.rate as f22 

     from @data d 
     cross apply 
     ( 
     select MAX(mdays) as x1 from @rates r1 
     where mdays <= d.ddays 
     ) xt1 
    cross apply 
    (
     select MIN(mdays) as x2 from @rates r1 
     where mdays >= d.ddays 
    ) xt2 
    cross apply 
    (
     select MAX(yield) as y11 from @rates r1 
     where r1.mdays = isnull(x1,x2) 
     and yield <= d.yield 
    ) yt1 
    cross apply 
    (
     select MIN(yield) as y12 from @rates r1 
     where r1.mdays = isnull(x1,x2) 
     and yield >= d.yield 
    ) yt2 
    cross apply 
    (
     select MAX(yield) as y21 from @rates r1 
     where r1.mdays = isnull(x2,x1) 
     and yield <= d.yield 
    ) yt3 
    cross apply 
    (
     select MIN(yield) as y22 from @rates r1 
     where r1.mdays = isnull(x2,x1) 
     and yield >= d.yield 
    ) yt4 
    left outer join @rates r11 on r11.mdays = isnull(x1,x2) and r11.yield = ISNULL(y11,y12) 
    left outer join @rates r12 on r12.mdays = isnull(x1,x2) and r12.yield = ISNULL(y12,y11) 
    left outer join @rates r21 on r21.mdays = isnull(x2,x1) and r21.yield = ISNULL(y21,y22) 
    left outer join @rates r22 on r22.mdays = isnull(x2,x1) and r22.yield = ISNULL(y22,y21) 
) r 
)r2