sql - 有效地合并匹配一个变量或另一个变量的两个数据集

Question

我有两个大型数据集，d1 和 d2，我想根据 EITHER 变量 idA 和 的匹配来合并它们>idB。

两者都存在于两个数据集中，但都可能包含错误和缺失值 (NA)，或者让 idA 和 idB 指向另一个数据集中的不同观察值。 Se 下面的例子。

下面列出了期望的结果。基本上，匹配是在 idA 或 idB 上进行的，在重复匹配的情况下，应优先考虑 idA 匹配而不是 idB 匹配。

实际数据集非常大(约 1000 万)。

在 R 中是否有有效的方法来实现它？

另外，SQL 中是否有这种连接的技术术语？

library(tidyverse)
library(data.table)

d1 <- read.table(text=
"idA idB value1
A 10 500
B 1  111
C 4  234
D NA 400
E 7  500
NA 3 700
Z 5  543
Q 9  567
U 23 723
", 
header=T) %>% data.table


d2 <- read.table(text=
"idA idB value2
A 10 11
B 1  12
L 21 15
D 15 12
E 8  17
M 3  18
N 5  13
Z 25 17
Q 23 12
",
header=T) %>% data.table

期望的结果是:

Out <- read.table(text=
"d1.idA d2.idA d1.idB d2.idB d1.v1 d2.v2
A  A  10 10 500 11 # matched on idA and idB
B  B  1  1  111 12 # matched on idA and idB
D  D  NA 15 400 12 # matched on idA. d2.idB had NAs 
E  E  7  8  500 17 # matched on idA. idB had divergent values
NA M  3  3  700 18 # matched on idB. d1.idA had NAs
Z  Z  5  25 543 13 # d1[7,] matched to d2[8,] on idA and d2[9,] on idB. Priority given to idA match. 
Q  Q  9  23 657 17 #  d2[9,] matched to d1[8,] on idA and d1[9,] on idB. Priority given to idA match.
",
header=T) %>% data.table

#Non matched rows
# d1[3,]
# d2[3,]

编辑1:

• 添加了期望的结果
• 保留 data.table(read.table) 是为了方便读者解析，实际数据来自fread(file)

EDIT2:从期望的结果中删除不匹配的行

Answer 1

我不知道实现所需结果的优雅方法(我也不知道 SQL 中此类操作的技术术语)。

因此，我建议分四步完成:

1. 内联idA和idB上的两个data.tables，确定每个data.tables中剩余的行。
2. 在 idA 上内部连接两个 data.tables 的剩余行，再次确定剩余行。
3. 在 idB 上内部连接两个 data.tables 的剩余行。
4. 合并前面步骤的结果。

所有 4 个步骤的代码:

library(data.table)

# create index column in both data.tables
d1[, idx := .I]
d2[, idx := .I]

# inner join on idA and idB
j1 <- d1[d2, .(idx, i.idx), on = c("idA", "idB"), nomatch = 0L]
m1 <- unique(j1$idx)
m2 <- unique(j1$i.idx)

# inner join on idA
j2 <- d1[!(idx %in% m1)][d2[!(idx %in% m2)], .(idx, i.idx), on = c("idA"), nomatch = 0L]
m1 <- append(m1, unique(j2$idx))
m2 <- append(m2, unique(j2$i.idx))

# inner join on idB
j3 <- d1[!(idx %in% m1)][d2[!(idx %in% m2)], .(idx, i.idx), on = c("idB"), nomatch = 0L]
m1 <- append(m1, unique(j3$idx))
m2 <- append(m2, unique(j3$i.idx))

# combine results
rbindlist(
  list(
    AB = cbind(
      d1[idx %in% j1[, idx]],
      d2[idx %in% j1[, i.idx]]),
    A. = cbind(
      d1[idx %in% j2[, idx]],
      d2[idx %in% j2[, i.idx]]),
    .B = cbind(
      d1[idx %in% j3[, idx]],
      d2[idx %in% j3[, i.idx]])),
  fill = TRUE, 
  idcol = "match_on")

产生

#   match_on idA idB value1 idx idA idB value2 idx
#1:       AB   A  10    500   1   A  10     11   1
#2:       AB   B   1    111   2   B   1     12   2
#3:       A.   D  NA    400   4   D  15     12   4
#4:       A.   E   7    500   5   E   8     17   5
#5:       A.   Z   5    543   7   Z  25     17   8
#6:       A.   Q   9    567   8   Q  23     12   9
#7:       .B  NA   3    700   6   M   3     18   6

m1和m2用于记住d1和d2中行的行id， resp.，它已经在之前的一个连接操作中使用过。

因此，最终可以打印 d1 和 d2 中未找到匹配项的剩余行:

d1[!(idx %in% m1)]
#   idA idB value1 idx
#1:   C   4    234   3
#2:   U  23    723   9
d2[!(idx %in% m2)]
#   idA idB value2 idx
#1:   L  21     15   3
#2:   N   5     13   7   

请注意，在每个连接操作中，只保留行索引而不是保留所有列。不同连接操作的结果在列的名称和位置上有所不同。

在最后的合并步骤中，使用这些索引选择原始数据表 d1 和 d2 的行，以生成外观统一的结果表。