Вычитание 1 года из даты без смазки

У меня есть фрейм данных в базе данных с измерением даты, который я извлек с помощью RPostgres. Каждая «дата» имеет формат «ГГГГ-ММ-ДД. Я хотел бы добавить новый столбец дат (помеченный как «lookback_date»), который на год раньше, чем исходные даты.

Для ясности, если бы «датой» наблюдения было «2000-01-01», я бы хотел добавить к этому наблюдению новую «lookback_date» - «1999-01-01». К сожалению, я не могу понять, как это сделать. Обычно я бы использовал Lubridate, но, насколько я могу судить, он не работает с dbplyr. Вот оптимизированная версия моего кода. Все в моем фактическом коде работает нормально до функции изменения.

# 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)

Есть ли другой способ создать этот новый столбец дат?

Спасибо!


r date dbplyr rpostgresql
person NoCaresBear    schedule 12.02.2021    source источник
comment
Вы ищете способ вычесть один год из даты? Или вы ищете способ вычесть один год из даты, которую dbplyr преобразует в SQL и выполнит на стороне сервера? Вот почему я предполагаю, что вы не хотите использовать lubridate.   -  person Adam    schedule 13.02.2021
comment
@Adam Я бы хотел убрать один год из дат. Есть ли способ сделать это с помощью dbplyr? Печать «Modified_data» после запуска этой функции mutate вызывает ошибку. У меня нет этой проблемы, когда я использую этот код в локальных фреймах данных. Надеюсь, я использую здесь правильную терминологию.   -  person NoCaresBear    schedule 13.02.2021

Ответы (2)


arrow_upward
2
arrow_downward

Вы правы, что lubridate и dbplyr не могут хорошо играть вместе (прямо сейчас). В результате я выполняю большую часть своих манипуляций с датой dbplyr, используя фрагменты sql.

На основании этого ответа и этот сайт, синтаксис 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 является частью базы R, а не lubridate). Следовательно, вы можете использовать следующее для преобразования (преобразования) столбца на дату:

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 не позволяет добавлять интервал в один год. Альтернативой этому является извлечение года, месяца и дня из даты, прибавление единицы к году и последующее повторное объединение.

После этих двух ссылок (ссылки на дату postgre и функция date_part) и этот ответ, вы, вероятно, захотите что-то вроде следующего:

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)
person Simon.S.A.    schedule 13.02.2021
comment
Спасибо за ваш ответ! Когда я запустил код изменения, я получил следующую ошибку: Ошибка: не удалось получить строку: ОШИБКА: значения интервала с частями месяца или года не поддерживаются (может ли это быть проблемой Redshift?). Когда я изменил «1 год» на «365 дней», это, похоже, устранило проблему, но расчет даты, похоже, не очень хорошо работает с високосными годами. Например, наблюдение от 08.09.2020 получает new_date на 09.09.2019. Есть ли способ обойти это? Спасибо за твою помощь! @ Simon.S.A. РЕДАКТИРОВАТЬ: новые даты указаны в dttm. Как мне приготовить финики без смазки? - person NoCaresBear; 15.02.2021
comment
Рад, что ты нашел это полезным. Я расширил ответ, чтобы ответить на ваш комментарий. Вы все еще можете получить некоторые ошибки - поскольку я не могу воспроизвести вашу среду R и SQL, нет никакого способа гарантировать, что мой ответ будет работать, если вы скопируете и вставите его в свою среду. Но, надеюсь, это хорошая отправная точка для адаптации. - person Simon.S.A.; 15.02.2021

arrow_upward
0
arrow_downward

Вы можете использовать строки, чтобы вычесть год, вычесть 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"))
person zimia    schedule 12.02.2021
comment
Спасибо за ваш ответ! Когда я пытаюсь использовать glimpse () modified_data, я получаю следующую ошибку: Ошибка в as.Date (paste0 ((as.numeric (substr (service_date, 1, 4))) - 1,: unused argument (format =% Y- % m-% d) Есть идеи, почему это может быть? Спасибо за вашу помощь! - person NoCaresBear; 13.02.2021
comment
хм, у меня нет такой проблемы. Rows: 3 Columns: 2 $ date <date> 2020-01-01, 1999-03-21, 1876-02-01 $ lookback_date <date> 2019-01-01, 1998-03-21, 1875-02-01. Однако на самом деле вам не нужно указывать формат, поскольку формат уже находится в ISO-8601, поэтому, возможно, просто полностью удалите аргумент формата и попробуйте - person zimia; 13.02.2021