时间戳

首页 > 解决方案 > 将 dplyr 转换为 sql

mysql - 将 dplyr 转换为 sql

问题描述

我的数据结构如下：


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

这些数据代表俱乐部会员资格，目标是提取那些取消会员资格并随后重新加入的会员。特别是我对取消和重新加入之间的天数感兴趣。

在 RI 中可以做到：

dat <- 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)) %>%

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

wheredrop_na(time_to_rejoin)处理成员具有多个并发未取消成员资格的情况，这会导致：

# A tibble: 3 x 3

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

我怎样才能在 MySQL 中做到这一点？

CREATE TABLE IF NOT EXISTS `dat` (
  `id` int(6) unsigned NOT NULL,
  `start` TIMESTAMP,
  `end` TIMESTAMP,
  `cancelled` TIMESTAMP NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `dt` (`id`, `start`, `end`, `cancelled`) VALUES
  ('1', '2020-01-01', '2020-12-31', '2021-01-10'),
  ('2', '2021-02-01', '2022-01-31', NULL ),
  ('2', '2021-01-01', '2020-12-31', NULL ),
  ('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', NULL )

http://sqlfiddle.com/#!9/252e6d6

标签： mysqlrdplyr

解决方案

SELECT t1.id, 
       COALESCE(t1.cancelled, t1.end) `end`, 
       t2.start next_start, 
       DATEDIFF(t2.start, COALESCE(t1.cancelled, t1.end)) gap
FROM dat t1
JOIN dat t2 ON t1.id = t2.id 
           AND COALESCE(t1.cancelled, t1.end) < t2.start
WHERE NOT EXISTS ( SELECT NULL
                   FROM dat t3
                   WHERE t1.id = t3.id
                     AND COALESCE(t1.cancelled, t1.end) < t3.start
                     AND COALESCE(t3.cancelled, t3.end) < t2.start )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e822fab2c63812c4f815f2700e62dd7e

PS。小提琴中的错误 src 数据（错字？）已修复。

聚苯乙烯。如果您不需要返回相邻的句点，则添加HAVING gap > 1到查询的最末尾。

你能添加一个关于它是如何工作的描述吗？——乔金

t1t2表格副本用于检索“上一个”和“下一个”期间。

t3允许确保它们是相邻的（从t1和开始的时间段之间没有任何时间段t2）。

也就是说，如果我们有 3 个周期，那么 JOIN 会产生 3 对 (1-2)、(2-3)、(1-3)，但 WHERE 会删除最后一对，因为在周期 1 和周期 3 之间存在周期 2。

COALESCE 用于获取cancelled是否已设置和end未设置（为 NULL）。

推荐阅读