将外键上的SQL连接转换为R data.table语法
data.table包提供了许多与SQL相同的表处理方法。 如果一个表有一个键，那么这个键由一个或多个列组成。 但是一个表不能有多个键，因为它不能同时以两种不同的方式排序。
在这个例子中， X和Y是data.table的单个键列“id”; 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，我们希望连接查找Y2的值，其中X2$y_id = Y2$id 。
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"
类似于以下kludge所做的事情：
> 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
好问题。 请注意以下（被承认掩埋）在?data.table ：
当i是data.table ， x必须有一个键。 i使用键将x连接到x ，并返回匹配的x中的行。 在i的每一列与x的每一列之间执行等连接。 该匹配是在O（log n）时间内编译的C中的二进制搜索。 如果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
上一篇: Translating SQL joins on foreign keys to R data.table syntax