在sqldf中调用R变量

Question

我需要在sqldf语句上进行循环，为此我需要在sqldf代码中调用循环变量：

我的表"data"可能是：

data <- read.table(text ="
    loaddate DaysRange DaysRangeNext
1 2014-03-16        30            30
2 2014-03-16         0             0
3 2014-03-16         0             0
4 2014-03-16        60            NA
5 2014-04-16        30            30
6 2014-04-16         0            30
"
,header = TRUE)

然后我将loaddate格式化为日期：

data$loaddate<-as.Date(as.character(data$loaddate), format='%Y-%m-%d')

假设我有一个向量“loaddates”：

loaddates<- unique(sort(data$loaddate))

我需要针对每个loaddate运行以下代码：

for (i in loaddates) {

sqldf("
SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
COUNT(*) AS clientes
FROM data AS D
WHERE D.loaddate = i
GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
ORDER BY D.DaysRange, D.DaysRangeNext
")        }

但是我遇到了以下错误:

在sqliteSendQuery(con, statement, bind.data)中的错误: 语句错误：没有这样的列：i

有没有办法保留变量值并在循环内部使用它？

谢谢。

编辑：

我尝试过：

sqldf(
strwrap(sprintf("
SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
COUNT(*) AS clientes 
FROM data AS D
WHERE D.LoadDate = '%s'
GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
ORDER BY D.DaysRange, D.DaysRangeNext
",i),simplify=TRUE,width=1000000))

但是我得到了：

> [1] loaddate      DaysRange     DaysRangeNext clientes      <0 rows>
> (or 0-length row.names)

- GabyLP

Answer 1

变量i不会被替换为查询中的值。您需要使用sprintf为其分配一个值。(我也不知道是否需要考虑换行，但为了确保我在下面提供了它。如果您不需要sqldf，则只需删除strwrap)。

#let's assume loaddates is the following:
loaddates <- 'something'

有一种获取所需查询的方法，即没有断行并且i取到所需的loaddates值：

strwrap(sprintf("
                SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
                COUNT(*) AS clientes
                FROM deuda AS D
                WHERE D.loaddate = '%s'
                GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
                ORDER BY D.DaysRange, D.DaysRangeNext
                ",i),simplify=TRUE,width=1000000)

这将会输出：

[1] "SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, COUNT(*) AS clientes FROM deuda AS D WHERE D.CodEmp = 'TGG' and D.loaddate = something GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext ORDER BY D.DaysRange, D.DaysRangeNext"

您需要的是一行代码，不带换行符或未赋值的变量i。

在您的循环中应该是：

for (i in loaddates) {

strwrap(sprintf("
                SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
                COUNT(*) AS clientes
                FROM deuda AS D
                WHERE D.loaddate = '%s'
                GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
                ORDER BY D.DaysRange, D.DaysRangeNext
                ",i),simplify=TRUE,width=1000000)

}

使用您的数据集：

library(sqldf)
data <- read.table(text ="
    loaddate DaysRange DaysRangeNext
1 2014-03-16        30            30
2 2014-03-16         0             0
3 2014-03-16         0             0
4 2014-03-16        60            NA
5 2014-04-16        30            30
6 2014-04-16         0            30
"
                   ,header = TRUE,stringsAsFactors=F)

loaddates<- unique(sort(data$loaddate))

for (i in loaddates) {

  print(sqldf(
  strwrap(sprintf("
                SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
                COUNT(*) AS clientes
                FROM data AS D
                WHERE D.loaddate = '%s'
                GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
                ORDER BY D.DaysRange, D.DaysRangeNext
                ",i),simplify=TRUE,width=1000000) ))
}

输出：

    loaddate DaysRange DaysRangeNext clientes
1 2014-03-16         0             0        2
2 2014-03-16        30            30        1
3 2014-03-16        60            NA        1
    loaddate DaysRange DaysRangeNext clientes
1 2014-04-16         0            30        1
2 2014-04-16        30            30        1

- LyzandeR

---

same result with quotes - GabyLP

但我的代码是可以运行的！你可以复制粘贴我的示例并检查。你需要在代码中添加 print 才能看到结果。此外，你的数据集中也没有添加 CodEmp 列。 - LyzandeR

顺便提一下，数据中的 loaddate 列是我使用的 character 类型。你的是 Date 类型吗？ - LyzandeR

打印是问题所在。谢谢。 - GabyLP

很高兴能够帮助到你（虽然我尝试了几次）!! - LyzandeR

Answer 2

您可以通过在循环内但函数调用之外定义SQL语句来使其工作。

for (i in loaddates) {

statement = paste( " SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext,
              COUNT(*) AS clientes
              FROM data AS D
              WHERE D.loaddate = ", i,
"GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
ORDER BY D.DaysRange, D.DaysRangeNext " )

sqldf(statement)
}

- anakin

Answer 3

首先创建一个新的数据框，然后加入它：

num_Pcode <- as.numeric("3550")
df_Pcode_0 <- as.data.frame(num_Pcode)
df_Pcode_0
...

返回 num_Pcode。

- maRRRRk

Answer 4

fn$sqldf 允许在 SQL 语句中使用 $ 插值 R 变量。请参见 sqldf GitHub 主页上的示例 5，以及帮助页面底部的 ?fn 获取更多示例。如果我们不需要输出名称，我们可以将 setNames(loaddates, loaddates) 简化为 loaddates。

Map(function(i)
  fn$sqldf("
    SELECT D.LoadDate,D.DaysRange, D.DaysRangeNext, 
    COUNT(*) AS clientes
    FROM data AS D
    WHERE D.loaddate = $i
    GROUP BY D.LoadDate,D.DaysRange, D.DaysRangeNext
    ORDER BY D.DaysRange, D.DaysRangeNext
  "), setNames(loaddates, loaddates))

提供：

$`2014-03-16`
    loaddate DaysRange DaysRangeNext clientes
1 2014-03-16         0             0        2
2 2014-03-16        30            30        1
3 2014-03-16        60            NA        1

$`2014-04-16`
    loaddate DaysRange DaysRangeNext clientes
1 2014-04-16         0            30        1
2 2014-04-16        30            30        1

- G. Grothendieck