Traducción de uniones SQL en claves externas a sintaxis de datos R

data.table

Question

El paquete data.table proporciona muchos de los mismos métodos de manejo de tablas que SQL. Si una tabla tiene una clave, esa clave consiste en una o más columnas. Pero una tabla no puede tener más de una clave, porque no se puede clasificar de dos maneras diferentes al mismo tiempo.

En este ejemplo, X e Y son data.table con una columna de clave única "id"; Y también tiene una columna no clave "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")

La siguiente sintaxis uniría las tablas en sus claves:

  X[Y]

¿Cómo puedo traducir la siguiente sintaxis SQL al código data.table?

  select * from X join Y on X.id = Y.x_id; 

Lo más cerca que he conseguido es:

Y[X,list(id, x_id),by = x_id,nomatch=0]

Sin embargo, esto no hace la misma unión interna que la instrucción SQL.

Aquí hay un ejemplo más claro en el que la clave externa es y_id, y queremos que la unión busque los valores de Y2, donde 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")

Me gustaría producir la tabla:

   id  y_id  b
    1     1 "a"
    2     1 "a"
    3     2 "b"
    4     2 "b"
    5     2 "b"

similar a lo que hace el siguiente 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

Sin embargo, cuando hago esto:

    X2[Y2, 1:2,by = y_id]

No obtengo el resultado deseado:

    y_id V1
[1,]    1  1
[2,]    1  2
[3,]    2  1
[4,]    2  2

Answer 1

Buena pregunta. Tenga en cuenta lo siguiente (es decir, enterrado) en la ?data.table :

Cuando i es una data.table , x debe tener una clave. i se une a x utilizando la clave y se devuelven las filas en x que coinciden. Se realiza una unión equitativa entre cada columna en i para cada columna en la clave de x . La coincidencia es una búsqueda binaria en C compilada en tiempo O (log n). Si i tiene menos columnas que la clave de x , entonces muchas filas de x pueden coincidir con cada fila de i . Si i tiene más columnas que la clave de x , las columnas de i no participan en la unión se incluyen en el resultado. Si también tengo una clave , son las columnas clave que se utilizan para coincidir con las columnas clave de x y se realiza una combinación binaria de las dos tablas.

Entonces, la clave aquí es que no tengo que ser ingresado. Sólo x debe estar en clave.

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

o,

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