SPLINE
Updated: 17 August 2010
Use SPLINE to calculate the interpolated value of y given x in the array (x, y). SPLINE uses cubic spline interpolation. SPLINE is sensitive to the order of the array (x, y); to provide meaningful results, data should be in x-value order, ascending.
Syntax
SELECT [westclintech].[wct].[SPLINE] (
<@TableName, nvarchar(4000),>
,<@Known_x_ColumnName, nvarchar(4000),>
,<@Known_y_ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@New_x, float,>)
Arguments
@TableName
the name, as text, of the table or view that contains the known x- and y-values to be used in the SPLINE calculation.
@Known_x_ColumnName
the name, as text, of the column in the table or view specified by @TableName that contains the x-values to be used in the SPLINE calculation. Data returned from the @Known_x_ColumnName must be of the type float or of a type that implicitly converts to float.
@Known_y_ColumnName
the name, as text, of the column in the table or view specified by @TableName that contains the y-values to be used in the SPLINE calculation. Data returned from the @Known_y_ColumnName must be of the type float or of a type that implicitly converts to float.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@New_x
the new x-value for which you want SPLINE to calculate the y-value.
Return Types
float
Remarks
· If the number of known-y data points is not equal to the number of known-x data points, SPLINE will return an error.
· No GROUP BY is required for this function even though it produces aggregated results.
· For more complex queries or queries where you need to order the array, use the SPLINE_q function.
· For linear interpolation use the INTERP function.
· If NULL is in the array an error will be returned.
Examples
Interpolation on a table that is in ascending order:
create table #xy (
[x] [float] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES(-16,0.0000152587890625)
INSERT INTO #xy VALUES(-14,0.00006103515625)
INSERT INTO #xy VALUES(-12,0.000244140625)
INSERT INTO #xy VALUES(-10,0.0009765625)
INSERT INTO #xy VALUES(-8,0.00390625)
INSERT INTO #xy VALUES(-6,0.015625)
INSERT INTO #xy VALUES(-4,0.0625)
INSERT INTO #xy VALUES(-2,0.25)
INSERT INTO #xy VALUES(0,1)
INSERT INTO #xy VALUES(2,4)
INSERT INTO #xy VALUES(4,16)
INSERT INTO #xy VALUES(6,64)
INSERT INTO #xy VALUES(8,256)
INSERT INTO #xy VALUES(10,1024)
INSERT INTO #xy VALUES(12,4096)
INSERT INTO #xy VALUES(14,16384)
INSERT INTO #xy VALUES(16,65536)
SELECT wct.SPLINE('#xy','x','y','',NULL, 2.5) as SPLINE
This produces the following result
SPLINE
----------------------
6.58809772701423
(1 row(s) affected)
Interpolation using dates
create table #xy (
[x] [datetime] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES('09/30/2009',0.00041)
INSERT INTO #xy VALUES('11/30/2009',0.00096)
INSERT INTO #xy VALUES('03/31/2010',0.00198)
INSERT INTO #xy VALUES('09/30/2010',0.00362)
INSERT INTO #xy VALUES('09/30/2011',0.00984)
INSERT INTO #xy VALUES('09/30/2012',0.0155)
INSERT INTO #xy VALUES('09/30/2014',0.02449)
INSERT INTO #xy VALUES('09/30/2019',0.03476)
INSERT INTO #xy VALUES('09/30/2029',0.04234)
SELECT wct.SPLINE(
'#xy'
,'cast(x as float)'
,'y'
,''
,NULL
, cast(cast('08/15/2010' as datetime) as float)
) as SPLINE
This produces the following result
SPLINE
----------------------
0.00312424514164717
(1 row(s) affected)
Interpolation with invalid data in the array
create table #xy (
[x] [datetime] NULL,
[y] [float] NULL
)
INSERT INTO #xy VALUES('09/30/2009',0.00041)
INSERT INTO #xy VALUES('11/30/2009',0.00096)
INSERT INTO #xy VALUES('03/31/2010',0.00198)
INSERT INTO #xy VALUES('09/30/2010',NULL)
INSERT INTO #xy VALUES('09/30/2011',0.00984)
INSERT INTO #xy VALUES('09/30/2012',0.0155)
INSERT INTO #xy VALUES('09/30/2014',0.02449)
INSERT INTO #xy VALUES('09/30/2019',0.03476)
INSERT INTO #xy VALUES('09/30/2029',0.04234)
SELECT wct.INTERP(
'#xy'
,'cast(x as float)'
,'y'
,''
,NULL
, cast(cast('08/15/2010' as datetime) as float)
) as SPLINE
This produces the following result
SPLINE
----------------------
Msg 6522, Level 16, State 1, Line 15
A .NET Framework error occurred during execution of user-defined routine or aggregate "SPLINE":
XLeratorDB_math.Core.Exceptions+Function_Parameter_Exception:
[Product version 1.2 Build: 0927.121]
Invalid 'Known_x' parameter value passed to function 'SPLINE'
*** Error information:
Cannot calculate SPLINE from input data.
Known_x(0) 40084 Known_y(0) 0.00041
Known_x(1) 40145 Known_y(1) 0.00096
Known_x(2) 40266 Known_y(2) 0.00198
Known_x(3) 40449 Known_y(3) [NULL]
Known_x(4) 40814 Known_y(4) 0.00984
Known_x(5) 41180 Known_y(5) 0.0155
Known_x(6) 41910 Known_y(6) 0.02449
Known_x(7) 43736 Known_y(7) 0.03476
Known_x(8) 47389 Known_y(8) 0.04234
See Also