Dieser Beitrag bietet einen Überblick über die Funktionen, die für die Aggregation in Data Warehouses zur Verfügung stehen,

Verweise

Setup

Group By

Setup


Die Beispiele in diesem Artikel werden anhand der folgenden einfachen Dimensionstabelle ausgeführt.

GROUP BY

Beginnen wir damit, uns daran zu erinnern, wie die GROUP BY-Klausel funktioniert. Eine Aggregatfunktion verwendet mehrere Datenzeilen, die von einer Abfrage zurückgegeben werden, und aggregiert sie zu einer einzigen Ergebniszeile.

Das Einbeziehen der GROUP BY-Klausel begrenzt das von der Aggregatfunktion verarbeitete Datenfenster. Auf diese Weise erhalten wir einen aggregierten Wert für jede einzelne Wertekombination, die in den in der GROUP BY-Klausel aufgelisteten Spalten vorhanden ist. Die Anzahl der erwarteten Zeilen kann berechnet werden, indem die Anzahl der unterschiedlichen Werte jeder in der GROUP BY-Klausel aufgelisteten Spalte multipliziert wird. In diesem Fall würden wir, wenn die Zeilen zufällig geladen würden, eine Anzahl unterschiedlicher Werte für die ersten drei Spalten in der Tabelle von 2, 5 bzw. 10 erwarten. Wenn Sie also die Spalte fact_1_id in der GROUP BY-Klausel verwenden, erhalten Sie 2 Zeilen.

Wenn Sie die ersten beiden Spalten in die GROUP BY-Klausel aufnehmen, erhalten Sie 10 Zeilen (2 * 5) mit jeweils aggregierten Werten.

Wenn Sie die ersten drei Spalten in die GROUP BY-Klausel aufnehmen, erhalten Sie 100 Zeilen (2 * 5 * 10).

Es ist möglich, einen Teil-Rollup durchzuführen, um die Anzahl der berechneten Zwischensummen zu verringern.

ROLLUP

Zusätzlich zu den regelmäßigen Aggregationsergebnissen, die wir von der GROUP BY-Klausel erwarten, erzeugt die ROLLUP-Erweiterung Gruppenzwischensummen von rechts nach links und eine Gesamtsumme. Wenn “n” die Anzahl der in ROLLUP aufgelisteten Spalten ist, gibt es n + 1 Zwischensummenebenen.

Wenn Sie sich die Ausgabe in einer SQL * Plus- oder einer Rasterausgabe ansehen, können Sie die Zeilen mit Zwischensummen visuell identifizieren, da sie Nullwerte in den ROLLUP-Spalten enthalten. Es ist möglicherweise einfacher zu erkennen, wenn die Ausgabe der folgenden, hier gezeigten Abfrage gescannt wird. Wenn die Rohdaten Nullwerte enthalten, ist die Verwendung dieser visuellen Identifikation offensichtlich kein genauer Ansatz, aber wir werden dieses Problem später erörtern.

CUBE

Zusätzlich zu den Zwischensummen, die von der Erweiterung ROLLUP generiert werden, generiert die Erweiterung CUBE Zwischensummen für alle Kombinationen der angegebenen Dimensionen. Wenn “n” die Anzahl der im CUBE aufgelisteten Spalten ist, gibt es 2n Zwischensummenkombinationen.

Wenn die Anzahl der Dimensionen zunimmt, nehmen auch die zu berechnenden Zwischensummenkombinationen zu, wie aus der Ausgabe der folgenden Abfrage hervorgeht:

Es ist möglich, einen Teilwürfel zu erstellen, um die Anzahl der berechneten Zwischensummen zu verringern.

GROUPING-Funktionen

Zwischensummen, die durch Rollups und Cubes generiert wurden, können leicht visuell identifiziert werden. Um dies jedoch programmgesteuert zu tun, benötigen Sie tatsächlich etwas Genaueres als das Vorhandensein von Nullwerten in den Gruppierungsspalten. Hier kommt die GROUPING-Funktion ins Spiel. Sie akzeptiert eine einzelne Spalte als Parameter und gibt “1” zurück, wenn die Spalte einen Nullwert enthält, der als Teil einer Zwischensumme durch eine ROLLUP- oder CUBE-Operation generiert wurde, oder “0” für einen anderen Wert. einschließlich gespeicherter Nullwerte.

Die folgende Abfrage ist eine Wiederholung eines vorherigen Cubes, aber die GROUPING-Funktion wurde für jede Dimension im Cube hinzugefügt.

Daraus können wir sehen:

     F1G = 0, F2G = 0: Stellt eine Zeile dar, die die reguläre Zwischensumme enthält,
                                  
die wir von einer GROUP BY-Operation erwarten würden.
     F1G = 0, F2G = 1: Stellt eine Zeile dar, die eine Zwischensumme für einen bestimmten Wert der Spalte FACT_1_ID enthält,
                                   wie sie durch ROLLUP- und CUBE-Operationen generiert wurde.

     F1G = 1, F2G = 0: Stellt eine Zeile dar, die eine Zwischensumme für einen eindeutigen Wert der Spalte FACT_2_ID enthält,
                                   die wir nur in einer CUBE-Operation sehen würden.

     F1G = 1, F2G = 1: Stellt eine Zeile dar, die eine Gesamtsumme für die Abfrage enthält, wie sie durch
                                   ROLLUP- und CUBE-Operationen generiert wurde.

Es wäre jetzt einfach, ein Programm zu schreiben, um die Daten genau zu verarbeiten.

Die GROUPING-Spalten können zum Sortieren oder Filtern von Ergebnissen verwendet werden.

GROUPING_ID

Die GROUPING_ID-Funktion bietet eine alternative und kompaktere Möglichkeit, Zwischensummenzeilen zu identifizieren. Wenn Sie die Dimensionsspalten als Argumente übergeben, wird eine Zahl zurückgegeben, die die GROUP BY-Ebene angibt.

GROUP_ID

Es ist möglich, Abfragen zu schreiben, die die doppelten Zwischensummen zurückgeben, was etwas verwirrend sein kann. Die GROUP_ID-Funktion weist dem ersten Satz den Wert “0” zu, und allen nachfolgenden Sätzen wird eine höhere Nummer zugewiesen. Die folgende Abfrage erzwingt Duplikate, um die GROUP_ID-Funktion in Aktion anzuzeigen.

Bei Bedarf können Sie dann die Ergebnisse anhand der Gruppe filtern.

GROUPING SETS

Das Berechnen aller möglichen Zwischensummen in einem Cube, insbesondere derjenigen mit vielen Dimensionen, kann ein ziemlich intensiver Prozess sein. Wenn Sie nicht alle Zwischensummen benötigen, kann dies einen erheblichen Aufwand bedeuten. Der folgende Würfel mit drei Dimensionen ergibt 8 Zwischensummenebenen (GROUPING_ID: 0-7).

 

Wenn wir nur einige dieser Zwischensummenebenen benötigen, können wir den GROUPING SETS-Ausdruck verwenden und genau angeben, welche wir benötigen, sodass wir nicht den gesamten Würfel berechnen müssen. In der folgenden Abfrage interessieren uns nur Zwischensummen für die Gruppen “FACT_1_ID, FACT_2_ID” und “FACT_1_ID, FACT_3_ID”.

Beachten Sie, wie wir von 198 Zeilen mit 8 Zwischensummenebenen im Cube auf nur 30 Zeilen mit 2 Zwischensummenebenen gewechselt sind.

Zusammengesetzte Spalten

ROLLUP und CUBE berücksichtigen jede Spalte unabhängig voneinander, wenn sie entscheiden, welche Zwischensummen berechnet werden müssen. Für ROLLUP bedeutet dies, einen Schritt zurück in die Liste zu gehen, um die Gruppierungen zu bestimmen.

CUBE erstellt eine Gruppierung für jede mögliche Kombination von Spalten.

Zusammengesetzte Spalten ermöglichen die Gruppierung von Spalten in geschweiften Klammern, sodass sie bei der Festlegung der erforderlichen Gruppierungen als eine Einheit behandelt werden. In den folgenden ROLLUP-Spalten wurden “a” und “b” durch die zusätzlichen Klammern in eine zusammengesetzte Spalte umgewandelt. Infolgedessen wird die Gruppe “a” nicht mehr berechnet, da die Spalte “a” nur als Teil der zusammengesetzten Spalte in der Anweisung vorhanden ist.

In ähnlicher Weise werden die möglichen Kombinationen des folgenden CUBE reduziert, da Verweise auf “a” oder “b” einzeln nicht berücksichtigt werden, da sie bei der Bestimmung der Gruppierungen als einzelne Spalte behandelt werden.

Die Auswirkung davon wird in den folgenden zwei Aussagen deutlich. Der reguläre Cube gibt 198 Zeilen und 8 Gruppen (0-7) zurück, während der Cube mit der zusammengesetzten Spalte nur 121 Zeilen mit 4 Gruppen (0, 1, 6, 7) zurückgibt.

Verkettete Gruppierungen

Verkettete Gruppierungen werden definiert, indem mehrere durch Kommas getrennte GROUPING SETS, CUBEs oder ROLLUPs zusammengefügt werden. Die resultierenden Gruppierungen sind das Kreuzprodukt aller von den einzelnen Gruppierungssätzen erzeugten Gruppen. Es ist vielleicht ein bisschen einfacher zu verstehen, was dies bedeutet, wenn man sich ein Beispiel ansieht. Das folgende GROUPING SET führt zu 2 Gruppen von Zwischensummen, eine für die Spalte fact_1_id und eine für die Spalte fact_id_2.

Das nächste GROUPING SET führt zu zwei weiteren Gruppen von Zwischensummen, eine für die Spalte fact_3_id und eine für die Spalte fact_4_id.

Wenn wir sie zu einer verketteten Gruppierung zusammenfassen, erhalten wir 4 Gruppen von Zwischensummen. Die Ausgabe der folgenden Abfrage

Die Ausgabe der vorherigen drei Abfragen ergibt die folgenden Gruppierungen.

So können wir das endgültige Kreuzprodukt der beiden GROUPING SETS sehen, aus denen die verkettete Gruppierung besteht. Eine allgemeine Zusammenfassung wäre wie folgt.