Verwenden von Arrays zum Berechnen diskreter Werte für hierarchische Aggregationen¶

Einführung¶

Wenn Sie die Anzahl der diskreten Werte für hierarchische Aggregationen (z. B. mehrere Gruppierungssätze, Rollups oder Cubes) berechnen, können Sie die Berechnung beschleunigen, indem Sie Funktionen aufrufen, die Arrays mit den diskreten Werten erzeugen. Sie können dann ARRAY_SIZE aufrufen, um die Anzahl dieser diskreten Werte zu zählen.

Diese Aggregatfunktionen, die ARRAYs diskreter Werte erzeugen, können bei Abfragen der folgenden Formen eine bessere Performance als COUNT(DISTINCT <Ausdruck>) erzielen:

• GROUP BY ROLLUP-Aggregatabfragen

• Abfragen, die mehrere Gruppierungssätze enthalten.

Im Gegensatz zu COUNT(DISTINCT <Ausdruck>) (das für jede Gruppe ausgeführt werden muss), können Sie ARRAYs mit den diskreten Werten zusammenstellen und wiederverwenden. Bei hierarchischen Aggregationen vermeiden Sie die wiederholte Berechnung der diskreten Anzahl, indem Sie diese ARRAYs einmal erstellen und in höheren Aggregationsstufen wiederverwenden.

Um die Performance weiter zu verbessern, können Sie die Berechnung dieser ARRAYs bereits im Voraus (z. B. in einer materialisierten Ansicht) und nicht erst während der Abfrage ausführen und dann die vorberechneten ARRAYs in der Abfrage verwenden.

Erstellen eines ARRAY, das diskreten Werte enthält¶

Um ein ARRAY zu erstellen, das die diskreten Werte in einer Spalte enthält, rufen Sie die Funktion ARRAY_UNIQUE_AGG in einer SELECT-Anweisung auf.

ARRAY_UNIQUE_AGG ist eine Aggregationsfunktion. Aggregation bedeutet in diesem Zusammenhang, dass nur genau eine Instanz eines Wertes zurückgegeben wird, der in mehreren Zeilen vorkommt. Wenn mehrere Zeilen den Wert 3 enthalten, dann umfasst ARRAY_UNIQUE_AGG nur einmal 3 in dem zurückgegebenen ARRAY.

Erstellen Sie zum Beispiel die folgende Tabelle, die eine Spalte mit numerischen Werten enthält, und fügen Sie einige Werte in diese Spalte ein.

CREATE OR REPLACE TABLE array_unique_agg_test (a INTEGER);
INSERT INTO array_unique_agg_test VALUES (5), (2), (1), (2), (1);

Führen Sie den folgenden Befehl aus, um ein ARRAY zu erzeugen, das die diskreten Werte in der Spalte enthält:

SELECT ARRAY_UNIQUE_AGG(a) AS distinct_values FROM array_unique_agg_test;

+-----------------+
| DISTINCT_VALUES |
|-----------------|
| [               |
|   5,            |
|   2,            |
|   1             |
| ]               |
+-----------------+

Berechnen der Anzahl diskreter Werte aus den ARRAYs¶

Um die Gesamtzahl der diskreten Werte aus den ARRAY zu erhalten, rufen Sie ARRAY_SIZE auf, und übergeben Sie dabei das mit ARRAY_UNIQUE_AGG erstellte ARRAY.

Beispiel:

SELECT ARRAY_SIZE(ARRAY_UNIQUE_AGG(a)) AS number_of_distinct_values FROM array_unique_agg_test;

+---------------------------+
| NUMBER_OF_DISTINCT_VALUES |
|---------------------------|
|                         3 |
+---------------------------+

Verwenden von Arrays zur Verbesserung der Abfrageleistung¶

Die folgenden Beispiele zeigen, wie die Aggregationsfunktionen verwendet werden, die ARRAYs von diskreten Werte als Alternative zu COUNT(DISTINCT <Ausdruck>) erzeugen.

• Beispiel 1: Zählen der diskreten Werte in einer einzelnen Tabelle

• Beispiel 2: Verwenden von GROUP BY zur Berechnung der Anzahl nach Gruppen

• Beispiel 3: Verwenden von GROUP BY ROLLUP für Rollup von Zählungen nach Gruppe

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table;

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column))
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

Vorberechnen der ARRAYs¶

Um die Leistung zu verbessern, können Sie das ARRAYs diskreter Werte in einer Tabelle oder materialisierten Ansicht vorberechnen.

Angenommen, Ihr Data Warehouse enthält eine Faktentabelle mit mehreren Dimensionen. Sie können eine materialisierte Ansicht definieren, die die ARRAYs konstruiert, um eine grobe Vorberechnung oder Voraggregation auszuführen, bevor die endgültigen Aggregate oder Cubes berechnet werden, die die Verwendung von COUNT(DISTINCT <Ausdruck>) erfordern.

Um die diskreten Werte von ARRAYs in jeder Zeile zu sammeln, rufen Sie die Funktion ARRAY_UNION_AGG auf.

Im folgenden Beispiel wird eine Tabelle erstellt, die die ARRAYs enthält. Diese Tabelle wird dann verwendet, um die Anzahl diskreter Werte zu berechnen, die nach verschiedenen Dimensionen aggregiert werden.

Die folgende Anweisung erstellt eine Tabelle mit dem Namen precompute, die die ARRAYs enthält:

CREATE TABLE precompute AS
SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_UNIQUE_AGG(my_column) arr
FROM my_table
GROUP BY 1, 2;

Mit der folgenden Anweisung werden die Aggregate für my_dimension_1 und my_dimension_2 berechnet:

SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_SIZE(arr)
FROM precompute
GROUP BY 1, 2;

Die folgende Anweisung berechnet das Aggregat nur für my_dimension_1:

SELECT
  my_dimension_1,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

Die folgende Anweisung berechnet das Aggregat nur für my_dimension_2:

SELECT
  my_dimension_2,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

Einschränkungen¶

In Snowflake sind ARRAY-Datentypen auf 16 MiB begrenzt, was bedeutet, dass ARRAY_UNIQUE_AGG oder ARRAY_UNION_AGG einen Fehler generieren, wenn die physische Größe des Ausgabe-ARRAY diese Größe überschreitet.

In diesen Fällen müssen Sie stattdessen eine Bitmap-Aggregation verwenden. Alternativ können Sie ein Bucketisierungsverfahren anwenden, das dem Verfahren für Bitmap-Aggregationen ähnelt, jedoch mit einer anderen Bucketisierungsfunktion als BITMAP_BUCKET_NUMBER.