提取重新连接的时间

Question

我有一个结构如下的表格，其中包含有关俱乐部会员的信息

id  start       end          cancelled
1   2020-01-01  2020-12-31   2021-01-10
1   2021-02-01  2022-01-31   NA
2   2020-01-01  2020-12-31   NA
3   2020-01-01  2020-06-30   2020-07-01
3   2020-07-10  2021-01-09   2021-01-31
3   2021-02-02  2021-08-01   NA

我对后来重新加入的成员感兴趣。对于上述数据，在整个期间，ID1这样做了一次，ID2没有，而ID3这样做了两次。

每次发生这种情况，从他们的会员资格结束到他们重新加入的时间是多少？对于上述数据，这将是：

id  rejoin_date time_to_rejoin
1   2021-01-31  22 days 
3   2020-07-10  9 days
3   2021-02-02  2 days

structure(list(id = c(1, 1, 2, 3, 3, 3), start = c("2020-01-01", 
"2021-02-01", "2020-01-01", "2020-01-01", "2020-07-10", "2021-02-02"
), end = c("2020-12-31", "2022-01-31", "2020-12-31", "2020-06-30", 
"2021-01-09", "2021-08-01"), cancelled = c("2021-01-10", NA, 
NA, "2020-07-01", "2021-01-31", NA)), class = "data.frame", row.names = c(NA, 
-6L))

我很乐意得到使用R（理想情况下使用dplyr）或SQL（MySQL）的答案

Answer 1

您可以使用lag()然后筛选：

select t.*,
       datediff(cancelled, prev_cancelled) as num_days_since_cancel
from (select t.*,
             lag(cancelled) over (partition by id order by start) as prev_cancelled
      from t
     ) t
where prev_cancelled is not null;

Answer 2

首先，更改为date类，任何人都不应该尝试计算character上的日期差异。

dat[,-1] <- lapply(dat[,-1], as.Date)

do.call(rbind, by(dat, dat$id, function(z) {
  if (nrow(z) == 1) return()
  data.frame(id = z$id[1], rejoin_date = z$start[-1],
             time_to_rejoin = as.numeric(z$start[-1] - z$cancelled[-nrow(z)], units="days"))
}))
#     id rejoin_date time_to_rejoin
# 1    1  2021-02-01             22
# 3.1  3  2020-07-10              9
# 3.2  3  2021-02-02              2

library(dplyr)
dat %>%
  group_by(id) %>%
  summarize(
    rejoin_date = start[-1],
    time_to_rejoin = as.numeric(start[-1] - cancelled[-n()], units="days")
  ) %>%
  ungroup()
# # A tibble: 3 x 3
#      id rejoin_date time_to_rejoin
#   <dbl> <date>               <dbl>
# 1     1 2021-02-01              22
# 2     3 2020-07-10               9
# 3     3 2021-02-02               2

或

dat %>%
  group_by(id) %>%
  summarize(
    rejoin_date = start[-1],
    time_to_rejoin = as.numeric(start - lag(cancelled), units="days")[-1]
  ) %>%
  ungroup()

library(data.table)
datDT <- as.data.table(dat)
datDT[, .(rejoin_date = start[-1],
          time_to_rejoin = as.numeric(start[-1] - cancelled[-.N], units="days")), by = .(id) ]
#       id rejoin_date time_to_rejoin
#    <num>      <Date>          <num>
# 1:     1  2021-02-01             22
# 2:     3  2020-07-10              9
# 3:     3  2021-02-02              2