Logo
Getting started

POLYVAL

Updated 2024-03-06 21:33:04.597000

Syntax

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

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

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.

@known_x

the x-values to be used in the calculation. @known_x 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 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.

{"columns":[{"field":"x","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"POLYVAL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"x":"0.05","POLYVAL":"0.0560409087420763"},{"x":"0.15","POLYVAL":"0.167414870029564"},{"x":"0.25","POLYVAL":"0.276183548384518"},{"x":"0.35","POLYVAL":"0.379669434495585"},{"x":"0.45","POLYVAL":"0.475932241043269"},{"x":"0.55","POLYVAL":"0.563669129052707"},{"x":"0.65","POLYVAL":"0.642120996194105"},{"x":"0.75","POLYVAL":"0.710984827030842"},{"x":"0.85","POLYVAL":"0.770332105215247"},{"x":"0.95","POLYVAL":"0.82053328763204"},{"x":"1.05","POLYVAL":"0.862188340489436"},{"x":"1.15","POLYVAL":"0.896063337357925"},{"x":"1.25","POLYVAL":"0.923033119156717"},{"x":"1.35","POLYVAL":"0.944030016087848"},{"x":"1.45","POLYVAL":"0.959998631517963"},{"x":"1.55","POLYVAL":"0.971856687807761"},{"x":"1.65","POLYVAL":"0.980461934089109"},{"x":"1.75","POLYVAL":"0.986585115989822"},{"x":"1.85","POLYVAL":"0.990889007306113"},{"x":"1.95","POLYVAL":"0.993913503622709"},{"x":"2.05","POLYVAL":"0.996066777880637"},{"x":"2.15","POLYVAL":"0.997622497892672"},{"x":"2.25","POLYVAL":"0.998723105806456"},{"x":"2.35","POLYVAL":"0.999389159515293"},{"x":"2.45","POLYVAL":"0.999534736016589"}]}

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.

{"columns":[{"field":"x","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"POLYVAL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ERF(x)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIFFERENCE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"x":"0.05","POLYVAL":"0.0560409087420763","ERF(x)":"0.0563719777970165","DIFFERENCE":"-0.00033106905494016"},{"x":"0.15","POLYVAL":"0.167414870029564","ERF(x)":"0.167995971427363","DIFFERENCE":"-0.000581101397799128"},{"x":"0.25","POLYVAL":"0.276183548384518","ERF(x)":"0.276326390168236","DIFFERENCE":"-0.000142841783717984"},{"x":"0.35","POLYVAL":"0.379669434495585","ERF(x)":"0.379382053562309","DIFFERENCE":"0.00028738093327535"},{"x":"0.45","POLYVAL":"0.475932241043269","ERF(x)":"0.475481719786923","DIFFERENCE":"0.000450521256346315"},{"x":"0.55","POLYVAL":"0.563669129052707","ERF(x)":"0.563323366325108","DIFFERENCE":"0.000345762727599452"},{"x":"0.65","POLYVAL":"0.642120996194105","ERF(x)":"0.642029327355671","DIFFERENCE":"9.16688384339226E-05"},{"x":"0.75","POLYVAL":"0.710984827030842","ERF(x)":"0.711155633653513","DIFFERENCE":"-0.000170806622671549"},{"x":"0.85","POLYVAL":"0.770332105215247","ERF(x)":"0.770668057608351","DIFFERENCE":"-0.000335952393103467"},{"x":"0.95","POLYVAL":"0.82053328763204","ERF(x)":"0.820890807273276","DIFFERENCE":"-0.000357519641236315"},{"x":"1.05","POLYVAL":"0.862188340489436","ERF(x)":"0.862436106090095","DIFFERENCE":"-0.000247765600658978"},{"x":"1.15","POLYVAL":"0.896063337357925","ERF(x)":"0.896123842936913","DIFFERENCE":"-6.05055789882902E-05"},{"x":"1.25","POLYVAL":"0.923033119156717","ERF(x)":"0.922900128256456","DIFFERENCE":"0.000132990900260643"},{"x":"1.35","POLYVAL":"0.944030016087848","ERF(x)":"0.943762196122722","DIFFERENCE":"0.000267819965126148"},{"x":"1.45","POLYVAL":"0.959998631517963","ERF(x)":"0.959695025637457","DIFFERENCE":"0.000303605880506042"},{"x":"1.55","POLYVAL":"0.971856687807761","ERF(x)":"0.97162273326201","DIFFERENCE":"0.000233954545751147"},{"x":"1.65","POLYVAL":"0.980461934089109","ERF(x)":"0.980375585023358","DIFFERENCE":"8.63490657508903E-05"},{"x":"1.75","POLYVAL":"0.986585115989822","ERF(x)":"0.98667167121918","DIFFERENCE":"-8.65552293580762E-05"},{"x":"1.85","POLYVAL":"0.990889007306113","ERF(x)":"0.991111030056084","DIFFERENCE":"-0.000222022749971074"},{"x":"1.95","POLYVAL":"0.993913503622709","ERF(x)":"0.994179333592187","DIFFERENCE":"-0.000265829969478215"},{"x":"2.05","POLYVAL":"0.996066777880637","ERF(x)":"0.996258096044454","DIFFERENCE":"-0.000191318163817344"},{"x":"2.15","POLYVAL":"0.997622497892672","ERF(x)":"0.997638607037322","DIFFERENCE":"-1.61091446494455E-05"},{"x":"2.25","POLYVAL":"0.998723105806456","ERF(x)":"0.998537283413317","DIFFERENCE":"0.000185822393138357"},{"x":"2.35","POLYVAL":"0.999389159515293","ERF(x)":"0.999110732967865","DIFFERENCE":"0.000278426547427824"},{"x":"2.45","POLYVAL":"0.999534736016589","ERF(x)":"0.999469419887746","DIFFERENCE":"6.53161288429738E-05"}]}

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:

http://westclintech.com/Portals/0/images/doc_math_POLYVAL_img2.jpg

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.

http://westclintech.com/Portals/0/images/doc_math_POLYVAL_img3.jpg

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 3rd 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.

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

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.

{"columns":[{"field":"MATURITY DATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"INTEREST RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MATURITY DATE":"30 Apr 2012","INTEREST RATE":"0.0015"},{"MATURITY DATE":"30 Apr 2013","INTEREST RATE":"0.0059"},{"MATURITY DATE":"30 Apr 2014","INTEREST RATE":"0.0099"},{"MATURITY DATE":"30 Apr 2015","INTEREST RATE":"0.0136"},{"MATURITY DATE":"30 Apr 2016","INTEREST RATE":"0.017"},{"MATURITY DATE":"30 Apr 2017","INTEREST RATE":"0.02"},{"MATURITY DATE":"30 Apr 2018","INTEREST RATE":"0.0228"},{"MATURITY DATE":"30 Apr 2019","INTEREST RATE":"0.0252"},{"MATURITY DATE":"30 Apr 2020","INTEREST RATE":"0.0274"},{"MATURITY DATE":"30 Apr 2021","INTEREST RATE":"0.0293"},{"MATURITY DATE":"30 Apr 2022","INTEREST RATE":"0.031"},{"MATURITY DATE":"30 Apr 2023","INTEREST RATE":"0.0325"},{"MATURITY DATE":"30 Apr 2024","INTEREST RATE":"0.0338"},{"MATURITY DATE":"30 Apr 2025","INTEREST RATE":"0.0349"},{"MATURITY DATE":"30 Apr 2026","INTEREST RATE":"0.0358"},{"MATURITY DATE":"30 Apr 2027","INTEREST RATE":"0.0366"},{"MATURITY DATE":"30 Apr 2028","INTEREST RATE":"0.0373"},{"MATURITY DATE":"30 Apr 2029","INTEREST RATE":"0.0378"},{"MATURITY DATE":"30 Apr 2030","INTEREST RATE":"0.0382"},{"MATURITY DATE":"30 Apr 2031","INTEREST RATE":"0.0385"},{"MATURITY DATE":"30 Apr 2032","INTEREST RATE":"0.0388"},{"MATURITY DATE":"30 Apr 2033","INTEREST RATE":"0.039"},{"MATURITY DATE":"30 Apr 2034","INTEREST RATE":"0.0391"},{"MATURITY DATE":"30 Apr 2035","INTEREST RATE":"0.0393"},{"MATURITY DATE":"30 Apr 2036","INTEREST RATE":"0.0394"},{"MATURITY DATE":"30 Apr 2037","INTEREST RATE":"0.0395"},{"MATURITY DATE":"30 Apr 2038","INTEREST RATE":"0.0397"},{"MATURITY DATE":"30 Apr 2039","INTEREST RATE":"0.0399"},{"MATURITY DATE":"30 Apr 2040","INTEREST RATE":"0.0402"},{"MATURITY DATE":"30 Apr 2041","INTEREST RATE":"0.0405"}]}