将基于外键的SQL连接转换为R data.table语法

Question

data.table 包提供了许多与 SQL 相同的表处理方法。如果一个表具有键，则该键由一个或多个列组成。但是一个表不能有多个键，因为它不能同时以两种不同的方式进行排序。

在这个例子中，X 和 Y 是具有单个关键列 "id" 的 data.table；Y 还具有非关键列 "x_id"。

   X <- data.table(id = 1:5, a=4:8,key="id")
   Y <- data.table(id = c(1,1, 3,5,7), x_id=c(1,4:1), key="id")

以下语法将根据它们的键将表连接起来：

  X[Y]

我该如何将以下SQL语法转换为data.table代码？

  select * from X join Y on X.id = Y.x_id; 

我最接近的是：

Y[X,list(id, x_id),by = x_id,nomatch=0]

然而，这并没有像SQL语句中的内连接一样执行。

---

这是一个更清晰的示例，在此示例中，外键为y_id，并且我们想要进行联接以查找X2$y_id = Y2$id的Y2值。

    X2 <- data.table(id = 1:5, y_id = c(1,1,2,2,2), key="id")
    Y2 <- data.table(id = 1:5, b = letters[1:5], key="id")

我想要生成一个表格：

   id  y_id  b
    1     1 "a"
    2     1 "a"
    3     2 "b"
    4     2 "b"
    5     2 "b"

类似于以下方法所使用的技巧：

> merge(data.frame(X2), data.frame(Y2), by.x = "y_id", by.y = "id")
  y_id id b
1    1  1 a
2    1  2 a
3    2  3 b
4    2  4 b
5    2  5 b

然而，当我这样做时：

    X2[Y2, 1:2,by = y_id]

我没有得到期望的结果：

    y_id V1
[1,]    1  1
[2,]    1  2
[3,]    2  1
[4,]    2  2

- David LeBauer

---

你可以暂时更改 X2 键并将它们设置为 "y_id"；然后执行正常的连接 Y2[X2]（或者根据方向选择 X2[Y2]），然后恢复 X2 的先前键。 - digEmAll

@digEmAll 很有用，我以为这就是 by 参数的作用...但是这是否适用于多个（>2）表连接，其中目标（左）表具有>1个外键？ - David LeBauer

以下语法将根据它们的键连接表格：X[Y] -- 我认为相应的SQL语法应该是：SELECT * FROM A NATURAL JOIN B; - onedaywhen

我编辑了问题的顶部以澄清术语。希望没问题。会回答... - Matt Dowle

@MatthewDowle 感谢您澄清术语 - 我在尝试将主键/外键翻译为data.table中使用的键时遇到了困难。现在，如果我理解正确，'key'是根据特定连接所需设置每个表的关键字。 - David LeBauer

Answer 1

问题很好。请注意以下信息（尽管有点隐蔽）在 ?data.table 中：

当 i 是一个 data.table 对象时，x 必须有一个键。使用键将 i 连接到 x，并返回与之匹配的行。在每个 i 列和每个 x 键列之间执行等值联接。匹配是在编译的 C 语言中进行二分查找，时间复杂度为 O(log n)。如果 i 的列数少于 x 的键列数，则可能会有许多 x 的行与每个 i 的行匹配。如果 i 的列数多于 x 的键列数，则不参与连接的 i 列将包含在结果中。如果 i 也有一个键，则使用 i 的键列来匹配 x 的键列，并对两个表进行二进制合并。

所以，关键在于 i 不必具有键值。只需要 x 具有键值即可。

X2 <- data.table(id = 11:15, y_id = c(14,14,11,12,12), key="id")
     id y_id
[1,] 11   14
[2,] 12   14
[3,] 13   11
[4,] 14   12
[5,] 15   12
Y2 <- data.table(id = 11:15, b = letters[1:5], key="id")
     id b
[1,] 11 a
[2,] 12 b
[3,] 13 c
[4,] 14 d
[5,] 15 e
Y2[J(X2$y_id)]  # binary search for each item of (unsorted and unkeyed) i
     id b
[1,] 14 d
[2,] 14 d
[3,] 11 a
[4,] 12 b
[5,] 12 b

或者，

Y2[SJ(X2$y_id)]  # binary merge of keyed i, see ?SJ
     id b
[1,] 11 a
[2,] 12 b
[3,] 12 b
[4,] 14 d
[5,] 14 d

identical(Y2[J(X2$y_id)], Y2[X2$y_id])
[1] FALSE

- Matt Dowle

---

谢谢。但是 identical(Y2[J(X2$y_id)], Y2[X2$y_id]) == TRUE，有什么区别吗？ - David LeBauer

@David 已经更改了示例数据以使其更清晰。先前的示例数据具有与行号相同的键值，即 1:5。 - Matt Dowle