INTERP2

Updated 2023-10-17 19:26:58.570000

Syntax

SELECT [westclintech].[wct].[INTERP2] (
  <@X, float,>
 ,<@Y, float,>
 ,<@Z, float,>
 ,<@New_x, float,>
 ,<@New_y, float,>);

Description

Use the aggregate function INTERP2 to interpolate on a 2-dimensional grid. INTERP2 expects the grid to be represented in 3rd normal form. INTERP2 is not sensitive to the order of the input.

The 2-dimensional or 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.

SELECT x,

       y,

       z

INTO #v

FROM

(

    VALUES

        (30, 70, 0.22),

        (30, 80, 0.2),

        (30, 90, 0.18),

        (30, 100, 0.16),

        (30, 110, 0.175),

        (30, 120, 0.19),

        (30, 130, 0.205),

        (90, 70, 0.23),

        (90, 80, 0.21),

        (90, 90, 0.19),

        (90, 100, 0.17),

        (90, 110, 0.185),

        (90, 120, 0.2),

        (90, 130, 0.215),

        (182, 70, 0.26),

        (182, 80, 0.24),

        (182, 90, 0.22),

        (182, 100, 0.2),

        (182, 110, 0.215),

        (182, 120, 0.23),

        (182, 130, 0.245),

        (270, 70, 0.25),

        (270, 80, 0.23),

        (270, 90, 0.21),

        (270, 100, 0.19),

        (270, 110, 0.205),

        (270, 120, 0.22),

        (270, 130, 0.235),

        (365, 70, 0.245),

        (365, 80, 0.225),

        (365, 90, 0.205),

        (365, 100, 0.185),

        (365, 110, 0.2),

        (365, 120, 0.215),

        (365, 130, 0.23)

) n (x, y, z);

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 2":"80","column 3":"90"},{"column 1":"30","column 2":"0.20","column 3":"0.18"},{"column 1":"90","column 2":"0.21","column 3":"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(y, z, 87) as INTERP2

FROM

(

    SELECT y,

           wct.INTERP(x, z, 63) as z

    FROM #v

    WHERE y =

    (

        SELECT MAX(y)FROM #v WHERE y <= 87

    )

    GROUP BY y

    UNION

    SELECT y,

           wct.INTERP(x, z, 63)

    FROM #v

    WHERE y =

    (

        SELECT MIN(y)FROM #v WHERE y > 87

    )

    GROUP BY y

) m;

Producing the result of 0.1915

Arguments

@New_x

the new x-value used by INTERP2 to calculate the interpolated z-value. @New_x must be of a type float or of a type that implicitly converts to float.

@Z

The value contained at (@X,@Y). @Z must be of a type float or of a type that implicitly converts to float.

@New_y

the new y-value used by INTERP2 to calculate the interpolated z-value. @New_y must be of a type float or of a type that implicitly converts to float.

@X

The value of the co-ordinate along the x-axis. @X must be of a type float or of a type that implicitly converts to float.

@Y

The value of the co-ordinate along the y-axis. @Y must be of a type float or of a type that implicitly converts to float.

Return Type

float

Remarks

For data not in 3rd normal form, consider using the BILINEARINTERP function.

If @New_x is less than the smallest x-value or greater than the largest x-value in the array, an error will be returned.

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.

@New_x must remain invariant for the GROUP.

@New_y must remain invariant for the GROUP.

Examples

SELECT wct.INTERP2(x, y, z, 63, 87) as INTERP2

FROM

(

    VALUES

        (30, 70, 0.22),

        (30, 80, 0.2),

        (30, 90, 0.18),

        (30, 100, 0.16),

        (30, 110, 0.175),

        (30, 120, 0.19),

        (30, 130, 0.205),

        (90, 70, 0.23),

        (90, 80, 0.21),

        (90, 90, 0.19),

        (90, 100, 0.17),

        (90, 110, 0.185),

        (90, 120, 0.2),

        (90, 130, 0.215),

        (182, 70, 0.26),

        (182, 80, 0.24),

        (182, 90, 0.22),

        (182, 100, 0.2),

        (182, 110, 0.215),

        (182, 120, 0.23),

        (182, 130, 0.245),

        (270, 70, 0.25),

        (270, 80, 0.23),

        (270, 90, 0.21),

        (270, 100, 0.19),

        (270, 110, 0.205),

        (270, 120, 0.22),

        (270, 130, 0.235),

        (365, 70, 0.245),

        (365, 80, 0.225),

        (365, 90, 0.205),

        (365, 100, 0.185),

        (365, 110, 0.2),

        (365, 120, 0.215),

        (365, 130, 0.23)

) n (x, y, z);

This produces the following result.

{"columns":[{"field":"INTERP2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP2":"0.1915"}]}