LINEAR

Updated 2023-10-17 19:17:40.300000

Syntax

SELECT [westclintech].[wct].[LINEAR] (
  <@Known_x, float,>
 ,<@Known_y, float,>
 ,<@New_x, float,>
 ,<@Extrapolate, bit,>)

Description

Use the aggregate function LINEAR to calculate the straight-line interpolated value of y given x in the array (x, y). LINEAR is not sensitive to the order of the array (x, y). LINEAR supports extrapolation when the new x-value is outside the bounds of the supplied x-values or permits you to return the y-value associated with maximum x-value or the y-value associated with the minimum x-value when the new x-value is greater than the maximum x-value or less than the minimum x-value.

Arguments

@Extrapolate

a bit value that identifies whether to return the boundary value or the extrapolated value when @New_x is outside the boundary. @Extrapolate must be of a type bit or of a type that implicitly converts to bit.

@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 LINEAR to calculate the y-value. @New_x must be of the type float or of a type that implicitly converts to float.

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

For monotonic spline interpolation use the MONOSPLINE function.

For bilinear interpolation use the INTERP2 function.

If @New_x is less than the smallest x-value and @Extrapolate is TRUE, then an extrapolated value is returned, otherwise the y-value paired with the smallest x-value is returned.

If @New_x is greater than the largest x-value and @Extrapolate is TRUE, then an extrapolated value is returned, otherwise the y-value paired with the largest x-value is returned.

If @Extrapolate is NULL then @Extrapolate is equal to FALSE.

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.

@Extrapolate must remain invariant for the GROUP.

The number of rows passed into the function is less than 2 then a NULL is returned.

If @New_x is NULL then a NULL is returned.

Examples

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

SELECT wct.LINEAR(x, y, 0.75, NULL) as LINEAR

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":"LINEAR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LINEAR":"0.6604485"}]}

In this example we calculate the interpolated values with x-values below the minimum and above the maximum with @Extrapolate set to FALSE.

;WITH mycte

AS (SELECT *

    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) )

SELECT m.x,

       wct.LINEAR(mycte.x, mycte.y, m.x, 'False') as LINEAR

FROM

(

    VALUES

        (-0.5),

        (0),

        (4.5),

        (5.0)

) m (x) ,

mycte

GROUP BY m.x;

This produces the following result.

{"columns":[{"field":"x","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"LINEAR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"x":"-0.5","LINEAR":"0.479426"},{"x":"0","LINEAR":"0.479426"},{"x":"4.5","LINEAR":"-0.756802"},{"x":"5","LINEAR":"-0.756802"}]}

Using the same data, but setting @Extrapolate to TRUE.

;WITH mycte

AS (SELECT *

    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) )

SELECT m.x,

       wct.LINEAR(mycte.x, mycte.y, m.x, 'True') as LINEAR

FROM

(

    VALUES

        (-0.5),

        (0),

        (4.5),

        (5.0)

) m (x) ,

mycte

GROUP BY m.x;

This produces the following result.

{"columns":[{"field":"x","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"LINEAR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"x":"-0.5","LINEAR":"-0.244664"},{"x":"0","LINEAR":"0.117381"},{"x":"4.5","LINEAR":"-1.162821"},{"x":"5","LINEAR":"-1.56884"}]}

In this example, we will interpolate using dates.

SELECT wct.LINEAR(CAST(CAST(x as datetime) as float), y, CAST(CAST('2012-Oct-31' 

          as datetime) as float), NULL) as LINEAR

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);

This produces the following result.

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