如何计算dbplyr (R和sql)中的月差

Question

我尝试使用dbplyr包将我的代码发送到sql查询，以月为单位计算日期之间的差异。我尝试了类似问题中建议的不同方法，但它们都不起作用(例如下面的代码)。Data2是由tbl()创建的对象

Data2 %>% mutate(age_Months = (lubridate::interval(date_1, date_2)) / base::months(1))
#OR
library(lubridate)
Data2 %>% mutate(age_Months = TIMESTAMPDIFF(MONTH, date_1, date_2))

你有什么办法来计算差额吗?请随意建议任何其他库和函数，这无关紧要。我只需要月份的差额。谢谢你

数据集如下。

Data2 %>% select(date_1, date_2) %>% head() %>% collect()
#  date_1                date_2               
#  <chr>                 <chr>                
#1 2015-05-01 00:00:00.0 2036-02-01 00:00:00.0
#2 2015-05-01 00:00:00.0 2036-01-01 00:00:00.0
#3 2015-05-01 00:00:00.0 2031-03-01 00:00:00.0
#4 2015-05-01 00:00:00.0 2035-12-01 00:00:00.0
#5 2015-05-01 00:00:00.0 2035-05-01 00:00:00.0
#6 2015-05-01 00:00:00.0 2032-03-01 00:00:00.0

我用as.Date()对它们进行了变换，但结果没有变化。

Data2 %>% mutate(date_1 = as.Date(date_1),
date_2 = as.Date(date_2))
#  date_1     date_2    
#  <date>     <date>    
#1 2018-08-01 2036-02-01
#2 2018-08-01 2036-06-01
#3 2018-08-01 2036-01-01
#4 2018-08-01 2034-08-01
#5 2018-08-01 2033-08-01
#6 2018-08-01 2035-03-01

Answer 1

这里有两种可能的方法。

1)使用润滑剂dbplyr翻译为一些(但不是全部)润滑函数定义为一些(但不是全部)SQL风格(上次测试时)。所以我将从下面开始:

Data2 %>%
mutate(year1 = lubridate::year(date_1),
year2 = lubridate::year(date_2),
month1 = lubridate::month(date_1),
month2 = lubridate::month(date_2)) %>%
mutate(age_months = 12*(year2 - year1) + month2 - month1) %>%
select(-year1, -year2, -month1, -month2)

2)使用未翻译SQL函数

Impala包含一个DATEDIFF函数(文档在这里)。当dbplyr遇到没有为其定义翻译的命令时，它将未翻译的命令传递给数据库。

就像这样:

Data2 %>%
mutate(age_months = DATEDIFF(date_2, date_1) / 30)

应该这样转换成SQL:

SELECT *
,DATEDIFF(date_2, date_1) / 30 AS age_months
FROM data2