使用data.table，等同于SQL中的笛卡尔积查询

Question

有没有一种方法可以在data.table中获得与以下SQL查询相当的结果？

create C as select * from R,P where 
P.x between R.min_x and R.max_x and P.var2 < R.col3

我的问题是我无法得到R,P的笛卡尔积，因为R会崩溃，我可以接受任何技术（即使需要分步骤进行...）。通常的大小为R 1K行，P 3M行。

library(data.table)
R = data.table(min_x=c(.6,.4,.01,.8),max_x=c(.7,.51,.05,.95),col3=c(.6,.4,1.2,.6))
P = data.table(x=seq(.1,.9,.1),var2=c(1,.4,.3,.2,0,.5,.65,.7,0))
setkey(P, x)
setkey(R,min_x,max_x) #and max_x is always > min_x

#R
#   min_x max_x col3
#1:  0.01  0.05  1.2
#2:  0.40  0.51  0.4
#3:  0.60  0.70  0.6
#4:  0.80  0.95  0.6

#B
#      x var2
#1:  0.1 1.00 => var1 not in any [col1,col2]
#2:  0.2 0.40 => same
#3:  0.3 0.30 => same
#4:  0.4 0.20 => .4 in [.4,.51] but .2 < .4 so NO
#5:  0.5    0 => same 
#6:  0.6 0.50 => .6 in [.6, .7] but .5 < .6 so NO
#7:  0.7 0.65 => .6 in [.6, .7] AND .65 > .6 => SELECTED
#8:  0.8 0.70 => YES
#9:  0.9    0 => NO

所期望的结果

#  min_x max_x  col3    x var2
#1: 0.60  0.70  0.6  0.70 0.65 
#2: 0.80  0.95  0.6  0.80 0.70

- statquant

---

如果您能用一个例子解释一下这个程序的作用，我会尝试着去理解它。 - Arun

通常，示例是指您提供的可重现的示例和样本数据。 - joran

Answer 1

当这个FR被实施时（并且它的链接可能很有用）：

FR#203 允许使用2列来指定i的范围，而不是%between%

可能会是：

setkey(B, var1, var2)
B[A[,list(.(col1,col2),.(-Inf,col3))], j]

如果这听起来不错？您需要指定一个j，它将针对每个组（每个i的行）运行，以便在内存中保存潜在的笛卡尔扩展。但是，如果您确实想要返回大表格，则可以设置allow.cartesian标志：

B[A[,list(.(col1,col2),.(-Inf,col3))], allow.cartesian=TRUE]

当然现在还不能做到这一点，所以这只是一个探索性答案。

- Matt Dowle

---

我对语法很确定，因为在这种情况下，var1和var2与(col1，col2)和（-Inf，col3）进行比较，所以您可以使用key知道正在比较什么，但是如果您有一个变量的多个比较呢？（我猜您总是可以简化...） - statquant

在我的情况下，为了完成任务，您认为我应该编写一个具有B作为全局变量的函数，并从中提取每行A的正确行吗？=> 我会检查链接 - statquant

@statquant，链接的问答中有一些提示。关于语法的确如此。data.table目前还没有达到声明性语法，它目前是命令式的。也许有一天... - Matt Dowle

Answer 2

在@Matthew Dowle的帮助下，我最终得到了一个答案，来自于这篇文章

setkey(P,x)
# sort by x and mark as sorted so future queries can use binary search on P
# Lookup each min_x in the key of P, returning the location. J stands for Join.
from = P[J(R$min_x), roll=-Inf, mult='first', which=TRUE]
# Lookup each max_x in the key of P, returning the location.
to = P[J(R$max_x),roll=Inf, mult='last', which=TRUE]
# vectorized for each item the length to[i]-from[i]+1
len = to-from+1
#get NA that can occur if no x > min_x  
isNaFromTo = !is.na(from) & !is.na(to)
#remove the NA from from/to
to = to[isNaFromTo]
from = from[isNaFromTo]
#replace NA by 0 in len which will flag the fact that we want to remove the line from R
len[!isNaFromTo] = 0;
# create index for P
i = unlist(mapply("seq.int",from,to,SIMPLIFY=FALSE))
# create index of R 
j = rep(1:nrow(R), len);
#bind to get the result
res = cbind(R[j], P[i]) 
res = res[var2>col3]

结果如预期

   min_x max_x col3   x var2
1:   0.6  0.70  0.6 0.7 0.65
2:   0.8  0.95  0.6 0.8 0.70

- statquant