BILINEARINTERP
Updated: 30 September 2013
Use the scalar BILINEARINTERP function to interpolate on a regular 2-dimensional grid. BILINEARINTERP accepts the 2-dimensional grid, a new x-value, and a new y-value as input, where the x-values are stored in the first column of data and the y-values are stored in the first row of data and the data are contained in the table.
The bilinear interpolation calculation can be thought of as a series of linear interpolation calculations using the closest combination of x- and y-values on the 2-dimensional grid. For example, let's look at the following table which shows the number of days in the future as the x-values, the strike price of an option as the y-values, and which contains the volatility associated with the date and the strike price.
|
|
70
|
80
|
90
|
100
|
110
|
120
|
130
|
|
30
|
0.22
|
0.2
|
0.18
|
0.16
|
0.175
|
0.19
|
0.205
|
|
90
|
0.23
|
0.21
|
0.19
|
0.17
|
0.185
|
0.2
|
0.215
|
|
182
|
0.26
|
0.24
|
0.22
|
0.2
|
0.215
|
0.23
|
0.245
|
|
270
|
0.25
|
0.23
|
0.21
|
0.19
|
0.205
|
0.22
|
0.235
|
|
365
|
0.245
|
0.225
|
0.205
|
0.185
|
0.2
|
0.215
|
0.23
|
If we want to interpolate the volatility for 63 days with a strike price of 87, we can quickly see that the surrounding points would look like this.
|
|
80
|
90
|
|
30
|
0.20
|
0.18
|
|
90
|
0.21
|
0.19
|
It is then a very straightforward to see that the desired result can be thought of as three linear interpolation calculations, as demonstrated by the following SQL.
SELECT wct.INTERP(x,y,87) as BiLinear
FROM (
SELECT 80 as x
,wct.INTERP(x,y,63) as y
FROM (VALUES (30,.20),(90,0.21))n(x,y)
UNION ALL
SELECT 90
,wct.INTERP(x,y,63)
FROM (VALUES (30,.18),(90,0.19))n(x,y)
)i
Producing the result of 0.1915
Syntax
SELECT [wct].[BILINEARINTERP](
<@Grid, nvarchar(max),>
,<@New_x, float,>
,<@New_y, float,>)
Arguments
@Grid
a T-SQL statement, as text, which when executed returns a resultant table containing the x-values in the first column, the y-values in the first row and the values (z) to be interpolated. In other words the table should look something like this:
|
NULL
|
y1
|
y2
|
y3
|
…
|
yn
|
|
x1
|
zx1y1
|
zx1y2
|
zx1y3
|
…
|
zx1yn
|
|
x2
|
zx2y1
|
zx2y2
|
zx2y3
|
…
|
zx2yn
|
|
x3
|
zx3y1
|
zx3y2
|
zx3y3
|
…
|
zx3yn
|
|
…
|
…
|
…
|
…
|
…
|
|
|
xm
|
zxmy1
|
zxmy2
|
zxmy3
|
…
|
zxmyn
|
All values in the resultant table must be of a type float or of a type the implicitly converts to float.
@New_x
the new x-value. @New_x must be a type float or of a type that implicitly converts to float.
@New_y
the new y-value. @New_y must be a type float or of a type that implicitly converts to float.
Return Types
float
Remarks
· If @New_x < MIN(X) or @New_x > MAX(X) then a NULL will be returned.
· If @New_y < MIN(Y) or @New_y > MAX(y) then a NULL will be returned.
· If the @New_x and @New_y are bound by a grid containing a NULL then a NULL will be returned.
Examples
SELECT wct.BILINEARINTERP('SELECT *
FROM (VALUES
(NULL,-45,-44,-43,-42,-41),
(44,67,17,67,7,66),
(45,53,65,34,3,90),
(46,25,40,21,29,24),
(47,29,69,17,63,51),
(48,71,90,90,57,54),
(49,92,15,71,0,10),
(50,33,73,21,19,99)
)n(x,y1,y2,y3,y4,y5)'
,47.25
,-43.5
) as BILINEARINTERP
This produces the following result.
BILINEARINTERP
----------------------
54.75