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