0

I have build a polynomial regression in R, one of the variables is a polynomial (of degree 3) of a variable. I use the function poly http://stat.ethz.ch/R-manual/R-devel/library/stats/html/poly.html in R.

Now I want to use my model in Sql Server, I can copy the coefficients of the polynomial regression to Sql Server. In previous versions of my model (without a polynomial), so a linear regression, I could compute the result in T-SQL, but now I have the polynomial input variable that I need to have to compute the result.

I have Sql Server 2014, so no R-services, I want to use only T-SQL to compute the solution. My idea was to code a similar function like poly in R, but then in SQL Server, but I haven't found it yet.

Simple R var with polynomial of degree 3:

polytest<-data.frame(seq(1:20))
colnames(polytest)[1]<-"X"
polytest<-cbind(polytest, poly(polytest$X, degree = 3))
polytest<-polytest[,-c(2,3)]
colnames(polytest)[2]<-"XPOLY3"

The variable and the polynomial of degree 3 (from R), insert statements in T-SQL:

CREATE TABLE [dbo].[polytest](
    [X] [int] NULL,
    [XPOLY3] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (1, -0.43760938779302377)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (2, -0.16122451129216667)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (3, 0.03838678840289679)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (4, 0.17025669679873057)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (5, 0.24341739940189858)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (6, 0.26690108171896487)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (7, 0.24973992925649333)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (8, 0.20096612752104803)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (9, 0.12961186201919273)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (10, 0.044709318257491568)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (11, -0.044709318257491582)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (12, -0.12961186201919278)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (13, -0.20096612752104803)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (14, -0.24973992925649335)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (15, -0.26690108171896487)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (16, -0.24341739940189866)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (17, -0.17025669679873054)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (18, -0.03838678840289686)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (19, 0.16122451129216667)
GO
INSERT [dbo].[polytest] ([X], [XPOLY3]) VALUES (20, 0.43760938779302377)
GO

Is there a possibility to compute this polynomials directly in SQL Server?

gilberke
  • 45
  • 1
  • 7
  • I don't know your exact math, but it is certainly possible to define a user defined function in SQL Server which does whatever you have in mind (within reason). Maybe if you post the code for the R function, or maybe give a doc reference, someone can give an exact answer. – Tim Biegeleisen Jun 03 '18 at 13:21
  • From the R-doc, it should be based on three-term recursion given in Kennedy & Gentle (1980, pp. 343–4). On https://stackoverflow.com/questions/39031172/how-poly-generates-orthogonal-polynomials-how-to-understand-the-coefs-ret there is 2 different ways to calculate the polynomials, but also in R – gilberke Jun 03 '18 at 14:56
  • Do you see any chance to upgrade to a version that includes R/ML Service? Otherwise I would investigate CLR integration. That would allow you to implement the algorithm e.g. in C# preferably with the help of suitable libraries to do some of the heavy lifting. – Ralf Stubner Jun 03 '18 at 20:51
  • I will investigate if an upgrade is possible, if it is, maybe it is the easiest way. Coding this in T-SQL is not that trivial... – gilberke Jun 04 '18 at 08:21

0 Answers0