Daten mit Envision aggregieren - Software zur Bestandsoptimierung

Aggregation von Daten












Startseite » Ressourcen » Hier

Die Aggregation von Daten bezeichnet den Prozess, mehrere Tabellenzeilen über besondere Funktionen, die sogenannten Aggregatoren, zu kombinieren. So werden Summen, Durchschnitte, Zählungen oder Mittelwerte immer über Aggregatoren berechnet. Die Aggregation bietet aber auch die Möglichkeit, Daten aus verschiedenen Tabellen zu kombinieren. Envision bietet daher eine breite Syntax, um solche Szenarien zu unterstützen. In diesem Abschnitt erklären wir, wie Aggregatoren in Envision funktionieren.

Ein veranschaulichendes Skript

In diesem Abschnitt nutzen wir erneut das Beispiels-Dataset, das über den /sample Pfad in Ihrem Lokad-Konto erreichbar ist. Das untere Skript ist mäßig komplex und veranschaulicht manche der Muster für Datenaggregationen, die Envision bietet. Datenaggregation kann sowohl außerhalb, als auch innerhalb der Elemente stattfinden. Wir empfehlen Ihnen, erst Berechnungen mit Envision zu lesen, da es Ihnen helfen wird, die Inhalte dieses Abschnitts zu verstehen.
read "/sample/Lokad_Items.tsv" as Items
read "/sample/Lokad_Orders.tsv" as O
read "/sample/Lokad_PurchaseOrders.tsv" as PO
expect O.Quantity : number

show label "Aggregating data" a1f1 tomato

oend := max(O.Date)
obegin := oend - 365

totalPurchased := sum(PO.NetAmount)
totalSold := sum(O.NetAmount) when date >= obegin
show table "Total purchased" a2c2 with 
totalPurchased unit:"$"
show table "Sold over 1 year" d2f2 with
totalSold unit:"$"

VolumeSold = sum(O.NetAmount)
UnitSold = median(O.Quantity)
show table "Top sellers" a3f4 tomato with
Name
VolumeSold as "Sold" unit:"$"
UnitSold as "Median" 
order by VolumeSold desc

avgRet := avg(distinct(O.Date) by O.Client)
avgQty := avg(sum(O.Quantity) by [O.Client, O.Date])
show table "Average client returns" a5c5 with
round(avgRet)
show table "Average backet quantity" d5f5 with
avgQty

O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
Supplier
distinct(Category) as "Categories"
sum(O.NetAmount) as "Sold" unit:"$"
mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold" 
group by Supplier
order by sum(O.NetAmount) desc
Wir empfehlen Ihnen, mit dem Kopieren und Einfügen des oberen Skripts in Ihr Lokad-Konto zu beginnen und diesen auszuführen, um das entstehende Dashboard zu sehen. Wenn alles korrekt verläuft, sollten Sie dann das unten angezeigte Dashboard sehen.

Image

Skalare für die Aggregation von Einzelwerten

Eine Variable ohne Affinität zu einem „Artikel“ wird in Envision als skalare Variable bezeichnet (Variable, die keiner besonderen Zeile der Items Tabelle zugeordnet ist). Envisions Variablen sind von Natur aus mit denen von Excel ähnlich: diese Variablen sind Vektoren und diese beinhalten mehrere Werte gleichzeitig (tatsächlich ein Wert pro Artikel). Doch es ist auch möglich, Variablen zu haben, die sich wie eine einzige Excel-Zelle verhalten und die auch einen einzigen Wert beinhalten: das sind skalare Variablen. Das obere Skript zeigt, wie skalare Variablen berechnet werden können. Der Klarheit halber werden die relevanten Zeilen unten angeführt.
totalPurchased := sum(PO.NetAmount)
totalSold := sum(O.NetAmount) when date >= obegin
show table "Total purchased" a2c2 with 
  totalPurchased unit:"$"
show table "Sold over 1 year" d2f2 with
  totalSold unit:"$"
Von diesen wenigen Zeilen Code sind mehrere Aspekte hervorzuheben. Erstens werden beide Zuordnungen in Zeilen 1 und 2 mit dem skalaren Zuordnungsoperator := ausgeführt. Wird der Operator := anstelle des gewöhnlichen = Zuordnungsoperators benutzt, soll die Aggregation einen einzelnen Wert ergeben, der unabhängig von den Artikeln ist. Zweitens werden die Anfangsbuchstaben der Variablen totalPurchased und tableSold kleingeschrieben. Obwohl Variablennamen in Envision Groß- und Kleinschreibung nicht berücksichtigen, ist die Schreibweise dieser beiden Variablen kein Zufall. Im Einklang mit den Programmiernormen empfehlen wir, die Anfangsbuchstaben von Variablen, die skalare Werte beinhalten, klein zu schreiben. So wird Ihr Skript lesbarer und einfacher zu Debuggen. Drittens findet die Aggregation standartmäßig in der show table Anweisung, wenn die Tabelle nur eine einzige Spalte hat. So könnten beispielsweise Zeilen 1 und 3-4 in eine einzige show table Anweisung kombiniert werden, wie unten dargestellt wird.
show table "Total purchased" a2c2 with
  sum(PO.NetAmount) unit:"$"
Numerische skalare Variablen können auch für beliebige Berechnungen benutzt werden, wie jede numerische Variable in Envision. So kann man beispielsweise - obwohl es aus der Sicht des Unternehmens nicht so sinnvoll sein mag - folgende Berechnung am Ende eines Envision-Skripts hinzufügen, wenn die Variablen totalPurchased und totalSold definiert wurden.
mySum := (totalSold - totalPurchased) / 2
show table "My Sum" with mySum

Mehrwert-Aggregation

Envision bietet auch die Möglichkeit, Mehrwert-Aggregationen durchzuführen, mit denen gewöhnlich die Daten einer bestimmten Tabelle aggregiert werden und dessen Ergebnis auf die Zeilen einer anderen Tabelle projiziert werden. Daher ist die Aggregation der häufigste Ansatz zur Kombination von Daten aus verschiedenen Tabellen. Insbesondere besteht einer der häufigsten Fälle darin, die Daten einer historischen Tabelle, d.h. einer Tabelle, die mit einer Date Spalte indexiert ist, in einem Vektor zur aggregieren, der an die Items Tabelle angepasst ist.

Das zuvor genannte Skript zeigt dieses Muster an einer Tabelle, in der die meistverkauften Produkte gezeigt werden, d.h., die Artikel nach Verkaufsvolumen geordnet, wobei die höchsten Werte nach oben verschoben werden. Die relevanten Zeilen des Codes werden zur Veranschaulichung hier kopiert.
VolumeSold = sum(O.NetAmount)
UnitSold = median(O.Quantity)
show table "Top sellers" a3f4 tomato with 
  Name
  VolumeSold as "Sold" unit:"$"
  UnitSold as "Median" 
  order by VolumeSold desc
In Zeilen 1 und 2 finden auch Aggregationen auf eine ähnliche Art und Weise, wie bei den skalaren Aggregationen, die wir im vorangehenden Abschnitt beschrieben haben, statt. Doch die Zuordnungen werden mit dem Gleichheitszeichen = statt mit dem := Operator für skalare Zuordnungen ausgeführt. Folglich sind beide Vektoren VolumeSold und UnitSold Teil der Items Tabelle. Daher sind diese Variablen ähnlich zu Spalten in Excel und beinhalten nur einen Wert pro Artikel.

sum und median stellen besondere Funktionen, sogenannte Aggregationen, in Envision dar. Doch es gibt viele andere Aggregatoren in Envision, wie avg, min, max, first, last, usw. Doch der Einfachheit halber führen wir nicht alle in diesem Abschnitt an. Für mehr Information, können Sie die komplette Liste der Aggregatoren aufrufen.

Das Ergebnis dieser beiden Aggregationen wird in der in Zeilen 3 bis 7 definierten Tabelle angezeigt. Beide Vektoren, VolumeSold und UnitSold werden nach dem Schlüsselwort with als Argumente gelistet und ihre Werte werden in der Tabelle angezeigt. Zuletzt zeigt die Anweisung order by in Zeile 7 an, dass die Tabelle nach den Artikeln mit den höchsten VolumeSold Werten sortiert werden sollte, welche am Anfang stehen sollten.

Diejenigen, denen die GROUP BY Syntax in SQL bekannt ist, mögen sich evtl. fragen, woher Envision weiß, welche Gruppierung bei der Berechnung von sum in Zeile 1 benutzt werden sollte. Standardmäßig nutzt Envision die Spalten, die als „Schlüssel“ auf der rechten Seite der Zuordnung fungieren. Im Falle der Variablen, die zur Items Tabelle gehören - der Tabelle, deren Namen implizit ist - ist die als Primärschlüssel dienende Spalte, die Id Spalte. Dies erklärt, weshalb eine Aggregation nach Artikel aus der Nutzung des = Zeichen resultiert.

Explizite Aggregationsgruppen mit by

Die bisherigen Aggregationen, die wir ausgeführt haben, stützten sich auf implizite Aggregationsmuster von Envision. Doch das Verhalten der Aggregatoren kann durch das optionale Schlüsselwort by, das zur expliziten Spezifizierung der anwendbaren Gruppierung dient, verändert werden. Lassen Sie uns zeigen, wie dieses Schlüsselwort by benutzt wird:
VolumeSold = sum(O.NetAmount)
SameVolumeSold = sum(O.NetAmount) by Id // Gleich!
Zeile 2 wird einem zweiten Vektor, namens SameVolumeSold zugeordnet, doch die Werte dieses Vektors sind identisch zu denen des Vektors VolumeSold in Zeile 1. Tatsächlich wird die Option by Id genutzt, obwohl dies implizit in Zeile 1 passiert. Intuitiv ist es bei der Nutzung der Option by so, als wenn Gruppen erstens, entsprechend des Ziels der Gruppierung erstellt werden würden und zweitens, als wenn der Aggregator für jede Gruppe separat berechnet werden würde. Die Option by bietet die Möglichkeit, ziemlich komplexe Aggregationen zu erstellen, wie im Skript am Anfang dieses Abschnitts gezeigt wird. Sehen wir uns die zwei Zeilen des Skripts, in denen die Aggregationen mit der by Option ausgeführt werden, genauer an.
avgRet := avg(distinct(O.Date) by O.Client)
avgQty := avg(sum(O.Quantity) by [O.Client, O.Date])
Der Aggregator distinct zählt die Anzahl der eindeutigen Werte in jeder Gruppe. In Zeile 1, werden die Zeilen der Bestellungstabelle O nach den entsprechenden Client Werten gruppiert. Anschließend wird für jeden Kunden die Anzahl der eindeutigen Bestelldaten gezählt. Intuitiv kann diese Aggregation als die Ermittlung der Anzahl von wiederkehrenden Bestellungen eines Kunden interpretiert werden. Dann wird dieses Ergebnis erneut mit dem avg Aggregator in einen einzelnen skalaren Wert, der die interne distinct Aggregation enthält, aggregiert.

Der Skalar avgQty kann als die Anzahl der gekauften Einheiten pro Einkauf gedeutet werden. Die Berechnung beginnt mit einer sum() by Anweisung, doch nach der by Option haben wir nicht mehr eine, sondern zwei durch Kommas getrennte Variablen, die in der eckigen Klammer auftauchen: [O.Client, O.Date]. Diese Syntax ist so zu verstehen: erstelle eine Gruppe für jedes Paar von Client und Date. Aus der Unternehmensperspektive behandeln wir alle Einheiten, die am selben Tag gekauft wurden, wie denselben Einkauf. Dies ist für die meisten Fälle ein logischer Ansatz. Zuletzt führt der externe Aufruf von avg zum endgültigen Durchschnitt der sum Aggregate, die für jedes Paar berechnet wurden.

Vereinfacht unterstützt die by Option eine beliebige Anzahl von Variablen nach der Syntax sum(foo) by [arg1, arg2, …, argN]. In der Praxis treten jedoch selten Fälle auf, in denen die Gruppierung von mehr als 4 Variablen gleichzeitig sinnvoll wäre. Dabei hat die Reihenfolge der Argumente keinen Einfluss auf die entstehenden Gruppen, die zur Berechnung der Aggregate genutzt werden.

Explizite Aggregation in einer Tabelle mit group by

Manchmal kann man mittels einer Aggregation neue Tabellen erstellen, die für die Nutzung in einem Dashboard relevanter als die originalen Tabellen, in denen die Daten nicht aggregiert sind, sein können. Daher unterstützt Envision die Möglichkeit, Daten direkt bei der Anweisung der Element-Deklaration zu aggregieren. Um schnellstmöglich diese Fähigkeiten von Envision zu prüfen, können Sie Daten aggregieren, die in einer Tabelle angezeigt werden müssen. Genau dies geschieht im obersten Skript auf dieser Seite. Sehen wir uns unten die relevanten Zeilen noch einmal an.
O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
Supplier
 distinct(Category) as "Categories"
  sum(O.NetAmount) as "Sold" unit:"$"
  mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold" 
  group by Supplier 
  order by sum(O.NetAmount) desc
Die show table Anweisung geht von Zeile 2 bis Zeile 8, dabei ist die Aggregation konkret in Zeile 7 mit der Anweisung group by zu finden, dieselbe Semantik wie bei der by Option, die wir zuvor beschrieben haben. Jetzt kann es sein, dass Sie sich wundern, warum Envision nicht das Schlüsselwort by statt dem Schlüsselwort group by benutzt, wenn diese semantisch gleich sind. Das if in Zeile 6 ist eine Agregatorfilter, der im nächsten Abschnitt erläutert wird.

Die Antwort darauf ist einfach: es ist möglich, die by Option innerhalb einer Liste von Ausdrücken nach dem with für ein bestimmtes Element zu nutzen (ein solcher Fall wird jedoch hier nicht gezeigt). Daher ermöglicht group by zwischen einer by Anweisung, die Teil eines Ausdrucks, das an das Element übergeben wird, ist und einer group by Anweisung, die sich auf das gesamte Element bezieht, zu unterscheiden. Mit anderen Worten bezieht sich group by auf alle nach dem with Schlüsselwort angeführten Ausdrücke, während die by Anweisung sich nur lokal auswirkt.

Wird group by genutzt, sollten alle Ausdrücke, die an das Element nach dem with Schlüsselwort übergeben werden, die Möglichkeit bieten, aggregiert zu werden. So ist beispielsweise das untere Skript nicht richtig, weil Name kein Aggregationsmuster bietet, wenn group by Supplier spezifiziert wird.
show table "WRONG!" with
  Name
  group by Supplier
Doch, wenn wir diesen Skript verändern, indem wir einen Aggregator hinzufügen, beispielsweise distinct, ist unser Envision Skript zulässig.
show table "CORRECT!" with
  distinct(Name)
  group by Supplier
Die einzige Ausnahme dieser Regel ist die Aggregation des eigenen Ziels. Im Skript am Anfang dieses Abschnitts steht eine group by Supplier Anweisung in Zeile 7. In Zeile 3 wird die Variable Supplier ohne Aggregatoren gelistet. Dennoch bleibt das Skript gültig, gerade deshalb, weil die Gruppierung entsprechend der Supplier Variable stattfindet.

Die Notwendigkeit, einen Aggregator zur Verfügung zu stellen betrifft auch die order by Anweisung in Zeile 8. So wird die Tabelle zuerst nach Lieferanten und erst dann nach der Id Spalte geordnet, es sei denn, es wird eine andere Reihenfolge spezifiziert. Folglich muss Envision pro Gruppe einen Wert berechnen, um alle diese Gruppen zu sortieren. Genau dies passiert mit der Anweisung group by sum(O.NetAmount).

Hier haben wir group by anhand des table Elements veranschaulicht, doch diese Syntax dient nicht ausschließlich dem table Element. Dasselbe Muster kann auch mit den meisten anderen Elementen benutzt werden. So wäre es auch möglich, das oberste Skript mit einem barchart Element (Säulendiagramm) zu erweitern, das nach Brand (Marke) aggregiert wäre.
show barchart "Sales by brand" with
  sum(O.NetAmount)
  group by Brand
Das group by unterstützt auch die Multi-Gruppierung, d.h., die Gruppierung mehrerer Vektoren oder mehrerer Ausdrücke, mit einer Syntax, die der der bereits beschriebenen by Option identisch ist.

Aggregationsfilter

Envision-Agregatoren unterstützten auch Filter über das if-Schlüsselword. Das folgende Skript veranschaulicht, wie ein solcher Filter in Zeile 6 benutzt werden kann:
O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
  Supplier
  distinct(Category) as "Categories"
  sum(O.NetAmount) as "Sold" unit:"$"
  mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold" 
  group by Supplier 
  order by sum(O.NetAmount) desc

Hier wird die Marke Fellowes durch den if-Filter ausdrücklich aus dem Bericht ausgeschlossen. Filter sind in den Anweisungen nützlich, weil sie die Möglichkeit bieten, jede Spalte einzeln zu filtern. Im Gegensatz dazu hätte ein where-Filter außerhalb der Anweisung alle ausgewählten Zeilen der O-Tabelle gleich für alle Spalten gefiltert.

Filter können auch inline benutzt werden:
TwoAndMore = sum(O.1) if (O.Quantity >= 2)