1

I'm attempting to create a SQL function that generates the polynomials required for the interpolating cubic spline. This wouldn't cause me much trouble in an object based programming language where I can deal with data points sequentially but I'm new to SQL and unfamiliar with the methodology used to solve such problems in SQL.

I have been referencing this article (https://www.periscopedata.com/blog/spline-interpolation-in-sql) which provides a framework for the same task I'm trying to accomplish but I believe its using a different SQL distribution which seems to give the ability to create object like items. The code below represents what I have been able to accomplish thus far and am having trouble wrapping my head around how to proceed.

Select [Option Value] As [T] ,[idx],
  LAG([Option Value],1) OVER (ORDER BY idx) AS [T-1],
  LEAD([Option Value],1) OVER (ORDER BY idx) AS [T+1],
  0 As [y2],
  0 As [u],
  0 AS [p]
  INTO #Calc
  FROM [#Data]
  ORDER BY [idx]


  Select *,
  ([T] - [T-1])/([T+1] - [T-1]) As Sig

  FROM #Calc

The input table will have 11 points with a non uniform distribution across the x axis (ex (-0.2,1), (-0.1,1.2), (-0.03,1.6), (0.05,2)) And I hope to create a function that provides the interpolated values between these points.

Lyric
  • 43
  • 1
  • 5
  • That post is using [Common Table Expressions](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017), which I think is the *objects* you referenced – S3S Aug 14 '19 at 15:15
  • I think you may be correct, is the difference between a CTE and a temp table the fact that you can reference previously defined columns in the definition of a new column? – Lyric Aug 14 '19 at 15:19
  • 1
    There are [a lot of differences](https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table) and in *most* cases you can [use either](https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables). A *gotcha* with CTE's is you must immediately `SELECT` from the CTE or implement another CTE which prevents you from referencing it multiple times later in your code. Since you are new to SQL Server, I'd just use #TempTables for now unless you need a CTE for recursion or something. – S3S Aug 14 '19 at 15:21

0 Answers0