Jointures de tables avec le Left-By

Jointures de tables avec le By-At










Accueil » Ressources » Ici

Il y a dans Envision un mécanisme implicite qui permet les jointures entre tables contenant un champ Id (index) et qui est souvent utilisé pour obtenir un historique des ventes ou des achats. Cependant, ce mécanisme implicite — appelé « jointure naturelle » — ne convient pas aux situations plus complexes. Ainsi, Envision offre un mécanisme de jointure plus général, le « by-at ». Ce dernier est un type d' agrégation spécial qui permet d'effectuer des jointures arbitraires de tables.

Un script en exemple

Dans la présente page, nous utilisons l'échantillon de données disponible sous /sample dans votre compte Lokad. Si ce n'est déjà fait, nous vous suggérons de tout d'abord parcourir l'article sur la lecture de fichiers avec Envision et notamment la partie sur les types de fichiers attendus qui est utile pour comprendre ce qui suit.

read "/sample/Lokad_Items.tsv"
read "/sample/Lokad_Orders.tsv" as O
read "/sample/Lokad_Suppliers.tsv" as S[*]

Moq = same(S.Moq) by S.Supplier at Supplier
show table "Item list" a1d4 tomato with
Id
Name
Supplier
Moq

Les trois premières lignes correspondent aux instructions de lecture de fichier habituelles. À la ligne 3, S[*] indique que la table des types n'attend aucun type spécifique — c'est une table indépendante sans clé primaire ni clé étrangère. En effet, le fichier Lokad_Suppliers.tsv – à la différence de tous les autres fichiers .tsv du répertoire — ne contient pas de colonne Id. Par conséquent, la jointure implicite de cette table avec la table Items est impossible. En réalité, la table des fournisseurs est agrégée « par fournisseur », avec un fournisseur par ligne, tandis que tous les autres fichiers sont organisés « par article ».

À la ligne 5, nous utilisons l'agrégation « by-at » (appelée ainsi à cause des mots-clés by et at) pour effectuer une jointure entre les tables Items et S. Nous aurions pu écrire :
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier
Selon les conventions Envision habituelles, le nom de la table Items n'est pas indiqué.

Dans cet exemple, nous avons renseigné le vecteur Items.Moq avec les données obtenues de la table S. Une jointure a été effectuée entre les deux champs Items.Supplier et S.Supplier.

Si un fournisseur de la table Items n'a pas de contrepartie dans la table S, la valeur zéro est utilisée. Cependant, avec mot clé optionnel or, d'autres valeurs par défaut peuvent être utilisées (il s'agit du fonctionnement habituel des agrégateurs Envision) :
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier or 0
Sur les lignes 6 à 10, la table résultat Items est affichée pour illustrer le calcul effectué par l'instruction by-at. Comme prévu, chaque article est associé à la quantité minimale de commande imposée par son fournisseur.

Syntaxe générale du by-at

La syntaxe générale du by-at est la suivante :
T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C

Comme les agrégateurs habituels dans Envision, le by-at permet de faire correspondre "n" champs en même temps à travers des tuples. Ensuite, tous les agrégateurs Envision habituels peuvent être utilisés : sum but also min, median, same, etc. Le bloc or peut être omis comme dans les agrégations classiques.

Le by-at fonctionne comme suit :

  • Construit tous les groupes de tuples pour le by [T2.X, T2.Y, T2.Z], en filtrant la condition E le cas échéant. Pour chacun de ces groupes, si au moins un groupe existe à gauche, calcule l'agrégat agg(T2.B).
  • Construit tous les groupes de tuples pour le at [T1.X, T1.Y, T1.Z]. Pour chacun de ces groupes, affecte l'agrégat correspondant de la droite.
  • Pour les groupes sur le at qui n'ont pas de groupe correspondant à droite, utilise la valeur par défaut T1.C.

Tout comme dans les agrégateurs classiques, l'instruction or est facultative et indique la valeur à utiliser si le groupe est vide au terme de l'agrégation.

Conseils pour le by-at

Le by-at est une construction puissante utilisable dans beaucoup de situations, pas uniquement pour des jointures entre tables du point de vue SQL.

La jointure d'une table avec elle-même est possible, par exemple :
O.DaySum = sum(O.Quantity) by O.Date at O.Date
Cette instruction illustre comment calculer des totaux quotidiens sur la table O sans recourir à la table Day.

Une jointure entre les jours et semaines des tables Calendar est possible grâce au left-by: par exemple,
Day.Shift = sum(O.Quantity) by [O.Id, O.Date - 1] at [Day.Id, Day.Date]
illuste comment décaler les quantités d'un jour au moyen d'un by-at.

Traduire le by-at en SQL

Pour les lecteurs qui connaissent le SQL, l'expression Envision :
Moq = same(S.Moq) by S.Supplier at Supplier or defaultMoq

est équivalente au code SQL suivant, qui utilise une jointure externe :

UPDATE Items LEFT OUTER JOIN S ON Items.Supplier = S.Supplier SET Moq = COALESCE(S.Moq, defaultMoq)

La syntaxe Envision met l'accent sur les calculs de type Excel plutôt que sur l'algèbre relationnelle elle-même.