r - sqldf:按日期范围查询数据

Question

我正在读取一个具有'%d/%m/%Y'日期格式的巨大文本文件。我想使用sqldf的read.csv.sql来同时读取和按日期过滤数据。这是为了通过跳过许多我不感兴趣的日期来节省内存使用量和运行时间。我知道如何在 dplyr 和 lubridate 的帮助下执行此操作，但我由于上述原因，只想尝试使用 sqldf 。尽管我非常熟悉 SQL 语法，但大多数时候它仍然让我困惑，sqldf 也不异常(exception)。

运行如下命令返回一个包含 0 行的 data.frame:

first_date <- "2001-11-1"
second_date <- "2003-11-1"
query <- "select * from file WHERE strftime('%d/%m/%Y', Date, 'unixepoch', 'localtime') between
                    '$first_date' AND '$second_date'"
df <- read.csv.sql(data_file,
                       sql= query,
                       stringsAsFactors=FALSE,
                       sep = ";", header = TRUE)

因此，为了进行模拟，我尝试使用 sqldf 函数，如下所示:

first_date <- "2001-11-1"
second_date <- "2003-11-1"
df2 <- data.frame( Date = paste(rep(1:3, each = 4), 11:12, 2001:2012, sep = "/"))
sqldf("SELECT * FROM df2 WHERE strftime('%d/%m/%Y', Date, 'unixepoch') BETWEEN '$first-date' AND '$second_date' ") 

# Expect:
# Date
# 1  1-11-2001
# 2  1-12-2002
# 3  1-11-2003

Answer 1

strftime strftime使用百分比代码将已被 sqlite 视为日期时间的对象转换为其他内容，但您想要相反，因此问题中的方法不起作用。例如，这里我们将当前时间转换为 dd-mm-yyyy 字符串:

library(sqldf)
sqldf("select strftime('%d-%m-%Y', 'now') now")
##          now
## 1 07-09-2014

讨论 由于 SQlite 缺乏日期类型，处理这个问题有点麻烦，特别是对于 1 或 2 位非标准日期格式，但如果你真的想使用 SQLite 我们可以通过繁琐地解析日期字符串来做到这一点。使用fn$用于字符串插值的 gsubfn 包稍微缓解了这一点。

代码如下zero2d输出 SQL 代码，如果输入是一位数字，则在其前面添加一个零字符。 rmSlash输出 SQL 代码以删除其参数中的所有斜杠。 Year , Month和Day每个输出 SQL 代码都以所讨论的格式获取表示日期的字符串，并提取指示的组件，在 Month 的情况下将其重新格式化为 2 位零填充字符串。和Day 。 fmtDate接受问题中所示形式的字符串 first_string和second_string并输出 yyyy-mm-dd字符串。

library(sqldf)
library(gsubfn)

zero2d <- function(x) sprintf("substr('0' || %s, -2)", x)

rmSlash <- function(x) sprintf("replace(%s, '/', '')", x)

Year <- function(x) sprintf("substr(%s, -4)", x)

Month <- function(x) {
   y <- sprintf("substr(%s, instr(%s, '/') + 1, 2)", x, x)
   zero2d(rmSlash(y))
}

Day <- function(x) {
   y <- sprintf("substr(%s, 1, 2)", x)
   zero2d(rmSlash(y))
}

fmtDate <- function(x) format(as.Date(x))

sql <- "select * from df2 where 
  `Year('Date')` || '-' || 
  `Month('Date')` || '-' || 
  `Day('Date')`
  between '`fmtDate(first_date)`' and '`fmtDate(second_date)`'"
fn$sqldf(sql)

给予:

       Date
1 1/11/2001
2 1/12/2002
3 1/11/2003

注释

1) 使用的 SQLite 函数 instr , replace和substr是核心sqlite函数

2) SQL fn$之后实际执行的SQL语句执行替换如下(稍微重新格式化以适应):

> cat( fn$identity(sql), "\n")
select * from df2 where 
  substr(Date, -4) 
  || '-' || 
  substr('0' || replace(substr(Date, instr(Date, '/') + 1, 2), '/', ''), -2) 
  || '-' || 
  substr('0' || replace(substr(Date, 1, 2), '/', ''), -2)
  between '2001-11-01' and '2003-11-01' 

3)并发症来源 主要并发症是不标准的1位或2位数字日和月。如果它们始终为 2 位数，则会减少为:

first_date <- "2001-11-01"
second_date <- ""2003-11-01"

fn$sqldf("select Date from df2 
   where substr(Date, -4) || '-' || 
         substr(Date, 4, 2) || '-' ||
         substr(Date, 1, 2)
   between '`first_date`' and '`second_date`' ")

4) H2 这是一个 H2 解决方案。 H2 确实有一个日期时间类型，与 SQLite 相比，它大大简化了解决方案。我们假设数据位于名为 mydata.dat 的文件中。请注意read.csv.sql不支持 H2，因为 H2 已经具有内部 csvread SQL 函数执行此操作:

library(RH2)
library(sqldf)

first_date <- "2001-11-01"
second_date <- "2003-11-01"

fn$sqldf(c("CREATE TABLE t(DATE TIMESTAMP) AS
  SELECT parsedatetime(DATE, 'd/M/y') as DATE
  FROM CSVREAD('mydata.dat')", 
  "SELECT DATE FROM t WHERE DATE between '`first_date`' and '`second_date`'"))

请注意， session 中的第一个 RH2 查询会很慢，因为它会加载 java.util.之后您可以尝试一下，看看性能是否足够。