使用R RODBC参数化SQL查询 [英] Parameterize SQL Queries using R RODBC

问题描述

我有一个相当简单的问题.

I have a rather simple question.

每天，我使用RODBC软件包在R中执行数据分析.我使用SQL将其连接到我们的数据仓库，然后将其移至R环境中

On a daily basis, I perform data analysis in R using the RODBC package. I connect it to our data warehouse using SQL and move it into the R environment

dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')

degrees <- sqlQuery(dbhandle, "select Inst, ID, DegreeDate, Degree from DEGREE where FY = ('2015') group by Inst, ID, DegreeDate, Degree order by Inst, ID, DegreeDate, Degree", as.is=TRUE)

您知道如何在MS Access中弹出一个窗口，询问您例如什么风云人物?您在2015年投入，您将获得该会计年度的所有费用.

You know how in MS Access, you can have a window pop up that asks you what FY for example? You put in 2015 and you'll get all the degress from that fiscal year.

在R中有什么方法可以做到吗?我在Stack Overflow上看到的参数查询问题涉及更改SQL数据库中的数据，对此我不感兴趣.我只想设置一些基本的限制，以便重新运行代码.

Is there any way to do it in R? The parameter query questions I see on Stack Overflow deal with changing the data in the SQL database and I'm not interested in that. I just want to set some pretty basic limits so I can rerun code.

有些人可能会想:为什么不能只将5更改为6?"这是一个公平的观点，但是我担心，对于更复杂的查询，用户可能会错过SQL查询中将5更改为6的部分，这会使分析混乱或减慢分析速度.

Some may wonder "why can't you just change the 5 to a 6?" That's a fair point but I'm concerned that, with more complicated queries, users may miss a part in the SQL query to change the 5 to a 6 and that would mess the analysis up or slow it down.

谢谢！ 沃克

推荐答案

您可以在开头创建一个输入变量，并将其传递给查询. 例如:

You can create a input variable at the start and pass it to your query. For example:

# Change your FY here
input_FY <- 2016

dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')

degrees <- sqlQuery(dbhandle, paste0("
select Inst, ID, DegreeDate, Degree 
from DEGREE 
where FY = ('", input_FY, "') 
group by Inst, ID, DegreeDate, Degree 
order by Inst, ID, DegreeDate, Degree"), 
as.is=TRUE)

因此，对于任何复杂的查询，您仍然可以传递相同的input_FY变量或在代码开始时声明的其他任何变量，以进行快速/轻松的更新.

So for any complicated queries you can still pass the same input_FY variable or any other variable that you have declared at the start of code for a quick/easy update.

这篇关于使用R RODBC参数化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！