SQL Server POLYCOEF Function

Updated 2024-03-06 21:30:06.110000

Description

Use the aggregate function POLYCOEF for obtaining a specific coefficient value from an approximating polynomial for a set of x- and y-values. The coefficients are of a polynomial p(x) of degree n that fits the x- and y-values supplied to the function. The function calculates 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 [westclintech].[wct].[POLYCOEF] (
  <@known_x, float,>
 ,<@known_y, float,>
 ,<@degree, smallint,>
 ,<@n, smallint,>)

Arguments

@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.

@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.

@degree

an integer specifying the degree of the polynomial. @degree must be of the type smallint or of a type that implicitly converts to smallint.

@n

the coefficient value to be returned. @n must be of the type smallint or of a type that implicitly converts to smallint.

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.

@n must be less than or equal to the number of x-y rows in the GROUP

@degree must be less than or equal to the number of x-y rows in the GROUP

You can also 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 select the first coefficient.

SET NOCOUNT ON;
SELECT wct.POLYCOEF(n.x, n.y, 6, 1) as POLYCOEF
FROM
(
    SELECT SeriesValue as x,
           westclintech.wct.ERF(SeriesValue) as y
    FROM wct.SeriesFloat(0, 2.5, 0.1, NULL, NULL)
) n;

This produces the following result.

This is exactly the same result as we would get from the POLYFIT_q function if we had entered the following SQL:

SET NOCOUNT ON;
SELECT coe_val
FROM wct.POLYFIT_q(
                      'SELECT SeriesValue as x
      ,westclintech.wct.ERF(SeriesValue) as y
      FROM wct.SeriesFloat(0,2.5,0.1,NULL,NULL)',
                      6
                  )
where coe_num = 1;

This produces the following result.

If we wanted to obtain all the coefficients in the 6 degree approximating polynomial, we could use the following SQL.

SET NOCOUNT ON;
SELECT a.seriesvalue,
       wct.POLYCOEF(n.x, n.y, 6, a.SeriesValue) as POLYCOEF
FROM
(
    SELECT SeriesValue as x,
           westclintech.wct.ERF(SeriesValue) as y
    FROM wct.SeriesFloat(0, 2.5, 0.1, NULL, NULL)
) n ,
wct.SeriesINT(1, 7, NULL, NULL, NULL) a
GROUP BY a.SeriesValue
ORDER BY 1;

This produces the following result.

This is identical to the result we would have obtained using the POLYFIT_q table-valued function.

SET NOCOUNT ON;
SELECT *
FROM wct.POLYFIT_q(
                      'SELECT SeriesValue as x
      ,westclintech.wct.ERF(SeriesValue) as y
      FROM wct.SeriesFloat(0,2.5,0.1,NULL,NULL)',
                      6
                  )
order by coe_num;

This produces the following result.