Tabellen mit by-at in Envision verküpfen

Tabellen mit by-at verknüpfen












Startseite » Ressourcen » Hier

Envision bietet einen eingebauten Mechanismus, um Tabellen, die ein Id-Feld (Artikelindex) enthalten - was auch üblich der Fall bei Verkaufs- oder Kaufhistorien ist - zu verknüpfen. Doch dieser eingebaute Mechanismus, der natural joins genannt wird, lässt sich nicht auf komplexere Fälle übertragen. Daher bietet Envision zusätzlich eine etwas umfassendere Lösung, das sogenannte by-at. Das by-at stellt eine besondere Art der Aggregation dar, die ermöglicht, beliebige Verknüpfungen von Tabellen vorzunehmen.

Ein Skript zur Veranschaulichung

Auf dieser Seite nutzen wir das Beispiel-Dataset, das Sie über den Pfad /sample in Ihrem Lokad-Konto erreichen können. Sollte dieser noch nicht vorhanden sein, empfehlen wir Ihnen, sich zuerst den Artikel Dateien mit Envision lesen anzusehen. Besonders wichtig ist der Abschnitt Typdefinition der Erwartungen, um folgendes Skript zu verstehen.

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

Die ersten drei Zeilen sind unsere typischen Anweisungen zum Lesen von Dateien. Auf Zeile 3 wird durch den erwarteten Typ S[*] bestimmt, dass die Tabelle der Lieferanten keine Erwartungen bezüglich des Typs hat. Es handelt sich um eine unabhängige Tabelle ohne Primärschlüssel oder Fremdschlüssel. Tatsächlich enthält die Datei Lokad_Suppliers.tsv, im Gegensatz zu allen anderen tsv-Dateien im Ordner, keine Id-Spalte. Da diese Spalte fehlt, kann Envision diese Tabelle nicht implizit mit der Items-Tabelle verknüpfen. So ist die Tabelle der Lieferantennach Lieferanten geordnet und enthält einen Lieferanten pro Zeile, während alle anderen Dateien nach Item sortiert werden.

In Zeile 5 nutzen wir die by-at Aggregation, die so genannt wird, weil sie die Schlüsselwörter by und at benutzt, um eine Verknüpfung (join) der Items- und der S-Tabelle auszuführen. Wir hätten auch Folgendes schreiben können:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier
Doch nach den gewöhnlichen Konventionen Envisions, wird der Name der Items-Tabelle ausgelassen.

In diesem besonderen Beispiel, haben wir einen Vektor Items.Moq mit den aus der S-Tabelle erhaltenen Daten befüllt. So wurde eine Verknüpfung zwischen den Feldern Items.Supplier und S.Supplier erstellt. Außerdem hätte der Ausdruck or 0 ausgelassen werden können, da 0 (Null) bereits der Standardwert ist.

Sollte ein Eintrag bezgl. des Lieferanten in der Items-Tabelle keinen Gegenstück in der S-Tabelle enthalten, wird der Wert Null benutzt. Doch über das optionale or-Schlüsselwort können nach dem gewöhnlichen Verhalten von Envision-Aggregatoren auch andere Standardwerte benutzt werden. So hätten wir auch schreiben können:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier or 0
In Zeilen 6 bis 10 wird die entstandene Items-Tabelle angezeigt, um die Berechnung, die durch das by-at in der vorangehenden Zeile ausgeführt wurde, zu veranschaulichen. Wie erwartet wurde jeder Artikel mit dem entsprechenden MOQ-Wert seines Lieferanten verknüpft.

Genauso wie bei gewöhnlichen Aggregatoren, ist die or-Anweisung optional und bietet den Wert, der als Ergebnis der Aggregation benutzt werden soll, wenn die Gruppe leer ist.

Allgemeine Syntax für das by-at

Die allgemeine Syntax für das by-at ist:

T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C

Ähnlich zu den gängigen Aggregatoren in Envision bietet das by-at die Möglichkeit, die Übereinstimmung von Tupel auszuführen, das heißt, n Felder gleichzeitig zu vergleichen. Es können dann auch alle weiteren Aggregatoren von Envision genutzt werden: sum, aber auch min, median, same, etc. Der Block or kann ausgelassen werden, wie auch bei der gewöhnlichen Aggregation.

Also lautet die Semantik des by-at wie folgt:

  • Bilde alle eindeutigen Gruppen von Tupeln für by [T2.X, T2.Y, T2.Z], indem, falls vorhanden, nach Bedingung E gefiltert wird. Berechne für jede dieser Gruppe das Aggregat agg(T2.B).
  • Bilde alle eindeutigen Gruppen von Tupeln für at [T1.X, T1.Y, T1.Z]. Ordne jedem das übereinstimmende Aggregat von rechts zu.
  • Für die Gruppen des at, die keine Entsprechung rechts haben, gehe auf den vorgegeben T1.C-Wert zurück.

Tipps für by-at

Das by-at stellt einen leistungsfähigen Mechanismus dar, der vielfältig, abgesehen für die Verknüpfung von Tabellen aus der üblichen SQL-Perspektive, eingesetzt werden kann

Eine Tabelle kann auch mit sich selbst über verknüpft werden: Zum Beispiel zeigt,
O.DaySum = sum(O.Quantity) by O.Date at O.Date
wie die täglichen Gesamtwerte über die O-Tabelle berechnet werden können, ohne auf die Day-Tabelle zurückzugreifen.

Auch Kalender-Tabellen, Tage und Wochen können verknüpft werden: Zum Beispiel zeigt,
Day.Shift = sum(O.Quantity) by [O.Id, O.Date - 1] at [Day.Id, Day.Date]
wie man Mengen von einem Tag auf einen anderen mit by-at verschieben kann.

By-at in SQL übersetzen

Für diejenigen Leser, die bereits mit SQL vertraut sind, sollte noch hervorgehoben werden, dass der Ausdruck:
Moq = same(S.Moq) by S.Supplier at Supplier or defaultMoq

in Envision, Folgendem in SQL entspricht, das ein äußeres left-join benutzt:

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

Die Syntax in Envision weist eine größere Ähnlichkeit zu Excel-Berechnungen auf, als zur relationalen Algebra selbst.