Перевод SQL-соединений по внешним ключам в синтаксис R data.table.

Question

data.table Пакет предоставляет много тех же методов обработки таблиц, что и SQL. Если таблица имеет ключ, этот ключ состоит из одного или нескольких столбцов. Но таблица не может иметь более одного ключа, потому что она не может быть отсортирована двумя разными способами одновременно.

В этом примере X а также Y являются data.table s с одним ключевым столбцом "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

Answer 1

Хороший вопрос. Обратите внимание на следующее (по общему признанию похоронен) в ?data.table:

когда i это data.table, x должен иметь ключ. i присоединяется к x используя ключ и строки в 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