Fonctions d'agrégation approximative en SQL standard

Les fonctions d'agrégation approximative sont adaptables en termes de temps et d'utilisation de la mémoire, mais elles produisent des résultats approximatifs au lieu de résultats exacts. Pour en savoir plus, consultez la page Agrégation approximative.

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

Description

Renvoie le résultat approximatif de COUNT(DISTINCT expression). La valeur renvoyée est une estimation statistique, pas nécessairement la valeur réelle.

Cette fonction est moins précise que COUNT(DISTINCT expression), mais elle fonctionne mieux en cas d'entrée volumineuse.

Types d'arguments acceptés

Tout type de données sauf : ARRAY STRUCT

Types de données renvoyés

INT64

Exemples

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTILES

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

Description

Renvoie les limites approximatives d'un groupe de valeurs expression, où number représente le nombre de quantiles à créer. Cette fonction renvoie un tableau de number + 1 éléments, le premier élément étant le minimum approximatif et le dernier le maximum approximatif.

Types d'arguments acceptés

expression peut correspondre à n'importe quel type de données accepté, sauf : ARRAY STRUCT

number doit être de type INT64.

Clauses facultatives

Les clauses sont appliquées dans l'ordre suivant :

1. DISTINCT : chaque valeur distincte de type expression n'est agrégée qu'une seule fois dans le résultat.

2. IGNORE NULLS ou RESPECT NULLS : si IGNORE NULLS est spécifiée ou si aucune de ces clauses n'est spécifiée, les valeurs NULL sont exclues du résultat. Si RESPECT NULLS est spécifiée, les valeurs NULL sont incluses dans le résultat (toutefois, si un tableau du résultat final de la requête contient un élément NULL, une erreur est générée).

Types de données renvoyés

Un tableau (ARRAY) du type spécifié par le paramètre expression.

Renvoie NULL s'il n'y a aucune ligne d'entrée ou si l'évaluation d'expression donne NULL pour toutes les lignes.

Exemples

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+

SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+

SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

Description

Renvoie les premiers éléments approximatifs d'expression. Le paramètre number spécifie le nombre d'éléments renvoyés.

Types d'arguments acceptés

expression peut correspondre à n'importe quel type de données accepté par la clause GROUP BY.

number doit être de type INT64.

Types de données renvoyés

Un tableau (ARRAY) de type STRUCT. L'objet STRUCT contient deux champs. Le premier champ (nommé value) contient une valeur d'entrée. Le second champ (nommé count) contient un entier de type INT64 spécifiant le nombre de fois où la valeur a été renvoyée.

Renvoie NULL s'il n'y a aucune ligne d'entrée.

Exemples

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

Gestion des valeurs NULL

APPROX_TOP_COUNT n'ignore pas les valeurs NULL dans l'entrée. Par exemple :

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

Description

Renvoie les premiers éléments approximatifs d'expression, en fonction de la somme d'un paramètre weight attribué. Le paramètre number spécifie le nombre d'éléments renvoyés.

Si l'entrée weight est négative ou correspond à NaN, cette fonction renvoie une erreur.

Types d'arguments acceptés

expression peut correspondre à n'importe quel type de données accepté par la clause GROUP BY.

weight doit être de l'un des types suivants :

• INT64
• FLOAT64

number doit être de type INT64.

Types de données renvoyés

Un tableau (ARRAY) de type STRUCT. L'objet STRUCT contient deux champs : value et sum. Le champ value contient la valeur de l'expression d'entrée. Le champ sum est du même type que weight, et il correspond à la somme approximative de la pondération d'entrée associée au champ value.

Renvoie NULL s'il n'y a aucune ligne d'entrée.

Exemples

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

Gestion des valeurs NULL

APPROX_TOP_SUM n'ignore pas les valeurs NULL pour les paramètres expression et weight.

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+