GLM适合(逻辑回归)到SQL [英] GLM fit (logistic regression) to SQL
问题描述
我们经常直接在数据库中为简单模型(例如线性或对数回归)评分数据.正确地将所有系数从R传递到SQL总是有些棘手.我以为可以为glm结果做一些R到SQL的翻译.对于数字变量,这非常简单:
We frequently score data in database directly for simple models like linear or logisitc regression. It is always a little bit tricky to transfer all coefficients from R to SQL correctly. I thought I can make some R to SQL translation for glm result. For numeric variables this is pretty straightforward:
library(rpart)
fit <- glm(Kyphosis ~ ., data = kyphosis, family = binomial())
coefs <- fit$coef[2:length(fit$coef)]
expr <- paste0('1/(1 + exp(-(',fit$coef[1], '+', paste0('(',
coefs, '*', names(coefs), ')', collapse = '+'),')))')
print(expr)
a <- with(kyphosis, eval(parse(text = expr)))
b <- predict(fit, kyphosis, type = 'response')
names(b) <- NULL
all.equal(a, b)
生成的expr
是:1/(1 + exp(-(-2.03693352129613+(0.0109304821420485*Age)+(0.410601186932733*Number)+(-0.206510049753697*Start))))
.
是否有一种方法可以使factor
变量起作用?我想将因素放在case ... when ... then ... end
子句中.假设我们有以下模型:
Is there a way how to make this work for factor
variables? I would like to put factors in case ... when ... then ... end
clause. Suppose we have the following model:
kyphosis$factor_variable <- rep(LETTERS[1:5],20)[1:81]
fit <- glm(Kyphosis ~ ., data = kyphosis, family = binomial())
我正在浏览fit
的结构,但是看不到任何有用的东西.是解析names(fit$coef)
的唯一选项吗?
I am browsing through structure of fit
, but do not see anything useful. Is the only option to parse names(fit$coef)
?
推荐答案
希望此功能有所帮助.今天就把它写好了,还没有测试所有角落-所以要小心使用:)
Hope this function helps. Wrote it today and haven't tested all corners - so use with care :)
glm_to_sql <- function(glmmodel) {
xlev <- data.frame(unlist(glmmodel$xlevels))
xlev$xlevrowname <- rownames(xlev)
rownames(xlev) <- NULL
colnames(xlev)[1] <- "xlevel"
if (nrow(xlev)==0){xlev <- data.frame(xlevrowname=character(0), xlevel=character(0), stringsAsFactors=F)}
modcoeffs <- data.frame(unlist(glmmodel$coefficients))
modcoeffs$coeffname <- rownames(modcoeffs)
rownames(modcoeffs) <- NULL
colnames(modcoeffs)[1] <- "coeffvalue"
coeffmatrix <- sqldf("select a.*,b.*,'' as sqlstr,
substr(coeffname,1,instr(coeffname, xlevel)-1) as varname
from modcoeffs a left join xlev b on coeffname like '%' || xlevel and xlevrowname like substr(coeffname,1,instr(coeffname, xlevel)-1) || '%'")
for (i in 1:nrow(coeffmatrix)) {
if(coeffmatrix$coeffname[i] == "(Intercept)")
{
coeffmatrix$sqlstr[i] <- coeffmatrix$coeffvalue[i]
} else if (is.na(coeffmatrix$xlevel[i]) ) {
coeffmatrix$sqlstr[i] <- paste("(",coeffmatrix$coeffvalue[i],"*",coeffmatrix$coeffname[i],")")
} else {
coeffmatrix$sqlstr[i] <- paste("(case when ",coeffmatrix$varname[i],"='",coeffmatrix$xlevel[i], "' THEN ",coeffmatrix$coeffvalue[i]," ELSE 0 END)",sep="")
}
if (i==1){x.sql0 <- coeffmatrix$sqlstr[i]} else {x.sql0 <- paste(x.sql0,"+",coeffmatrix$sqlstr[i])}
}
if (glmmodel$family$link == "logit") {
x.sql <- paste("1/(1 + exp(-(",x.sql0,")))")
} else if (glmmodel$family$link == "identity") {
x.sql <- x.sql0
}
return(x.sql)
}
这篇关于GLM适合(逻辑回归)到SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!