SQL Server POLYVAL Function

Updated 2024-03-06 21:33:04.597000

Description

Use the aggregate function POLYVAL for calculating a new y-value given a new x-value using the coefficients of a polynomial p(x) of degree n that fits the x- and y-values supplied to the function. The y-value is calculated using n+1 polynomial coefficients in descending powers:

y=p_1x^n+p_2x^{n-1}+\dots+p_nx^1+p_{n+1}x^0

Syntax

SELECT [wct].[POLYVAL] (<@known_x, float,>
<@known_y, float,>
<@degree, smallint,>
<@new_x, float,>)

Arguments

@known_y

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

@degree

an integer specifying the degree of the polynomial.

@new_x

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

Return Type

float

Remarks

The x- and y-values are passed to the function as pairs.

If x is NULL or y is NULL, the pair is not used in the calculation.

Use the POLYFIT or POLYFIT_q functions to get the coefficients.

@degree must remain invariant for the GROUP.

@new_x must remain invariant for the GROUP.

Examples

In this example, we will use the SeriesFloat function to generate a series of x-values equally spaced in the interval [0, 2.5] and then evaluate the error function, ERF, at those points. We will specify an approximating polynomial of 6 degrees. We will then generate values at the midpoint for each interval using the approximating polynomial.

SET NOCOUNT ON;
SELECT SeriesValue as x,
       westclintech.wct.ERF(SeriesValue) as y
INTO #erf
FROM wct.SeriesFloat(0, 2.5, 0.1, NULL, NULL);
SELECT a.seriesvalue as x,
       wct.POLYVAL(e.x, e.y, 6, seriesvalue) as POLYVAL
FROM wct.SERIESFLOAT(0.05, 2.5, 0.1, NULL, NULL) a ,
     #erf E
GROUP BY seriesvalue;
DROP TABLE #erf;

This produces the following result.

Since we know that the y-values are actually erf(x), in this example we will compare the values calculated using the approximating polynomial and erf(x).

SET NOCOUNT ON;
SELECT SeriesValue as x,
       westclintech.wct.ERF(SeriesValue) as y
INTO #erf
FROM wct.SeriesFloat(0, 2.5, 0.1, NULL, NULL);
SELECT x,
       POLYVAL,
       [ERF(x)],
       POLYVAL - [ERF(x)] as [DIFFERENCE]
FROM
(
    SELECT a.seriesvalue as x,
           wct.POLYVAL(e.x, e.y, 6, a.seriesvalue) as POLYVAL,
           westclintech.wct.ERF(a.SeriesValue) as [ERF(x)]
    FROM wct.SERIESFLOAT(0.05, 2.5, 0.1, NULL, NULL) a ,
         #erf E
    GROUP BY seriesvalue
) m;
DROP TABLE #erf;

This produces the following result.

It looks like the approximating polynomial is accurate to about 3 or 4 decimal places. In fact if we graphed the results, they would look like this:

We can see that in this range, [0, 2.5] the fit between the y-values and the f-values (ERF(x) in this case) is quite good. Since POLYVAL creates an approximating polynomial, it is not bound by minima and maxima of x-y pairs. In this example, we will graph the results this SQL, which will using the approximating polynomial from the [0, 2.5] interval on the interval [0, 6].

SET NOCOUNT ON;
 
SELECT SeriesValue as x
,westclintech.wct.ERF(SeriesValue) as y
INTO #erf
FROM wct.SeriesFloat(0,2.5,0.1,NULL,NULL);
 
SELECT a.seriesvalue as x
,wct.POLYVAL(e.x, e.y, 6, seriesvalue) as POLYVAL
FROM wct.SERIESFLOAT(0.0,6.0,0.1,NULL,NULL) a
,#erf E
GROUP BY seriesvalue;
 
DROP TABLE #erf;

This produces the following graph.

As you can see, the polynomial approximation quickly diverges from the function in the region above 2.5. Let's look at how POLYVAL works with dates. In this example we will look at some interest rate values over a time-horizon and calculate the 3^rd degree polynomial approximation. The x-values are dates and the y-values are the decimal values of the interest rate (.01 = 1%).

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.POLYVAL(cast(x as float), y, 3, cast(Cast('2012-Oct-31' as datetime) 
          as float)) as POLYVAL
from #a;
DROP TABLE #a;

This produces the following result.

In this example we will use POLYVAL, the SeriesInt function and the EOMONTH function from XLeratorDB/financial to calculate the rates for each year from 1 through 30 out to 4 decimal places.

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 convert(varchar, wct.EOMONTH('04/30/2011', SeriesValue), 106) as [MATURITY 
          DATE],
       ROUND(wct.POLYVAL(cast(x as float), y, 3, cast(wct.EOMONTH('04/30/2011', 
                 SeriesValue) as float)), 4) as [INTEREST RATE]
from #a,
     wct.SeriesInt(12, 360, 12, NULL, NULL)
GROUP BY wct.EOMONTH('04/30/2011', SeriesValue);
DROP TABLE #a;

This produces the following result.