Microsoft SQL和R，存储过程和k均值 [英] Microsoft SQL and R, stored procedure and k-means

问题描述

我在这里是新来的，希望能有所帮助.

I am new here, hope to help and be helped.

但是，我正在使用新的Microsoft Sql Server Management Studio(2016)，它使用的新功能暗示了与R的集成. 首先，我的目标是创建一个存储过程，该存储过程使用x和y列执行K-Means聚类.

However, I am working on the new Microsoft Sql Server Management Studio (2016), using its new features that imply the integration with R. First of all, my goal is to create a stored procedure that perform a K-Means clustering with x and y column.

问题在于我被困在中间，因为我无法拒绝在线文档.

The problem is that I am stuck in the middle, because I am not able to decline the online documentation to my case.

此处是脚本

    CREATE TABLE [dbo].[ModelTable]
    (
    column_name1 varchar(8000)
    )
    ;

    CREATE TABLE [dbo].[ResultTable]
    (
    column_name1 varchar(8000),
    column_name2 varchar(8000),
    column_name3 varchar(8000),
    column_name4 varchar(8000)
    )
    ;

    CREATE PROCEDURE [dbo].[kmean]  

    AS  
    BEGIN  
    DECLARE @inquery nvarchar(max) = N'  
              select  name,x,y FROM [dbtable] 

   '  
   -- then I decide to insert the model in a table: this is similar to  the documentation, but I am not sure it fits well.  

   INSERT INTO [dbo].[ModelTable] 
   EXEC sp_execute_external_script @language = N'R',  
                                   @script = N'  

   ## Here I create model: this is one of the biggest problem, because I tried to create a data frame with the data, but I do not know if here,
   ## in the R code, the data are read in this way. Generally in "pure"  R, I write data.frame(sourcedata$x,sourcedata$y), but here, where   is source of data?
   ## In the documentation it is used ImputDataSet, so maybe I could do: 

    trained_model <- kmeans(data.frame(ImputDataSet$x,ImputDataSet$y),8)  

    -- If everything is ok (doubtfully) I should have the model. And here, the part that I really cannot handle.
    -- I'd like to have a table [ResultTable] with name, variable x, variable y,  and trainedmodel$cluster.

    ',  
                              @input_data_1 = @inquery,  
                              @output_data_1_name = N'trained_model'  
     ;  


    END  
    GO  

    EXEC kmean 

还有很多问题等等，由于这是MSSMS中的一个相当新的功能，因此Internet上没有大量的帮助等. 预先感谢

Well there many problems and so on and, due the fact that this is a quite brand new feature in MSSMS, there are not a great quantity of help etc. in the Internet. Thanks in advance

推荐答案

我们可以尝试以下操作:

We can try the following:

CREATE TABLE #tempData (x float not null, y float not null); 
INSERT INTO #tempData   VALUES (0, 0), (0.1, 0.1), (1, 1), (1.1, 1.1);

CREATE TABLE #output (x float, y float, Cluster int); 

INSERT INTO #output
EXECUTE  sp_execute_external_script
                @language = N'R'
              , @script = N'                
                        trained_model <- kmeans(df[, c("x", "y")], 2)
                        df$cluster <- trained_model$cluster
                        '
              , @input_data_1 = N'SELECT * from #tempData'
              , @output_data_1_name = N'df'
              , @input_data_1_name = N'df';

SELECT *
FROM #output

输出:

x   y   Cluster
0   0   1
0.1 0.1 1
1   1   2
1.1 1.1 2

请注意，我将输入和输出数据指定为df.默认值为InputDataSet和OutputDataSet.

Note that I specified my input- and output data to be df. The defaults are InputDataSet and OutputDataSet.

如果您有更长的R脚本:我建议您在R环境中编写和测试它们，然后将它们保存在一个包中，然后简单地加载并调用它们.

If you have longer R-scripts: I would recommend writing and testing them in your R enviroment, then keep them in a package and simply load and call these instead.

这篇关于Microsoft SQL和R，存储过程和k均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！