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

Si vous comptez des valeurs distinctes pour des agrégations hiérarchiques (par exemple, des ensembles de groupements multiples, des rollups ou des cubes), vous pouvez améliorer les performances en produisant des ARRAYs qui contiennent ces valeurs distinctes et calculer le nombre de valeurs distinctes à partir de ces ARRAYs. Cette approche peut être plus rapide que l’utilisation de COUNT(DISTINCT <expr>).

Cette rubrique explique comment utiliser des ARRAYs pour compter des valeurs distinctes.

Pour d’autres techniques de comptage des valeurs distinctes, voir Calcul du nombre de valeurs distinctes.

Dans ce chapitre :

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

Supposons que vous vouliez compter le nombre de valeurs distinctes dans my_column. Le tableau suivant compare les instructions SQL pour effectuer cette tâche avec COUNT(DISTINCT expression) et ARRAY_UNIQUE_AGG(expression).

Exemple avec COUNT(DISTINCT <expression>)

Exemple avec ARRAY_UNIQUE_AGG(<expression>)

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;

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

Supposons que vous vouliez compter le nombre de valeurs distinctes dans my_column par my_key_1 et my_key_2. Le tableau suivant compare les instructions SQL pour effectuer cette tâche avec COUNT(DISTINCT expression) et ARRAY_UNIQUE_AGG(expression).

Exemple avec COUNT(DISTINCT <expression>)

Exemple avec ARRAY_UNIQUE_AGG(<expression>)

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;

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

ARRAY_UNIQUE_AGG est encore plus efficace pour les requêtes agrégées GROUP BY ROLLUP. Les ARRAYs sont composables (contrairement aux COUNT(DISTINCT <expressions>)), ce qui permet de réduire le travail de calcul et les temps d’exécution.

Supposons que vous vouliez remonter le nombre de valeurs distinctes dans my_column par my_key_1 et my_key_2. Le tableau suivant compare les instructions SQL pour effectuer cette tâche avec COUNT(DISTINCT expression) et ARRAY_UNIQUE_AGG(expression).

Exemple avec COUNT(DISTINCT <expression>)

Exemple avec ARRAY_UNIQUE_AGG(<expression>)

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.

Revenir au début