以最有效的方式获取间隔期间的事件数
样本数据
我有一个数据。带有事件（dt）和数据的表。包含特定时间段内所有分钟数的表格（dt.minutes）。
dt <- data.table( id = 1:3,
start = c("2019-01-01 18:00:00", "2019-01-01 19:00:00", "2019-01-01 20:00:00"),
end = c("2019-01-01 21:00:00", "2019-01-01 20:15:00", "2019-01-01 20:30:00") )
dt[, c("start", "end") := lapply( .SD,
as.POSIXct,
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
.SDcols = c("start", "end")]
dt.minutes <- data.table( from = seq( from = as.POSIXct( "2019-01-01 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
to = as.POSIXct( "2019-01-05 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
by = "1 min") )
dt.minutes[, to := from + 59 ][]
setkey( dt, start, end)
setkey( dt.minutes, from, to )
看起来像这样
> dt
id start end
1: 1 2019-01-01 18:00:00 2019-01-01 21:00:00
2: 2 2019-01-01 19:00:00 2019-01-01 20:15:00
3: 3 2019-01-01 20:00:00 2019-01-01 20:30:00
> dt.minutes
from to
1: 2019-01-01 00:00:00 2019-01-01 00:00:59
2: 2019-01-01 00:01:00 2019-01-01 00:01:59
3: 2019-01-01 00:02:00 2019-01-01 00:02:59
4: 2019-01-01 00:03:00 2019-01-01 00:03:59
5: 2019-01-01 00:04:00 2019-01-01 00:04:59
---
5757: 2019-01-04 23:56:00 2019-01-04 23:56:59
5758: 2019-01-04 23:57:00 2019-01-04 23:57:59
5759: 2019-01-04 23:58:00 2019-01-04 23:58:59
5760: 2019-01-04 23:59:00 2019-01-04 23:59:59
5761: 2019-01-05 00:00:00 2019-01-05 00:00:59
问题
对于dt中的每一行（=分钟）。分钟，我想知道在这一分钟内发生了多少来自dt的事件。
我可以得出两个可能的数据。表3：解决方案：
setkey( dt, start, end)
setkey( dt.minutes, from, to )
#method 1: non-equi join
ans1 <- dt.minutes[ dt.minutes, N := {
num = dt[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ][]
#method 2: use foverlaps, summarise on `from` and then update-join
ans2 <- dt.minutes[, N:=0L][ foverlaps( dt, copy(dt.minutes) )[, .(N =.N), by = .(from)], N := i.N, on = .(from)]
这两种方法都有效，并提供了我需要的答案
all.equal( ans1, ans2 )
# [1] TRUE
但是当我看基准时，foveraps（）以压倒性优势获胜...
# Unit: milliseconds
# expr min lq mean median uq max neval
# non_equi_join 2074.0594 2097.3363 2111.87762 2100.1306 2116.6965 2171.1653 5
# foverlaps 10.5716 10.8999 10.93622 10.9011 10.9479 11.3606 5
#
microbenchmark::microbenchmark(
non_equi_join = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT2, from, to )
DT2[ DT2, N := {
num = DT[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ][]
},
foverlaps = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := 0L][ foverlaps( DT, copy(DT2) )[, .( N = .N ), by = .(from)], N := i.N, on = .(from)]
}, times = 5L
)
问题:
为了更好地理解data.table连接，我正在寻找为什么我的连接（ans1）花费了这么长时间（200倍慢），而不是f重叠（）（ans2）。
有没有办法提高连接的性能？或者，foverlaps（）仅仅是这项工作的优化工具？
或者有没有更快的方法来实现我的目标？
共有1个答案
首先，我不确定foverlaps的默认类型是否是您想要的。例如：
> foverlaps(dt.minutes, dt)[1368]
id start end from to
1: 1 2019-01-01 18:00:00 2019-01-01 21:00:00 2019-01-01 21:00:00 2019-01-01 21:00:59
它的行为确实像留档指定的那样，但它似乎不是您想要的（。id应该是NA）。您可能需要type="in"
我不熟悉数据的内部结构。表，所以下面的一些猜测是有根据的。
当使用by=时，关于在连接时进行总结的事情。EACHI是为了优化内存使用，而不是速度。如果连接中的每个结果组都非常大，那么每次只实现其中的一部分可能是值得的，但是传递给j的任何代码都是R代码（通常，请参阅下面的注释），即不是编译代码。连接的基本代码可能完全用C计算，但是如果使用by=。在EACHI中，为连接找到匹配的行可能会很快，但是计算j本质上成为R中跨组的循环，如果有很多小组，相关的时间开销会增加（就像你的问题一样）。
我想出了另外两个选择（并稍微修改了设置），我系统中的基准看起来像这样：
library(data.table)
dt <- data.table( id = 1:3,
start = c("2019-01-01 18:00:00", "2019-01-01 19:00:00", "2019-01-01 20:00:00"),
end = c("2019-01-01 21:00:00", "2019-01-01 20:15:00", "2019-01-01 20:30:00") )
dt[, c("start", "end") := lapply( .SD,
as.POSIXct,
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
.SDcols = c("start", "end")]
dt.minutes <- data.table( from = seq( from = as.POSIXct( "2019-01-01 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
to = as.POSIXct( "2019-01-05 00:00:00",
format = "%Y-%m-%d %H:%M:%S",
tz = "Europe/Amsterdam"),
by = "1 min") )
dt.minutes[, to := from + 59 ]
library(microbenchmark)
microbenchmark::microbenchmark(
times = 5L,
non_equi_join = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[ DT2, N := {
num = DT[ start <= i.to & end >= i.from ]
list( nrow(num) )
}, by = .EACHI ]
},
foverlaps = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := 0L][ foverlaps( DT, copy(DT2) )[, .( N = .N ), by = .(from)], N := i.N, on = .(from)]
},
nej = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := DT[.SD, .(id, start), on = .(start <= from, end >= to), allow.cartesian = TRUE
][, sum(!is.na(id)), by = "start"]$V1]
},
fo = {
DT <- copy(dt)
DT2 <- copy(dt.minutes)
setkey( DT, start, end)
setkey( DT2, from, to )
DT2[, N := foverlaps(DT2, DT, type="within", which=TRUE)[, sum(!is.na(yid)), by="xid"]$V1]
}
)
Unit: milliseconds
expr min lq mean median uq max neval
non_equi_join 2506.3448 2535.3132 2597.71440 2565.4727 2647.7538 2733.6875 5
foverlaps 13.8878 14.3945 14.66726 14.9400 15.0491 15.0649 5
nej 11.6391 12.0179 13.89408 13.2644 13.3602 19.1888 5
fo 11.4082 12.7889 13.77820 12.9216 13.0430 18.7293 5
*我的版本的结果与您的不匹配，因为我在开头提到了关于类型的内容。
我们可以看到，它们并不比你所拥有的快多少，但是值得注意的有趣的事情是nej版本。也使用非equi连接，但不使用by=。EACHI。连接的整个结果首先被具体化，然后我们才聚合结果，这在本例中更快。不幸的是，我不能确切地告诉你为什么（同样，不熟悉内部），但是一般的经验法则应该是by=。EACHI仅当您期望结果中没有大的组，或者如果j中的代码可以通过data.table进行优化时才应使用。
顺便说一句，在fo版本中，我使用which=TRUE来避免从联接返回所有列，只返回索引。因为条目的数量很重要，所以使用匹配项返回索引的工作原理类似。在这种情况下，这并没有造成很大的区别。
*请注意，foverlaps”文档中提到通常应在x中提供较大的表格。
编辑：弗兰克的版本似乎是最快的：
dt.minutes[, n := dt[.SD, on=.(start <= from, end >= to), allow.cartesian=TRUE, .N, by=.EACHI]$N]
-
问题内容： 在MS SQL 2000和2005中，给定日期时间（例如‘2008-09-25 12:34:56’），获取仅包含‘2008-09-25’的日期时间的最有效方法是什么？ 问题答案： 我必须承认，我以前从未见过Matt展示的下浮率转换。我不得不对此进行测试。 我测试了一个纯选择（它将返回日期和时间，而这不是我们想要的），这里是统治性解决方案（floor-float），这里提到了一个普通的“
-
问题内容： 我有一个包含2个字段的表：唯一ID，用户ID（外键）和日期时间。这是对服务的访问日志。我在SQL Server中工作，但我希望得到不可知论的答案。 我想使用SQL为最长间隔开始的特定用户查找ID。 因此，举例来说，假设我的值如下（为一位用户简化）： 如果我搜索用户1的最长间隔，我将得到ID 2（也可以在那儿获得间隔的长度，但不那么关键）。 在SQL中最有效的方法是什么？ 注意：ID不一
-
问题内容： 在sql server中查询表时，im试图仅获取记录的当前页面。但是，我需要为特定查询返回的记录总数，以计算页面数。如何有效地做到这一点，而无需编写另一个查询来对记录进行计数。 问题答案： 如果您添加第二种排序方式而不是，则可以通过将两个行计数加在一起再减去一个来计算记录总数。rsult集中的任何行都将具有正确的总行数：
-
问题内容： 我有两个LocalDate，它们代表某个时间间隔。现在，我必须获取此间隔包含的所有星期五的LocalDates。最简单的方法吗？ 问题答案： 解决方案：懒惰地走一个星期。 测试
-
问题内容： 我目前有一个数据库，该数据库有超过600万行并且还在不断增长。我目前正在SELECT COUNT（id）FROM table; 为了向我的用户显示数字，但是数据库越来越大，除了能够显示数字之外，我不需要存储所有这些行。有没有办法选择要显示的auto_increment值，以便可以清除数据库中的大多数行？使用似乎无效。 问题答案： 如果仅是要获取记录（行）数，则建议使用： （至少对于My