使用DBI在R中进行日期循环查询

Question

我有一个查询

library(DBI)
res = dbSendQuery(con,
"select 
X, Y, z from table where date between date'2018-07-01' - interval '31' day and date'2018-07-01 - interval '1' day")
res.df = dbFetch(res, -1)  

我想在日期循环中运行这个，以便它填充2018-07-01和2018-07-30 之间所有日期的数据

有人能帮我查一下的代码吗

Answer 1

不需要循环来填充2018-07-01和2018-07-30之间所有日期的数据。

您可以只按日期筛选：

res = dbSendQuery(con,
"select X, Y, z 
from table 
where date > '2018-06-31' 
and date < '2018-07-31'")
res.df = dbFetch(res, -1)  

Answer 2

如果你真的想要一个循环：

date_seq <- as.character(seq(as.Date("2017-07-01"), 
as.Date("2017-07-30"), by = "day"))
for (i in 1:length(date_seq)) {
string <- "select X, Y, z from table where date between date'REPLACEME' - interval '31' day and date'REPLACEME' - interval '1' day"
string <- gsub("REPLACEME", date_seq[i], string)
print(string) # replace this with the dbSendQuery
}
[1] "select X, Y, z from table where date between date'2017-07-01' - interval '31' day and date'2017-07-01' - interval '1' day"
[1] "select X, Y, z from table where date between date'2017-07-02' - interval '31' day and date'2017-07-02' - interval '1' day"
...
[1] "select X, Y, z from table where date between date'2017-07-30' - interval '31' day and date'2017-07-30' - interval '1' day"