r语言 - 从未润滑的日期减去1年

Question

我在数据库中有一个数据框架，我使用RPostgres拉出了一个日期维度。每个"日期"的格式为"YYYY-MM-DD"。我想添加一个新的日期列(标记为'lookback_date')，它比初始日期早一年。

需要明确的是，如果一个观测值的"日期"是"2000-01-01"，我想为该观测值添加一个新的"lookback_date"为"1999-01-01"。不幸的是，我不知道该怎么做。通常情况下，我会用润滑剂，但是，据我所知，它不工作与dbplyr。这是目前为止我的代码的精简版本。在我的实际代码中，直到mutate函数之前，一切都工作得很好。

# Packages
library(dbplyr)
library(RPostgres)
# Connect to db 
drv <- dbDriver("Postgres")
# Setup connect to db
conn <- dbConnect(drv,
dbname = etc,)
# Define table to use in db
table <- tbl(conn, in_schema("xyz", "abc"))
#Select columns and filter
base_data <- table %>%
#Filter for pertinent data
filter(date > as.Date("2018-01-01") & date <= as.Date("2020-01-01"))
modified_data <- base_data %>%
mutate(lookback_date = date - 365)

是否有其他方法可以创建这个新的日期列?

谢谢!

Answer 1

你是正确的，润滑剂和dbplyr不能很好地发挥在一起(现在)。因此，我使用sql的片段来执行大多数dbplyr日期操作。

根据这个答案和这个站点，postgresql从日期中添加/减去时间的语法是:

SELECT old_date + INTERVAL '1 day' AS new_date;

基于此，我将尝试以下操作:

output = base_data %>% mutate(lookback_date = date - sql("INTERVAL '1 year'"))

当我对模拟连接执行此操作时，它会生成正确的语法:

library(dplyr)
library(dbplyr)
df = data.frame(my_num = c(1,2,3), my_dates = as.Date(c('2000-01-01','2000-02-02','2000-03-03')))
df = tbl_lazy(df, con = simulate_postgres())
output = df %>% mutate(new_date = my_dates - sql("INTERVAL '1 year'"))
show_query(output)
# <SQL>
# SELECT `my_num`, `my_dates`, `my_dates` - INTERVAL '1 year' AS `new_date`
# FROM `df`

---

更新:从注释中，您首先要将日期时间转换为日期。

dbplyr似乎不支持将as.Date转换为PostgreSQL (as.Date是base R的一部分，而不是润滑油的一部分)。因此，可以使用以下命令将列强制转换(转换)为日期:

library(dplyr)
library(dbplyr)
df = data.frame(my_str = c('2000-01-01','2000-02-02','2000-03-03'))
df = tbl_lazy(df, con = simulate_postgres())
output = df %>% mutate(my_date = as.Date(my_str))
show_query(output)
# <SQL>
# SELECT `my_str`, CAST(`my_str` AS DATE) AS `my_date`
# FROM `df`

PostgreSQL似乎也不允许你添加一年的间隔。另一种方法是从日期中提取年、月和日，在年份中添加1，然后重新组合。

在这两个引用(postgre date引用和date_part function)和这个答案之后，您可能想要以下内容:

output = df %>%
mutate(the_year = DATE_PART('year', my_date),
the_month = DATE_PART('month', my_date),
the_day = DATE_PART('day', my_date)) %>%
mutate(new_date = MAKE_DATE(the_year + 1, the_month, the_day)

Answer 2

您可以使用字符串将年份减去1并将其与日期和月份连接起来。我不确定这是否会转化为sql !这也可以防止闰年把日子弄乱。

base_data %>%
mutate(lookback_date = as.Date(paste0((as.numeric(substr(date,1,4)))-1,substr(date,5,10)),format="%Y-%m-%d"))