r - 多条件和不等式连接

Question

我有以下代码。此代码已在 SQL 中。我想在 R 中复制相同的代码(最好是 dplyr)。我找到了使用不等式条件而不是 AND 和 OR 条件加入的方法。有人可以指导我吗？

library(sqldf)


start_date <- c('2022-01-01','2022-02-01','2022-03-01','2022-04-01','2022-05-01','2022-06-01')
end_date <- c('2022-01-31','2022-02-28',NA,NA,'2022-05-31','2022-06-30')

dat1 <- data.frame(start_date, end_date)

date_col <- c('2022-01-15','2022-02-02','2022-03-17','2022-04-22','2022-05-14','2022-06-09')

dat_col <- data.frame(date_col)

final_dat <- sqldf("SELECT * FROM dat_col a LEFT JOIN dat1 b on b.start_date<= a.date_col AND (b.end_date < a.date_col 
                   OR b.end_date IS NULL)")

Answer 1

一个可能的解决方案，基于power_left_join:

library(powerjoin)
library(lubridate)

power_left_join(
  dat_col, dat1, 
  by = c(~ ymd(.y$start_date) <= ymd(.x$date_col) &
       (ymd(.y$end_date) < ymd(.x$date_col) | 
       is.na(.y$end_date))))

#>      date_col start_date   end_date
#> 1  2022-01-15       <NA>       <NA>
#> 2  2022-02-02 2022-01-01 2022-01-31
#> 3  2022-03-17 2022-01-01 2022-01-31
#> 4  2022-03-17 2022-02-01 2022-02-28
#> 5  2022-03-17 2022-03-01       <NA>
#> 6  2022-04-22 2022-01-01 2022-01-31
#> 7  2022-04-22 2022-02-01 2022-02-28
#> 8  2022-04-22 2022-03-01       <NA>
#> 9  2022-04-22 2022-04-01       <NA>
#> 10 2022-05-14 2022-01-01 2022-01-31
#> 11 2022-05-14 2022-02-01 2022-02-28
#> 12 2022-05-14 2022-03-01       <NA>
#> 13 2022-05-14 2022-04-01       <NA>
#> 14 2022-06-09 2022-01-01 2022-01-31
#> 15 2022-06-09 2022-02-01 2022-02-28
#> 16 2022-06-09 2022-03-01       <NA>
#> 17 2022-06-09 2022-04-01       <NA>
#> 18 2022-06-09 2022-05-01 2022-05-31