数据表中的筛选笛卡尔积

Question

我正在尝试使用data.table调用来替换SQL生成的笛卡尔积。 我有一个包含资产和价值的大型历史记录，我需要其中所有组合的子集。 假设我有表a，其中T = [日期，合同，价值]。在SQL中，它看起来像：

SELECT a.date, a.contract, a.value, b.contract. b.value 
FROM T a, T b
WHERE a.date = b.date AND a.contract <> b.contract AND a.value + b.value < 4

在R中，我现在有以下内容：

library(data.table)

n <- 1500
dt <- data.table(date     = rep(seq(Sys.Date() - n+1, Sys.Date(), by = "1 day"), 3),
                 contract = c(rep("a", n), rep("b", n), rep("c", n)),
                 value    = c(rep(1, n), rep(2, n), rep(3, n)))
setkey(dt, date)

dt[dt, allow.cartesian = TRUE][(contract != i.contract) & (value + i.value < 4)]

我认为我的解决方案首先创建所有组合（在这种情况下有13,500行），然后进行过滤（到3000）。然而，SQL（我可能错了）加入子集，更重要的是不会将所有组合加载到RAM中。有什么办法可以更有效地使用data.table吗？

- kismsu

Answer 1

使用by = .EACHI功能。在data.table中，连接和子集非常紧密相关；即，使用data.table进行连接只是另一种子集 - 而不是通常的整数 / 逻辑 / 行名称。它们是这样设计的，考虑到这些情况。

基于子集的连接允许在连接时同时合并j-表达式和分组操作。

require(data.table)
dt[dt, .SD[contract != i.contract & value + i.value < 4L], by = .EACHI, allow = TRUE]

这是一种惯用的方式（如果您只想在条件中使用 i.* 列，而不返回它们），但是.SD尚未优化，并且对于每个组评估j表达式会很昂贵。

system.time(dt[dt, .SD[contract != i.contract & value + i.value < 4L], by = .EACHI, allow = TRUE])
#    user  system elapsed 
#   2.874   0.020   2.983 

一些使用.SD的情况已经被优化了。 在这些情况得到解决之前，您可以通过以下方式解决：

dt[dt, {
        idx = contract != i.contract & value + i.value < 4L
        list(contract = contract[idx],
             value = value[idx], 
             i.contract = i.contract[any(idx)],
             i.value = i.value[any(idx)]
        )
       }, by = .EACHI, allow = TRUE]

这需要 0.045 秒，而你的方法只需要 0.005 秒。但是 by = .EACHI 每次都会评估 j 表达式（因此更节省内存）。这就是你必须接受的权衡。

- Arun

Answer 2

从版本v1.9.8（于2016年11月25日在CRAN上）开始，data.table可以实现非等值连接，这可以应用于此处。

此外，OP的方法会创建“对称重复”（a，b）和（b，a）。避免重复会减少结果集的大小，而不会丢失信息（请参见?combn）。

如果这是OP的意图，我们可以使用非等值连接来避免这些对称重复：

library(data.table)
dt[, rn := .I][dt, on = .(date, rn < rn), nomatch = 0L][value + i.value < 4]

这提供了

            date contract value   rn i.contract i.value
   1: 2013-09-24        a     1 1501          b       2
   2: 2013-09-25        a     1 1502          b       2
   3: 2013-09-26        a     1 1503          b       2
   4: 2013-09-27        a     1 1504          b       2
   5: 2013-09-28        a     1 1505          b       2
  ---                                                  
1496: 2017-10-28        a     1 2996          b       2
1497: 2017-10-29        a     1 2997          b       2
1498: 2017-10-30        a     1 2998          b       2
1499: 2017-10-31        a     1 2999          b       2
1500: 2017-11-01        a     1 3000          b       2

与使用 OP 的代码得到的结果相反

            date contract value i.contract i.value
   1: 2013-09-24        b     2          a       1
   2: 2013-09-24        a     1          b       2
   3: 2013-09-25        b     2          a       1
   4: 2013-09-25        a     1          b       2
   5: 2013-09-26        b     2          a       1
  ---                                             
2996: 2017-10-30        a     1          b       2
2997: 2017-10-31        b     2          a       1
2998: 2017-10-31        a     1          b       2
2999: 2017-11-01        b     2          a       1
3000: 2017-11-01        a     1          b       2

下一步是进一步减少创建的需要在之后进行过滤的配对数：

dt[, val4 := 4 - value][dt, on = .(date, rn < rn, val4 > value), nomatch = 0L]

它返回与上面相同的结果。

请注意，过滤条件value + i.value < 4被另一个连接条件val4 > value替换，其中val4是一个特别创建的辅助列。

基准测试

对于一个基准测试案例n <- 150000L，在dt中产生450k行，时间如下：

n <- 150000L
dt <- data.table(date     = rep(seq(Sys.Date() - n+1, Sys.Date(), by = "1 day"), 3),
                 contract = c(rep("a", n), rep("b", n), rep("c", n)),
                 value    = c(rep(1, n), rep(2, n), rep(3, n)))

dt0 <- copy(dt)
microbenchmark::microbenchmark(
  OP = {
    dt <- copy(dt0)
    dt[dt, on = .(date), allow.cartesian = TRUE][
      (contract != i.contract) & (value + i.value < 4)]
  },
  nej1 = {
    dt <- copy(dt0)
    dt[, rn := .I][dt, on = .(date, rn < rn), nomatch = 0L][value + i.value < 4]
  },
  nej2 = {
    dt <- copy(dt0)
    dt[, rn := .I][, val4 := 4 - value][dt, on = .(date, rn < rn, val4 > value), nomatch = 0L]
  },
  times = 20L
)

Unit: milliseconds
 expr      min       lq     mean   median       uq      max neval cld
   OP 136.3091 143.1656 246.7349 298.8648 304.8166 311.1141    20   b
 nej1 127.9487 133.1772 160.8096 136.0825 146.0947 298.3348    20  a 
 nej2 180.4189 183.9264 219.5171 185.9385 198.7846 351.3038    20   b

因此，在连接之后执行检查value + i.value < 4似乎比在非等值连接中包含它更快。

- Uwe