sql - 在 dplyr (SQL) 中处理日期和连接字符串

Question

我有一个包含 YEAR 和 DOY(一年中的某天)列的数据库。我想添加一个额外的列DATE，例如

mutate(data, DATE = date(julianday(YEAR || '-01-01'), '+'||(DOY-1)||' day')) 

但是那不行，可能是因为SQL字符串连接“||”转换为“OR”。你会怎么做？

在另一种情况下，我必须转换 SQL 日期对象中的字符串。在 R 中，我会使用 lubridate 和 ymd 在 dplyr (SQL) 中是否有类似的简单方法来处理 mutate？

克里斯托夫

Answer 1

mutate 在 dplyr 修改结果但不修改我收集的数据库中的表是你想要的。

问题没有说明正在使用哪个数据库，这很重要，但在没有 SQLite 的情况下，下面将使用它。

1) 下面是使用 RSQLite 包更新数据库中表 DF 的代码，其中包含 DATE 列。 (DOY-1) 可能需要代替 DOY，具体取决于 DOY 的来源(0 或 1)。您可能还需要将 DOY 转换为整数，但在我的运行中它没有这样做。

library(RSQLite)

# create test database with a table DF
con <- dbConnect(SQLite())
DF <- data.frame(YEAR = 2014, DOY = 15)
dbWriteTable(con, "DF", DF)

# add DATE column to table DF and update its value
dbGetQuery(con, "alter table DF add column DATE")
dbGetQuery(con, "update DF 
                 set DATE = date(cast(YEAR as integer) || '-01-01', DOY || ' days')")

给予:

dbGetQuery(con, "select * from DF")
##   YEAR DOY       DATE
## 1 2014  15 2014-01-16

2) 如果您只是想修改结果而不是数据库本身，那么假设使用 SQLite 再试一次:

library(dplyr)
library(RSQLite)

# create database and add table DF to it
db <- src_sqlite(path = tempfile(), create = TRUE) # test database
DF <- data.frame(YEAR = 2014, DOY = 15) # test data
dbWriteTable(db$con, "DF", DF)

db_DF <- tbl(db, "DF")
db_DF %>% 
      mutate(DATE = sql("date(cast(YEAR as integer) || '-01-01', DOY || ' days')"))

更新 (1) 使用RSQLite 修改数据库。 (2) 使用和不使用 dplyr。