POLYINTERP

Updated 2023-10-17 20:19:27.090000

Syntax

SELECT [westclintech].[wct].[POLYINTERP] (
  <@known_x, float,>
 ,<@known_y, float,>
 ,<@new_x, float,>)

Description

Use the aggregate function POLYINTERP to calculate the interpolated value of y given x in the array (x, y). POLYINTERP uses Neville’s method.

Arguments

@known_y

the y-values to be used in the interpolation calculation. @known_y must be of the type float or of a type that implicitly converts to float.

@new_x

the new x-value for which you want INTERP to calculate the y-value.

@known_x

the x-values to be used in the interpolation calculation. @known_x must be of the type float or of a type that implicitly converts to float.

Return Type

float

Remarks

For linear interpolation use the INTERP function.

For cubic spline interpolation use the SPLINE function.

If an x-y pair contains a NULL, then the pair is not used in the interpolation calculation.

POLYTINTERP will accept values for @new_x that are less than the minimum @known_x or greater than the maximum @known_x.

@new_x must remain invariant for the GROUP.

Examples

Given the following x- and y-values, we want to calculate the y-value for x = 0.75.

SET NOCOUNT ON;
 
SELECT wct.POLYINTERP(x,y,0.75) as POLYINTERP
FROM (VALUES
      (0.5,0.479426),
      (1,0.841471),
      (1.5,0.997495),
      (2,0.909297),
      (2.5,0.598472),
      (3,0.14112),
      (3.5,-0.350783),
      (4,-0.756802)
      ) n(x,y);

This produces the following result.

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

In this example, we will interpolate using dates.

SET NOCOUNT ON;
 
SELECT cast(x as datetime) as x,y
INTO #a
FROM (VALUES
      ('2012-Apr-30',0.0028),
      ('2013-Apr-30',0.0056),
      ('2014-Apr-30',0.0085),
      ('2016-Apr-30',0.0164),
      ('2018-Apr-30',0.0235),
      ('2021-Apr-30',0.0299),
      ('2031-Apr-30',0.0382),
      ('2041-Apr-30',0.0406)
      ) m(x, y);
     
SELECT wct.POLYINTERP(cast(x as float), y, cast(Cast('2012-Oct-31' as datetime) as float)) as POLYINTERP
from #a;
 
DROP TABLE #a;

This produces the following result.

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

We could have used the YEARFRAC function from XLeratorDB/financial, which makes the SQL somewhat simpler, but also permits interpolation using different interest bases. In this example, we will use YEARFRAC to interpolate using the BOND interest basis.

SET NOCOUNT ON;

SELECT cast(x as datetime) as x,

       y

INTO #a

FROM

(

    VALUES

        ('2012-Apr-30', 0.0028),

        ('2013-Apr-30', 0.0056),

        ('2014-Apr-30', 0.0085),

        ('2016-Apr-30', 0.0164),

        ('2018-Apr-30', 0.0235),

        ('2021-Apr-30', 0.0299),

        ('2031-Apr-30', 0.0382),

        ('2041-Apr-30', 0.0406)

) m (x, y);

SELECT wct.POLYINTERP(

                         westclintech.wct.YEARFRAC('2011-Apr-30', x, 0),

                         y,

                         westclintech.wct.YEARFRAC('2011-Apr-30', '2012-Oct-31', 

                                   0)

                     ) as POLYINTERP

FROM #a;

This produces the following result.

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

In this example, we provide a @new_x value that is less than the minimum @known_x value.

SET NOCOUNT ON;
 
SELECT wct.POLYINTERP(x, y, -3) as POLYINTERP
FROM (VALUES
      (-2,-2),
      (-1,-0.5),
      (0,1),
      (1,2.5),
      (2,4),
      (3,5.5)
      ) n(x, y);

This produces the following result.

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

In this example, we provide a @new_x value that is greater than the maximum @known_x value.

SET NOCOUNT ON;
 
SELECT wct.POLYINTERP(x, y, 4) as POLYINTERP
FROM (VALUES
      (-2,-2),
      (-1,-0.5),
      (0,1),
      (1,2.5),
      (2,4),
      (3,5.5)
      ) n(x, y);

This produces the following result.

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

In this example, one of the @known_y values is NULL.

SET NOCOUNT ON;
 
SELECT wct.POLYINTERP(x, y, 1.5) as POLYINTERP
FROM (VALUES
      (-2,-2),
      (-1,-0.5),
      (0,1),
      (1,NULL),
      (2,4),
      (3,5.5)
      ) n(x, y);

This produces the following result.

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

In this example, one of the @known_x values is NULL.

SET NOCOUNT ON;
 
SELECT wct.POLYINTERP(x, y, 1.5) as POLYINTERP
FROM (VALUES
      (-2,-2),
      (-1,-0.5),
      (0,1),
      (1,2.5),
      (NULL,4),
      (3,5.5)
      ) n(x, y);

This produces the following result.

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