Login     Register

        Contact Us     Search

Menu

XLeratorDB/math Documentation

SQL Server 2005 cubic spline interpolation


SPLINE_q
 
Updated: 17 August 2010 

Use SPLINE_q to calculate the interpolated value of y given x in the array (x, y). SPLINE_q uses cubic spline interpolation. SPLINE_q 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_q] (
  <@XY_RangeQuery, nvarchar(4000),>
 ,<@New_x, float,>)
Arguments
 
@XY_RangeQuery
the SELECT statement, as text, used to determine the known x- and y-values to be used in this function. Data returned from the @XY_RangeQuery select 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 trend to calculate the y-value. @New_x is an expression of type float or of a type that can be implicitly converted to float.
 
Return Types
float
Remarks
·         If the number of known-y data points is not equal to the number of known-x data points, SPLINE_q will return an error.
·         No GROUP BY is required for this function even though it produces aggregated results.
·         For simpler queries use the SPLINE function.
·         For linear interpolation use the INTERP_q function.
·         If NULL is in the array an error will be returned.
Examples
Interpolation on a table that is in descending order:
create table #xy (
      [x] [float] NULL,
      [y] [float] NULL
)
 
INSERT INTO #xy VALUES(16,65536)
INSERT INTO #xy VALUES(14,16384)
INSERT INTO #xy VALUES(12,4096)
INSERT INTO #xy VALUES(10,1024)
INSERT INTO #xy VALUES(8,256)
INSERT INTO #xy VALUES(6,64)
INSERT INTO #xy VALUES(4,16)
INSERT INTO #xy VALUES(2,4)
INSERT INTO #xy VALUES(0,1)
INSERT INTO #xy VALUES(-2,0.25)
INSERT INTO #xy VALUES(-4,0.0625)
INSERT INTO #xy VALUES(-6,0.015625)
INSERT INTO #xy VALUES(-8,0.00390625)
INSERT INTO #xy VALUES(-10,0.0009765625)
INSERT INTO #xy VALUES(-12,0.000244140625)
INSERT INTO #xy VALUES(-14,0.00006103515625)
INSERT INTO #xy VALUES(-16,0.0000152587890625)
 
SELECT wct.SPLINE_q('Select x, y from #xy order by x', 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_q('Select cast(x as float)
      ,y from XY#'
      ,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.SPLINE_q('Select cast(x as float)
      ,y from #xy'
      ,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_q":
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
·         SPLINE
·         INTERP_q

 



View Topic History
Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service