将回归系数保存到数据库表 [英] Saving regression coefficients to database table

问题描述

有没有快速的方法将系数从GLM保存回数据库？我正在寻找一个INSERT语句。以下代码适用于我，但必须有一个技巧来获取插入语句？

 
  EXEC SP_EXECUTE_EXTERNAL_SCRIPT 
 @LANGUAGE = N'R'
，@ SCRIPT = N'DF<  -  INPUTDATASET 
 ORDER.LOGIT < -LM（TEAMQUANTITY~QTYPLANNED + QTYPOURED + SACKYARDS + LINEARMEASURE + NET_FUML + ACTUALPOUR，DATA = DF）
 DPY< -COEF（ORDER.LOGIT）[" QTYPLANNED"] 
 DRY< -COEF（ORDER.LOGIT）[" QTYPOURED"] 
 DZY<  -  C（DPY，DRY）
 PRINT（DZY）'，
 @ INPUT_DATA_1 = N'SELECT TEAMQUANTITY~QTyPlanned + QtyPoured +萨ckYards + LinearMeasure + Net_Fuml + ActualPour FROM [analysis]。[DataInputForR];'
 GO  
 
来自外部脚本的STDOUT消息：
  QtyPlanned QtyPoured 
 0.06528663 0.07858043  
   
 

---

风险很好。

解决方案

嗨！

您可以使用INSERT INTO EXECUTE .. 。语法，并让你的EXECUTE sp_execute_external_script返回一个OutputDataSet。所以首先创建一个表来存储你的系数：

 CREATE TABLE dbo。 tb_Coeff 
（
 RowID int主键标识，
 QtyPlanned浮点数，
 QtyPoured浮动
）

然后更改你的sp_execute_externa_script是这样的：

 INSERT INTO dbo.tb_Coeff 
 EXEC sp_execute_external_script 
 @LANGUAGE = N'R'
，@ SCRIPT = N'DF<  -  InputDataSet 
 order.logit<  -  lm（teamquantity~qtyplanned + qtypoured + sackyards + linearmeasure + net_fuml + actualpour，data = df）
 dpy< -coef（order.logit）[" QtyPlanned"] 
 dry< -coef（order.logit）[&q uot; QtyPoured"] 
 
 OutputDataSet<  -  data.frame（dpy，dry）'
，@ input_data_1 = N'SELECT TEAMQUANTITY~QTyPlanned + QtyPoured + SackYards + LinearMeasure + Net_Fuml + ActualPour FROM [analysis]。[DataInputForR];'
 GO 

应该这样做。

Niels

Is there a quick way to save the coefficients from a GLM back to a database? I'm looking for an INSERT statement. The following code works for me, but there must be a trick to getting an insert statement in here?

EXEC SP_EXECUTE_EXTERNAL_SCRIPT
@LANGUAGE = N'R'
, @SCRIPT = N'DF <- INPUTDATASET
    ORDER.LOGIT <- LM(TEAMQUANTITY~QTYPLANNED+QTYPOURED+SACKYARDS+LINEARMEASURE+NET_FUML+ACTUALPOUR, DATA = DF)
    DPY <-COEF(ORDER.LOGIT)["QTYPLANNED"]
    DRY <-COEF(ORDER.LOGIT)["QTYPOURED"]
    DZY <- C(DPY,DRY)
    PRINT(DZY)',
@INPUT_DATA_1 = N'SELECT TEAMQUANTITY~QTyPlanned+QtyPoured+SackYards+LinearMeasure+Net_Fuml+ActualPour FROM [analysis].[DataInputForR];'
GO
STDOUT message(s) from external script:
QtyPlanned QtyPoured 
0.06528663 0.07858043 

---

Risk is good.

解决方案

Hi!

You can use the INSERT INTO EXECUTE ... syntax, and have your EXECUTE sp_execute_external_script return an OutputDataSet. So start with creating a table to where you want to store your coefficients:

CREATE TABLE dbo.tb_Coeff
(
  RowID int primary key identity,
  QtyPlanned float,
  QtyPoured float
)

Then change your sp_execute_externa_script to something like so:

INSERT INTO dbo.tb_Coeff
EXEC sp_execute_external_script
@LANGUAGE = N'R'
, @SCRIPT = N'DF <- InputDataSet
    order.logit <- lm(teamquantity~qtyplanned+qtypoured+sackyards+linearmeasure+net_fuml+actualpour, data = df)
    dpy <-coef(order.logit)["QtyPlanned"]
    dry <-coef(order.logit)["QtyPoured"]
	
    OutputDataSet <- data.frame(dpy, dry)'
, @input_data_1 = N'SELECT TEAMQUANTITY~QTyPlanned+QtyPoured+SackYards+LinearMeasure+Net_Fuml+ActualPour FROM [analysis].[DataInputForR];'
GO

That should do it.

Niels

这篇关于将回归系数保存到数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！