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 de tables spécifiques.

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.

Jointures croisées

La jointure croisée est une autre opération relationnelle classique prise en charge par Envision. Une jointure croisée opère un produit cartésien des lignes concernées. Elle peut facilement générer un grand nombre de lignes à traiter. Nous vous recommandons donc de faire attention à la taille des tables impliquées afin de garder la maîtrise du temps de traitement. La syntaxe des jointures croisées est la suivante :
T1.R = sum(T1.A * T2.B) cross (T1, T2) if (T1.C & T2.D) or T1.Default
Cette instruction crée une table temporaire anonyme qui correspond à la jointure des tables T1 et T2. L'argument agrégateur et le filtre if sont évalués dans cette table (et, par conséquent, gonflés de force depuis les tables T1 et T2). Le résultat de l'agrégation est ajouté à la table T1 en agrégeant les lignes filtrées de la jointure croisée qui correspondent à chaque ligne de T1.

Si une relation existe déjà entre T1 et T2 — comme entre Items et Orders (articles et commandes), puisque les commandes peuvent être reliées aux articles — ou si les deux tables sont identiques, une variante qui repose sur un alias peut être utilisée :
T1.R = sum(T1.A * TAlias.B) cross (T1, T2 as TAlias) if (T1.C & TAlias.B) or T1.Default
La table alias TAlias contient les même variables que T2, mais aucune relation n'existe entre elle et les autres tables (sauf la table croisée elle-même). La table d'alias n'est pas disponible en dehors de l'argument agrégateur et du filtre de condition. Un nom d'alias peut être utilisé plusieurs fois dans un script donné (dans des agrégateurs séparés), mais ne doit pas être en conflit avec les tables d'entrée, les tables internes ou les tables créées avec une instruction table T = ... (comme illustré ci-dessus).

La taille de la jointure croisée est pour l'instant limitée à 4e9 (quatre milliards) de lignes. Si vous croisez une table avec elle-même, celle-ci ne doit donc pas dépasser les 60 000 lignes. En pratique, Envision ralentit sensiblement avant la limite de quatre milliards de lignes. Nous vous recommandons de veiller à bien filtrer les tables « avant » la jointure pour garder le contrôle de la complexité.