Перевод 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

Ответы

Ответ 1

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

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