mysql - 在 dbGetQuery 中为 () 查询中的 sql 准备字符串

Question

我编写了以下查询来研究我的分析的一些示例方法，并且工作正常，我不需要修改此代码。但是我需要理解 "",; 的使用sep="" 以及 dbGetQuery 中的 limit 语句。

df <- data.frame(Sample.Num = integer(),
                 Sample.Mean = integer(),
                 quant.01 = integer(),
                 quant.05 = integer(),
                 quant.10 = integer(),
                 quant.25 = integer(),
                 quant.50 = integer(),
                 quant.75 = integer(),
                 stringsAsFactors = FALSE)
df[1,] <- NA
for (i in 1:500){
  sdf <- dbGetQuery(con,"select col11,col23,col30
                    from schema.db.name
                    where col1 in ('value1')
                    and col2 in ('(v3) - value3')
                    and col3 in ('v0123 - value4')
                    order by random()*600000 limit 100")
  meansample <- mean(sdf$mileage,na.rm = TRUE)
  quant.01 <- quantile(sdf$mileage,na.rm = TRUE,probs = .01)
  quant.05 <- quantile(sdf$mileage,na.rm = TRUE,probs = .05)
  quant.10 <- quantile(sdf$mileage,na.rm = TRUE,probs = .10)
  quant.25 <- quantile(sdf$mileage,na.rm = TRUE,probs = .25)
  quant.50 <- quantile(sdf$mileage,na.rm = TRUE,probs = .50)
  quant.75 <- quantile(sdf$mileage,na.rm = TRUE,probs = .75)
dbDisconnect(con)

问题:

Col1、2 和 3 具有字符值。现在我需要检查 col2 的所有 7 个值和 col3 的所有 9 个值。每当我使用 col1,2,3 的任何特定值时，我都需要将它们存储在数据框中，以便在循环结束并以第二个值重新启动之前执行一些操作。

因此需要学习:Add a dynamic value into RMySQL getQuery

我也研究了 gsub 和 shQuote。尽管有多种组合以及限制语句，但我无法概念化 '", df[i], "';", sep = "" 的使用。如果它是本地数据库，我可以使用 dplyr 包来运行循环。我知道 seq_along() 需要一个数值，并且为了通过循环，在 () 中设置正确的“正确值”我确实尝试过paste0并将参数与collapse=","参数一起粘贴，但没有帮助。我还尝试了下面帖子中的 lapply 但它在 () 查询中不起作用。

How to do dbGetQuery for loop in R

(v3) - value3、(v1) - value1 等是 col 中的实际元素。这是属性 col2、col3 等的代码和描述的组合。

Answer 1

假设您有一个查询并想要替换其中的一些范围值。

sql <- "select col11,col23,col30
                    from schema.db.name
                    where col1 in (%s)
                    and col2 in (%s)
                    and col3 in (%s)
                    order by random()*600000 limit 100"

这里我将使用sprintf在查询中进行替换，所以我在字符串中留下了标记(%s)。因此，让我们设置 3 个要测试的范围:

col1 <- 1:10
col2 <- c('a', 'f', 'z')
col3 <- c('name1', 'name2')

# create strings that are valid in SQL
col1_sql <- paste(col1, collapse = ',')
col2_sql <- paste0("'", col2, "'", collapse = ',')  # put quotes on strings
col3_sql <- paste0("'", col3, "'", collapse = ',')

# now substitute back in query
sql_new <- sprintf(sql,
                   col1_sql,  # strings we just constructed
                   col2_sql,
                   col3_sql
)

# print out the query
cat(sql_new)

以下是查询内容:

select col11,col23,col30
    from schema.db.name
    where col1 in (1,2,3,4,5,6,7,8,9,10)
    and col2 in ('a','f','z')
    and col3 in ('name1','name2')
    order by random()*600000 limit 100