问题 如何解决R和性能与SQL的差距和孤岛问题？

我想知道是否可以有效地解决岛屿和间隙问题，类似于SQL。如果我们检查一个ID，我有以下数据：

ID StartDate  StartTime EndDate      EndTime 
1  19-05-2014 19:00     19-05-2014   20:00
1  19-05-2014 19:30     19-05-2014   23:30
1  19-05-2014 16:00     19-05-2014   18:00
1  20-05-2014 20:00     20-05-2014   20:30

请注意，前两行重叠，我想要做的是合并重叠的行，结果：

ID StartDate  StartTime EndDate      EndTime 
1  19-05-2014 19:00     19-05-2014   23:30
1  19-05-2014 16:00     19-05-2014   18:00
1  20-05-2014 20:00     20-05-2014   20:30

有没有办法在R中这样做？

我很清楚这是在SQL中完成的，但由于我的数据已经在R中，我更喜欢在R中执行此操作。其次，我对查找间隙和孤岛的性能有一些疑问，我知道SQL非常快这样做，但我想知道由于所有数据都在内存中R是否更快。

我想用 data.table 这样做，但我不知道如何。

UPDATE - 对Arun的回应

我创建了以下测试用例，其中包含每个可能的间隔方向。

dat <- structure(
  list(ID = c(1L, 1L, 1L, 1L, 1L, 1L), 
       stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 11:30:00"),
                           as.POSIXct("2014-01-15 12:00:00")),
                         class = c("POSIXct", "POSIXt"), tzone = ""),
       etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 11:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"), 
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 12:30:00"),
                           as.POSIXct("2014-01-15 13:00:00")), 
                         class = c("POSIXct", "POSIXt"), tzone = "")
  ),
  .Names = c("ID", "stime", "etime"),
  sorted = c("ID", "stime", "etime"),
  class = c("data.table", "data.frame"),
  row.names = c(NA,-6L)
)

我希望从8:30到10:00的间隔将“粘在”10:00到11:00，但事实并非如此。结果是：

   idx ID               stime               etime
1:   4  1 2014-01-15 08:00:00 2014-01-15 10:00:00
2:   3  1 2014-01-15 10:00:00 2014-01-15 11:00:00
3:   6  1 2014-01-15 11:30:00 2014-01-15 13:00:00

以下数据集提供了更全面的测试：

# The numbers represent seconds from 1970-01-01 01:00:01
dat <- structure(
  list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), 
       stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 11:30:00"),
                           as.POSIXct("2014-01-15 12:00:00"),
                           as.POSIXct("2014-01-15 07:30:00"),
                           as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 10:00:00")
                           ),
                         class = c("POSIXct", "POSIXt"), tzone = ""),
       etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 11:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"), 
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 12:30:00"),
                           as.POSIXct("2014-01-15 13:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 10:30:00"),
                           as.POSIXct("2014-01-15 11:00:00")
                           ), 
                         class = c("POSIXct", "POSIXt"), tzone = "")
  ),
  .Names = c("ID", "stime", "etime"),
  sorted = c("ID", "stime", "etime"),
  class = c("data.table", "data.frame"),
  row.names = c(NA,-6L)
)

所以我们的结果是：

   idx ID               stime               etime
1:   4  1 2014-01-15 08:00:00 2014-01-15 10:00:00
2:   3  1 2014-01-15 10:00:00 2014-01-15 11:00:00
3:   6  1 2014-01-15 11:30:00 2014-01-15 13:00:00
4:  12  2 2014-01-15 07:30:00 2014-01-15 09:30:00
5:  13  2 2014-01-15 09:00:00 2014-01-15 11:00:00

现在对于ID = 2的受访者，我们看到间隔是重叠的，但没有报告为一个间隔。正确的解决方案是：

   idx ID               stime               etime
1:   ?  1 2014-01-15 08:00:00 2014-01-15 11:00:00
3:   ?  1 2014-01-15 11:30:00 2014-01-15 13:00:00
4:  ??  2 2014-01-15 07:30:00 2014-01-15 11:00:00

更新 - 基准测试和大型数据集

我有以下数据集，大约有1000个用户，每个用户有500个持续时间，给出50万行。你可以在我的网站上下载数据集 Google云端硬盘，包括Google云端硬盘中的解决方案。

SQL Server 2014在笔记本电脑上使用8GB RAM，64位，i5-4210U CPU @ 1.70Ghz - 2.39Ghz，使用Itzik Ben-Gan在SQL中提供的解决方案大约需要5秒钟。 5秒不包括创建功能的过程。此外，不会为任何表创建任何索引。

PS：我用 library(lubridate);

这是一个非常简单的想法。按开始时间排序，然后查找结束时间的累计最大值。完成后，重叠组只是下一个开始时间仍然小于或等于当前累计最大结束时间（全部由ID完成）的重叠组：

setorder(dat, ID, stime) # ordering by ID is unnecessary, it's just prettier

dat[, etime.max := as.POSIXct(cummax(as.numeric(etime)), origin = '1970-01-01'), by = ID]

# find the grouping of intervals (1:.N hack is to avoid warnings when .N=1)
dat[, grp := cumsum(c(FALSE, stime[2:.N] > etime.max[1:(.N-1)]))[1:.N], by = ID]

dat[, .(stime = min(stime), etime = max(etime)), by = .(ID, grp)][, grp := NULL][]
#   ID               stime               etime
#1:  1 2014-01-15 08:00:00 2014-01-15 11:00:00
#2:  1 2014-01-15 11:30:00 2014-01-15 13:00:00
#3:  2 2014-01-15 07:30:00 2014-01-15 11:00:00

由于这不需要找到所有可能的重叠，因此速度非常快。在大致匹配OP描述的模拟数据集上，它对我来说是瞬时的（<0.2s）。

什么是SQL解决方案？ - eddi

亲爱的Eddi，这里有一些问题的例子： sqlmag.com/blog/...。 Itzik Ben-Gan在令人印象深刻的2秒内完成了这项工作。 - Snowflake

@alexis_laz，是的，它可以在这里工作，但POSIXct也可以有毫秒，并且失败（因为IRanges :: reduce会隐式地将其转换为整数范围）。 - Arun

这对我来说可能听起来很愚蠢，但是你能详细说明累计最大结束时间是什么意思吗？（我对data.table不是很了解，但我很想知道代码的第3行是如何工作的）。 - Snowflake

我的意思是最大结束时间到当前点。查看 ?cummax 举个例子。至于第3行，查看每个单独计算的内容（单个 ID），然后它才有意义。它也可能有助于可视化转换 etime.max 至 POSIXct （实际上，我只是编辑了这样做，使其更容易阅读）。 - eddi

谢谢eddi，我现在理解你的代码，但我想知道什么是“。（stuff）”。我认为它应该在列表中，但我找不到关于圆括号表示法的任何内容。或许我忽略了它。这似乎是一个非常优雅的解决方案，可能会有效。我将用我用于测试的完整500 000行检查并报告:)。 - Snowflake

点相当于写作 list 和imo更具可读性。我想说它是最近推出的，因为我最近开始这样做，但我不记得肯定。很酷，我期待测试:) - eddi

感谢Eddi，它比SQL更快。我得到了1秒的测试数据。 - Snowflake

问题如何解决R和性能与SQL的差距和孤岛问题？

答案:

热门问题

问题 如何解决R和性能与SQL的差距和孤岛问题？

答案:

热门问题

问题如何解决R和性能与SQL的差距和孤岛问题？