Utilisation de tableaux pour calculer des valeurs distinctes pour les agrégations hiérarchiques¶

Introduction¶

Lorsque vous calculez le nombre de valeurs distinctes pour des agrégations hiérarchiques (par exemple, des ensembles de groupement multiples, des rollups ou des cubes), vous pouvez accélérer le calcul en appelant des fonctions qui produisent des tableaux contenant ces valeurs distinctes. Vous pouvez alors appeler ARRAY_SIZE pour calculer le nombre de ces valeurs distinctes.

Ces fonctions d’agrégation qui produisent des ARRAYs de valeurs distinctes peuvent être plus performantes que l”COUNT(DISTINCT <expression >) dans les requêtes des formes suivantes :

• Requêtes agrégées GROUP BY ROLLUP

• Requêtes contenant plusieurs ensembles de regroupement.

Contrairement à une COUNT(DISTINCT <expression>) (qui doit être exécutée pour chaque groupe), vous pouvez composer et réutiliser des ARRAYs en appelant les valeurs distinctes. Pour les agrégations hiérarchiques, vous évitez de calculer à plusieurs reprises les comptes distincts en produisant ces ARRAYs une fois et en les réutilisant dans les niveaux d’agrégation supérieurs.

En outre, pour améliorer encore les performances, vous pouvez produire ces ARRAYs à l’avance (par exemple dans une vue matérialisée), plutôt que pendant la requête. De plus, vous pouvez utiliser ces ARRAYs précalculés dans votre requête.

Création d’un ARRAY contenant des valeurs distinctes¶

Pour créer un ARRAY qui contient les valeurs distinctes d’une colonne, appelez la fonction ARRAY_UNIQUE_AGG dans une instruction SELECT.

ARRAY_UNIQUE_AGG est une fonction d’agrégation. Dans ce contexte, l’agrégation consiste à ne renvoyer qu’une seule instance d’une valeur qui apparaît dans plusieurs lignes. Si plusieurs lignes contiennent la valeur 3, ARRAY_UNIQUE_AGG n’inclut 3 qu’une seule fois dans le ARRAY retourné.

Par exemple, créez la table suivante contenant une colonne de valeurs numériques et insérez des valeurs dans cette colonne.

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

Exécutez la commande suivante pour produire un ARRAY qui contient les valeurs distinctes dans la colonne :

SELECT ARRAY_UNIQUE_AGG(a) AS distinct_values FROM array_unique_agg_test;

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

Calcul du nombre de valeurs distinctes à partir des ARRAYs¶

Pour obtenir le nombre total de valeurs distinctes à partir de l’ARRAY, appelez ARRAY_SIZE, en transmettant un ARRAY créé par ARRAY_UNIQUE_AGG.

Par exemple :

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

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

Utilisation des tableaux pour améliorer les performances des requêtes¶

Les exemples suivants montrent comment utiliser les fonctions d’agrégation qui produisent des ARRAYs de valeurs distinctes comme alternative à une COUNT(DISTINCT <expression>).

• Exemple 1 : comptage des valeurs distinctes dans une seule table

• Exemple 2 : utilisation de GROUP BY pour effectuer le comptage par groupe

• Exemple 3 : utilisation de GROUP BY ROLLUP pour remonter les comptages par groupe

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);

Précalcul des ARRAYs¶

Pour améliorer les performances, vous pouvez précalculer les ARRAYs de valeurs distinctes dans une table ou une vue matérialisée.

Par exemple, supposons que votre entrepôt de données contient une table de faits avec plusieurs dimensions. Vous pouvez définir une vue matérialisée qui construit les ARRAYs pour effectuer un précalcul ou une pré-agrégation approximative avant de calculer les agrégats ou cubes finaux qui nécessitent une COUNT(DISTINCT <expression>).

Pour collecter les valeurs distinctes des ARRAYs dans chaque ligne, appelez la fonction ARRAY_UNION_AGG.

L’exemple suivant crée une table contenant les ARRAYs et utilise cette table pour calculer le nombre de valeurs distinctes, agrégées par différentes dimensions.

L’instruction suivante crée une table nommée precompute qui contient les ARRAYs :

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

L’instruction suivante calcule les agrégats pour my_dimension_1 et my_dimension_2 :

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

L’instruction suivante calcule l’agrégat uniquement pour my_dimension_1 :

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

L’instruction suivante calcule l’agrégat uniquement pour my_dimension_2 :

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

Limites¶

Dans Snowflake, les types de données ARRAY sont limités à 16 MiB, ce qui signifie que ARRAY_UNIQUE_AGG ou ARRAY_UNION_AGG généreront une erreur si la taille physique de l’ARRAY de sortie dépasse cette taille.

Dans ces cas, envisagez d’utiliser une agrégation bitmap à la place. Comme alternative, vous pouvez appliquer une technique de bucketisation similaire à celle utilisée pour les agrégations de bitmap, mais avec une fonction de bucketisation différente de BITMAP_BUCKET_NUMBER.