在sqldf崩溃R中使用RpostgreSQL

Question

我正在使用sqldf来培训一些使用SQL的r-用户。在加载RPostgreSQL或RH2之前加载sqldf将更改sqldf使用的默认SQL。这在h2中很好，但是每次我加载RPostgreSQL时，R都会在第一次尝试查询时崩溃。我想让sqldf与RPostgreSQL一起工作，以便能够使用SQLite和h2中缺少的date函数。

# packages
library(tidyverse)
# library(RH2) # comment out this row or the next to determine default SQL for sqldf
library(RPostgreSQL) 
library(sqldf)

输出确认"sqldf将默认使用PostgreSQL“。

创建一些数据：

set.seed(42)
N <- 1e6

sales <- tibble(
    buyer_id = 1:N/100,
    sales_date = sample(seq(as.Date('2018/01/01'), as.Date('2021/01/01'), by="day"), N, replace = TRUE),
    sales_amount = rpois(N, 200)
) 

坠毁R：

sqldf("
select 
    max(sales_date) 
from sales
")

"R会话中止R遇到了一个致命错误--会话终止了“

Answer 1

如果不使用H2的唯一原因是date_trunc，那么这里有一些解决方法。

1)宏是一种用于截断到年度/季度/月/周开始的解决方法。这些函数充当宏，扩展为H2接受的代码。确保使用fn$前缀sqldf，并在每一个前面加上引号以打开替换。注意，在每种情况下，参数都是一个字符串。将verbose=TRUE参数添加到sqldf以查看生成的代码。

library(RH2)
library(sqldf)

trunc_year <- function(x) sprintf("DATEADD(day, 1-day_of_year(%s), %s)", x, x)
trunc_qtr <- function(x) sprintf("DATEADD(month, 3*(quarter(%s)-1), %s)", x, 
  trunc_year(x))
trunc_month <- function(x) sprintf("DATEADD(day, 1-day_of_month(%s), %s)", x, x)
trunc_week <- function(x) sprintf("DATEADD(day, -iso_day_of_week(%s), %s)", x, x)

# test
DF <- data.frame(x = as.Date("2021-11-15"))
fn$sqldf("select x, 
                `trunc_year('x')` year, 
                `trunc_qtr('x')` qtr,
                `trunc_month('x')` month,
                `trunc_week('x')` week
          from DF")
##            x       year        qtr      month       week
## 1 2021-11-15 2021-01-01 2021-10-01 2021-11-01 2021-11-14

2)修补程序RH2，另一种可能是用带有date_trunc的较新版本的H2来修补RH2的安装。为此，我们在RH2中删除包含H2的h2-1.3.175.jar文件，并将其替换为更新版本h2-1.4.200.jar。只要您在RH2安装的java子目录中有写权限，这就可以工作。只要在R中运行这段代码，您的RH2安装就会得到修补。

u <- "https://h2database.com/h2-2019-10-14.zip"
z <- basename(u)
tmp <- tempdir()  # create temporary dir 
old.dir <- setwd(tmp)  # go to it
download.file(u, z)  # download u
unzip(z, "h2/bin/h2-1.4.200.jar")  # extract jar from zip file

# copy new jar file to RH2 installation and remove old one
new.jar <- file.path(tmp, "h2", "bin", "h2-1.4.200.jar")
old.jar <- dir(system.file("java", package = "RH2"), "h2.*jar$", full.names = TRUE)
if (basename(old.jar) != basename(new.jar)) {
  file.copy(new.jar, dirname(old.jar))
  file.remove(old.jar)
}
setwd(old.dir)  # return to original directory

# test
library(RH2)
library(sqldf)
sqldf("select h2version()")
##   '1.4.200'
## 1   1.4.200
# see more tests in (3) below

3)重新构建RH2，另一种可能是使用带有date_trunc的较新版本的H2创建一个新的RH2源版本。第一个参数是“年”、“季度”、“月份”或“周”，第二个参数是日期。因此，如果您愿意用这个新的RH2版本重新构建H2，那么它是可用的。

(a)从RH2下载https://cran.r-project.org/package=RH2的源代码，并将其用于创建目录树RH2。

(b)下载这个h2 zip https://h2database.com/h2-2019-10-14.zip并从该zip文件中提取h2/bin/h2-1.4.200.jar，并将jar文件放在RH2源代码的RH2/inst/java目录中，删除旧的j2-1.3.175.jar。

(c)像这样重建和测试RH2：

# rebuild RH2
# cd to the RH2 directory containing its DESCRIPTION file
setwd("...whatever.../RH2")

# build RH2 
#  on Windows this needs Rtools40 (not an R package)
#  https://cran.r-project.org/bin/windows/Rtools/
library(devtools)
build()
install(args = "--no-multiarch")

# test
library(RH2)
library(sqldf)
sqldf("select h2version()") # check it's the latest version
##   '1.4.200'
## 1   1.4.200

DF <- data.frame(x = as.Date("2000-11-15"))
sqldf("select date_trunc('month', x) from DF")
##   DATE_TRUNC('month', x)
## 1             2000-11-01