tables - sql server join




Pourquoi la jointure X[Y] de data.tables n'autorise-t-elle pas une jointure externe complète ou une jointure à gauche? (2)

Cette "réponse" est une proposition de discussion: Comme indiqué dans mon commentaire, je suggère d'ajouter un paramètre de join à [.data.table () pour activer d'autres types de jointures, par exemple: X[Y,j,join=string] . En plus des 4 types de jointures ordinaires, je suggère également de supporter 3 types de jointures exclusives , et la jointure croisée .

Les valeurs de chaîne de join (et les alias) pour les différents types de jointure sont proposées comme suit:

  1. "all.y" et "right" - jointure à droite, le paramètre data.table présent par défaut (nomatch = NA) - toutes les lignes Y avec NAs où il n'y a pas de correspondance avec X;
  2. "both" et "inner" - jointure interne (nomatch = 0) - uniquement les lignes où X et Y correspondent;

  3. "all.x" et "left" - jointure à gauche - toutes les lignes de X, NAs où Y ne correspond pas:

  4. "outer" et "full" - jointure externe complète - toutes les lignes de X et Y, NAs où aucune correspondance

  5. "only.x" et "not.y" - non-jointure ou anti-jointure retournant X lignes où il n'y a pas de correspondance Y

  6. "only.y" et "not.x" - non-jointure ou anti-jointure retournant les lignes Y où il n'y a pas de correspondance avec X
  7. "not.both" - jointure exclusive retournant les lignes X et Y où il n'y a pas de correspondance avec l'autre table, c'est-à-dire un exclusif-ou (XOR)
  8. "cross" - jointure croisée ou produit cartésien avec chaque ligne de X correspondant à chaque ligne de Y

La valeur par défaut est join="all.y" qui correspond à la valeur par défaut actuelle.

Les valeurs de chaîne "all", "all.x" et "all.y" correspondent aux paramètres merge() . Les chaînes "right", "left", "inner" et "outer" peuvent être plus adaptées aux utilisateurs SQL.

Les chaînes "both" et "not.both" sont ma meilleure suggestion en ce moment - mais quelqu'un peut avoir de meilleures suggestions de chaînes pour la jointure interne et la jointure exclusive. (Je ne suis pas sûr si "exclusif" est la bonne terminologie, corrigez-moi s'il y a un terme approprié pour une jointure "XOR".)

L'utilisation de join="not.y" est une alternative pour X[-Y,j] ou X[!Y,j] syntaxe de non-jointure et peut-être plus claire (pour moi), même si je ne suis pas sûr qu'ils soient la même chose (nouvelle fonctionnalité dans data.table version 1.8.3).

La jointure croisée peut parfois être utile, mais elle peut ne pas tenir dans le paradigme data.table.

C'est un peu une question philosophique à propos de la syntaxe de jointure data.table. Je trouve de plus en plus d'utilisations pour data.tables, mais j'apprends toujours ...

Le format de jointure X[Y] pour data.tables est très concis, maniable et efficace, mais autant que je sache, il ne supporte que les jointures internes et les jointures externes droites. Pour obtenir une jointure externe gauche ou complète, j'ai besoin d'utiliser la merge :

  • X[Y, nomatch = NA] - toutes les lignes dans Y - jointure externe droite (par défaut)
  • X[Y, nomatch = 0] - uniquement les lignes avec des correspondances dans X et Y - jointure interne
  • merge(X, Y, all = TRUE) - toutes les lignes de X et Y - jointure externe complète
  • merge(X, Y, all.x = TRUE) - toutes les lignes dans X - jointure externe gauche

Il me semble que ce serait pratique si le format de jointure X[Y] supportait les 4 types de jointures. Y a-t-il une raison pour que deux types de jointures soient supportés?

Pour moi, les valeurs des paramètres nomatch = 0 et nomatch = NA ne sont pas très intuitives pour les actions en cours. Il m'est plus facile de comprendre et de mémoriser la syntaxe de merge : all = TRUE , all.x = TRUE et all.y = TRUE . Puisque l'opération X[Y] ressemble beaucoup plus à la merge qu'à la match , pourquoi ne pas utiliser la syntaxe de merge pour les jointures plutôt que le paramètre nomatch la fonction de nomatch ?

Voici des exemples de code des 4 types de jointure:

# sample X and Y data.tables
library(data.table)
X <- data.table(t = 1:4, a = (1:4)^2)
setkey(X, t)
X
#    t  a
# 1: 1  1
# 2: 2  4
# 3: 3  9
# 4: 4 16

Y <- data.table(t = 3:6, b = (3:6)^2)
setkey(Y, t)
Y
#    t  b
# 1: 3  9
# 2: 4 16
# 3: 5 25
# 4: 6 36

# all rows from Y - right outer join
X[Y]  # default
#  t  a  b
# 1: 3  9  9
# 2: 4 16 16
# 3: 5 NA 25
# 4: 6 NA 36

X[Y, nomatch = NA]  # same as above
#    t  a  b
# 1: 3  9  9
# 2: 4 16 16
# 3: 5 NA 25
# 4: 6 NA 36

merge(X, Y, by = "t", all.y = TRUE)  # same as above
#    t  a  b
# 1: 3  9  9
# 2: 4 16 16
# 3: 5 NA 25
# 4: 6 NA 36

identical(X[Y], merge(X, Y, by = "t", all.y = TRUE))
# [1] TRUE

# only rows in both X and Y - inner join
X[Y, nomatch = 0]  
#    t  a  b
# 1: 3  9  9
# 2: 4 16 16

merge(X, Y, by = "t")  # same as above
#    t  a  b
# 1: 3  9  9
# 2: 4 16 16

merge(X, Y, by = "t", all = FALSE)  # same as above
#    t  a  b
# 1: 3  9  9
# 2: 4 16 16

identical( X[Y, nomatch = 0], merge(X, Y, by = "t", all = FALSE) )
# [1] TRUE

# all rows from X - left outer join
merge(X, Y, by = "t", all.x = TRUE)
#    t  a  b
# 1: 1  1 NA
# 2: 2  4 NA
# 3: 3  9  9
# 4: 4 16 16

# all rows from both X and Y - full outer join
merge(X, Y, by = "t", all = TRUE)
#    t  a  b
# 1: 1  1 NA
# 2: 2  4 NA
# 3: 3  9  9
# 4: 4 16 16
# 5: 5 NA 25
# 6: 6 NA 36

Mise à jour: data.table v1.9.6 a introduit la syntaxe on= , qui permet des jointures ad hoc sur des champs autres que la clé primaire. La réponse de jangorecki à la question Comment joindre (fusionner) des trames de données (interne, externe, gauche, droite)? fournit des exemples de types de jointure supplémentaires que data.table peut gérer.


La réponse de @ mnel est sur place, alors acceptez cette réponse. C'est juste un suivi, trop long pour les commentaires.

Comme le dit mnel, la jointure gauche / droite est obtenue en échangeant Y et X : Y[X] -vs- X[Y] . Donc 3 des 4 types de jointure sont supportés dans cette syntaxe, pas 2, iiuc.

Ajouter le 4ème semble une bonne idée. Disons que nous ajoutons full=TRUE ou both=TRUE ou merge=TRUE (pas sûr du meilleur nom d'argument?) Alors il ne m'est pas venu à l'esprit que X[Y,j,merge=TRUE] serait utile pour les raisons après le MAIS dans la FAQ 1.12. Nouvelle demande de fonctionnalité ajoutée et reliée ici, merci:

FR # 2301: Ajouter merge = VRAI argument pour X [Y] et Y [X] joindre comme merge ().

Les versions récentes ont accéléré merge.data.table (en prenant une copie superficielle en interne pour définir les clés plus efficacement, par exemple). Nous essayons donc de rapprocher merge() et X[Y] , et de fournir toutes les options à l'utilisateur pour une flexibilité totale. Il y a des avantages et des inconvénients des deux. Une autre demande de fonctionnalité exceptionnelle est:

FR # 2033: Ajouter by.x et by.y à merge.data.table

S'il y en a d'autres, continuez s'il vous plaît.

Par cette partie de la question:

Pourquoi ne pas utiliser la syntaxe de fusion pour les jointures plutôt que le paramètre nomatch de la fonction de correspondance?

Si vous préférez la syntaxe merge() et ses 3 arguments all , all.x et all.y utilisez simplement cela à la place de X[Y] . Je pense qu'il devrait couvrir tous les cas. Ou voulez-vous dire pourquoi l'argument est-il un nomatch unique dans [.data.table ? Si c'est le cas, c'est juste la manière qui semblait naturelle étant donné FAQ 2.14: "Pouvez-vous expliquer pourquoi data.table est inspiré par la syntaxe A [B] dans la base?". Mais aussi, nomatch ne prend que deux valeurs actuellement 0 et NA . Cela pourrait être étendu de sorte qu'une valeur négative signifie quelque chose, ou 12 signifierait utiliser les valeurs de la 12e rangée pour remplir les NA, par exemple, ou nomatch dans le futur pourrait être un vecteur ou même lui-même une data.table .

Hm. Comment by-by-by-by interagirait avec merge = TRUE? Peut-être devrions-nous passer à l' datatable-help .





data.table