在R中使用RODBC创建循环SQL查询

Question

首先，也是最重要的--谢谢你花时间看我的问题，不管你是否回答！

我试图使用R中的RODBC包创建一个循环遍历我的df和SQL中必要数据中的查询的函数，但是我在设置查询时遇到了问题，因为查询的参数在每次迭代中都会发生变化(下面的例子)

我的df看起来是这样的：

     ID     Start_Date    End_Date    
     1       2/2/2008      2/9/2008
     2       1/1/2006      1/1/2007
     1       5/7/2010      5/15/2010
     5       9/9/2009      10/1/2009

如何在sql程序中指定开始日期和结束日期？

到目前为止，我的情况如下：

 data_pull <- function(df) {
    a <- data.frame()
    b <- data.frame()

    for (i in df$id)
{ 
    dbconnection <- odbcDriverConnect(".....")

    query <- paste("Select ID, Date, Account_Balance from Table where ID = (",i,") and Date > (",df$Start_Date,") and Date <= (",df$End_Date,")")

    a <- sqlQuery(dbconnection, paste(query))
    b <- rbind(b,a)
 }
  return(b)
 }

但是，这在任何情况下都不会查询。我相信这与我如何指定迭代的开始日期和结束日期有关。

如果有人能在这方面提供帮助，我们将不胜感激。如果您需要进一步的解释，请不要犹豫地问！

Answer 1

当前的设置产生了几个语法问题：

1. 循环:您不迭代数据帧的所有行，而只迭代单个列df$ID中的原子ID值。在同一个循环中，您将把、df$Start_Date和df$End_Date的整个向量传递到查询连接中。
2. 日期:您的日期格式与“YYYY-MM-DD”的大多数数据库日期格式不对齐。还有其他一些像甲骨文，你需要字符串到数据转换：TO_DATE(mydate, 'YYYY-MM-DD')。

上述几个绩效/最佳做法问题：

1. 参数化:虽然出于安全原因不需要参数化，因为您的值不是由可以注入恶意SQL代码的用户输入生成的，但为了可维护性和可读性，建议使用参数化查询。因此，考虑这样做。
2. 增长对象:根据Patrick的地狱循环2:增长对象，R程序员应该避免在循环中生成多维对象，比如数据帧，这会导致内存中的过度复制。相反，在循环之外为rbind 构建一个数据帧列表。

这样，您就可以避免任何循环或列表需要，方法是将数据帧保存为数据库表，然后将其连接到最终表，用于筛选后的join查询导入。这假设您的数据库用户拥有CREATE TABLE和DROP TABLE权限。

# CONVERT DATE FIELDS TO DATE TYPE
df <- within(df, {
          Start_Date = as.Date(Start_Date, format="%m/%d/%Y")
          End_Date = as.Date(End_Date, format="%m/%d/%Y")
})

# SAVE DATA FRAME TO DATABASE
sqlSave(dbconnection, df, "myRData", rownames = FALSE, append = FALSE)

# IMPORT JOINED AND DATE FILTERED QUERY
q <- "SELECT ID, Date, Account_Balance 
      FROM Table t
      INNER JOIN myRData r 
        ON r.ID = t.ID 
        AND t.Date BETWEEN r.Start_Date AND r.End_Date"

final_df <- sqlQuery(dbconnection, q)