INTERP
Updated 2023-10-17 19:04:11.487000
Syntax
SELECT [westclintech].[wct].[INTERP] (<@known_x, float,>
<@known_y, float,>
<@new_x, float,>)
Description
Use the aggregate function INTERP to calculate the straight-line interpolated value of y given x in the array (x, y). INTERP is not sensitive to the order of the array (x, y).
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 cubic spline interpolation use the SPLINE function.
For polynomial interpolation, use the POLYINTERP 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 an x-y pair contains a NULL, then the pair is not used in the interpolation calculation.
If @new_x = @known_x then @known_y is returned.
@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.INTERP(x, y, 0.75) as INTERP
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":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"0.6604485"}]}
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.INTERP(cast(x as float), y, cast(Cast('2012-Oct-31' as datetime) as
float)) as INTERP
from #a;
DROP TABLE #a;
This produces the following result.
INTERP
----------------------
0.00421150684931507
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.INTERP(
westclintech.wct.YEARFRAC('2011-Apr-30', x, 0),
y,
westclintech.wct.YEARFRAC('2011-Apr-30', '2012-Oct-31', 0)
) as INTERP
from #a;
DROP TABLE #a;
This produces the following result.
{"columns":[{"field":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"0.0042"}]}
In this example, we provide a @new_x value that is less than the minimum @known_x value.
SET NOCOUNT ON;
SELECT wct.INTERP(x, y, -3) as INTERP
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":"INTERP"}],"rows":[{"INTERP":"NULL"}]}
In this example, we provide a @new_x value that is greater than the maximum @known_x value.
SET NOCOUNT ON;
SELECT wct.INTERP(x, y, 4) as INTERP
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":"INTERP"}],"rows":[{"INTERP":"NULL"}]}
In this example, one of the @known_y values is NULL.
SET NOCOUNT ON;
SELECT wct.INTERP(x, y, 1.5) as INTERP
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":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"3.25"}]}
In this example, one the @known_x values is NULL.
SET NOCOUNT ON;
SELECT wct.INTERP(x, y, 1.5) as INTERP
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":"INTERP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INTERP":"3.25"}]}