Fonctions d'agrégation

Une fonction d'agrégation est une fonction qui récapitule les lignes d'un groupe en une valeur unique. COUNT, MIN et MAX sont des exemples de fonctions d'agrégation.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Lorsqu'ils sont utilisés conjointement avec une clause GROUP BY, les groupes récapitulés disposent généralement d'au moins une ligne. Lorsque la clause SELECT associée n'a pas de clause GROUP BY ou que certains modificateurs de fonction d'agrégation filtrent les lignes du groupe à récapituler, il est possible que la fonction d'agrégation doive récapituler un groupe vide. Dans ce cas, les fonctions COUNT et COUNTIF renvoient 0, alors que toutes les autres fonctions d'agrégation renvoient NULL.

Les sections suivantes décrivent les fonctions d'agrégation acceptées par Cloud Spanner SQL.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Description

Renvoie expression pour une ligne sélectionnée dans le groupe. La ligne sélectionnée est non déterministe et non aléatoire. Renvoie NULL lorsque l'entrée ne produit aucune ligne. Renvoie NULL lorsque expression est NULL pour toutes les lignes du groupe.

ANY_VALUE se comporte comme si IGNORE NULLS était spécifié. Les lignes pour lesquelles la valeur de expression est définie sur NULL ne sont pas prises en compte et ne seront pas sélectionnées.

Types d'arguments acceptés

Tous

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

Le type renvoyé correspond au type de données d'entrée.

Exemples

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Description

Renvoie un tableau (ARRAY) de valeurs expression.

Types d'arguments acceptés

Tout type de données, sauf ARRAY.

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'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é, les valeurs NULL sont exclues du résultat. Si RESPECT NULLS est spécifié, les valeurs NULL sont incluses dans le résultat. Si l'une de ces valeurs est spécifiée, les valeurs NULL sont incluses dans le résultat.
3. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Ordre des éléments de sortie

L'ordre des éléments dans le résultat est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.

Type de données renvoyé

ARRAY

S'il n'y a aucune ligne d'entrée, cette fonction renvoie NULL.

Exemples

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+

SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+

SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

Description

Concatène les éléments de la valeur expression de type ARRAY et renvoie un seul élément ARRAY. Cette fonction ignore les tableaux d'entrée NULL, mais respecte les éléments NULL des tableaux d'entrée non-NULL.

Types d'arguments acceptés

ARRAY

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Ordre des éléments de sortie

L'ordre des éléments dans le résultat est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.

Type de données renvoyé

ARRAY

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

Exemples

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Renvoie la moyenne des valeurs d'entrée différentes de NULL, ou NaN si l'entrée contient une valeur NaN.

Types d'arguments acceptés

Tout type d'entrée numérique, par exemple INT64. Notez que le résultat renvoyé est non déterministe pour les types d'entrée à virgule flottante, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	FLOAT64
RÉSULTAT	FLOAT64	NUMERIC	FLOAT64

Exemples

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+

SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Effectue une opération AND (ET) bit à bit sur l'expression et renvoie le résultat.

Types d'arguments acceptés

• INT64

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

INT64

Exemples

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Effectue une opération OR (OU) bit à bit sur l'expression et renvoie le résultat.

Types d'arguments acceptés

• INT64

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

INT64

Exemples

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Effectue une opération XOR (OU exclusif) bit à bit sur l'expression et renvoie le résultat.

Types d'arguments acceptés

• INT64

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

INT64

Exemples

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+

SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

NB

1. COUNT(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

1. Renvoie le nombre de lignes dans l'entrée.
2. Renvoie le nombre de lignes où expression est évaluée avec une valeur autre que NULL.

Types d'arguments acceptés

expression peut correspondre à n'importe quel type de données. Si DISTINCT est présent, expression ne peut être qu'un type de données groupable.

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

INT64

Exemples

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+

SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Renvoie le nombre de valeurs TRUE pour l'expression. Renvoie 0 s'il n'y a aucune ligne d'entrée ou si l'évaluation de la valeur expression donne FALSE ou NULL pour toutes les lignes.

Types d'arguments acceptés

BOOL

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

INT64

Exemples

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

Description

Renvoie le AND (ET) logique de toutes les expressions non nulles (non-NULL). Renvoie NULL s'il n'y a aucune ligne d'entrée ou si l'évaluation de l'expression est NULL pour toutes les lignes.

Types d'arguments acceptés

BOOL

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

BOOL

Exemples

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

Description

Renvoie le OR (OU) logique de toutes les expressions non nulles (non-NULL). Renvoie NULL s'il n'y a aucune ligne d'entrée ou si l'évaluation de l'expression est NULL pour toutes les lignes.

Types d'arguments acceptés

BOOL

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

BOOL

Exemples

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

Description

Renvoie la valeur maximale des expressions non nulles (non-NULL). Renvoie NULL s'il n'y a aucune ligne d'entrée ou si l'évaluation de l'expression est NULL pour toutes les lignes. Renvoie NaN si l'entrée contient une valeur NaN.

Types d'arguments acceptés

Tout type de données sauf : ARRAY STRUCT

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

Identique au type de données utilisé pour les valeurs d'entrée.

Exemples

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

Description

Renvoie la valeur minimale des expressions non nulles (non-NULL). Renvoie NULL s'il n'y a aucune ligne d'entrée ou si l'évaluation de l'expression est NULL pour toutes les lignes. Renvoie NaN si l'entrée contient une valeur NaN.

Types d'arguments acceptés

Tout type de données sauf : ARRAY STRUCT

Clause facultative

HAVING MAX ou HAVING MIN : limite l'ensemble des lignes que la fonction agrège selon une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

Identique au type de données utilisé pour les valeurs d'entrée.

Exemples

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Description

Renvoie une valeur (STRING ou BYTES) obtenue par concaténation des valeurs non nulles.

Si un delimiter est spécifié, les valeurs concaténées sont séparées par ce délimiteur. Dans le cas contraire, une virgule est utilisée comme délimiteur.

Types d'arguments acceptés

STRING BYTES

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Ordre des éléments de sortie

L'ordre des éléments dans le résultat est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.

Type de données renvoyé

STRING BYTES

Exemples

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+

SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+

SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Description

Renvoie la somme des valeurs non nulles.

Si l'expression est une valeur à virgule flottante, la somme est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.

Types d'arguments acceptés

Tout type de données numériques accepté.

Clauses facultatives

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

1. DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
2. HAVING MAX ou HAVING MIN : limite l'ensemble de lignes que la fonction agrège en fonction d'une valeur maximale ou minimale. Consultez les clauses HAVING MAX et HAVING MIN pour plus de détails.

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	FLOAT64
RÉSULTAT	INT64	NUMERIC	FLOAT64

Cas particuliers :

Renvoie NULL si l'entrée ne contient que des valeurs NULL.

Renvoie NULL si l'entrée ne contient aucune ligne.

Renvoie Inf si l'entrée contient Inf.

Renvoie -Inf si l'entrée contient -Inf.

Renvoie NaN si l'entrée contient une valeur NaN.

Renvoie NaN si l'entrée contient une combinaison de Inf et -Inf.

Exemples

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+

SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+

SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Clauses courantes

Clauses HAVING MAX et HAVING MIN

La plupart des fonctions d'agrégation acceptent deux clauses facultatives appelées HAVING MAX et HAVING MIN, qui limitent l'ensemble de lignes agrégées par une fonction aux lignes ayant une valeur maximale ou minimale dans une colonne particulière. La syntaxe se présente généralement comme suit :

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])

• HAVING MAX : limite l'ensemble des lignes agrégées par la fonction à celles ayant pour expression2 une valeur égale à la valeur maximale de expression2 au sein du groupe. La valeur maximale est égale au résultat de MAX(expression2).
• HAVING MIN : limite l'ensemble des lignes agrégées par la fonction à celles ayant pour expression2 une valeur égale à la valeur minimale de expression2 au sein du groupe. La valeur minimale est égale au résultat de MIN(expression2).

Ces clauses ignorent les valeurs NULL lors du calcul de la valeur maximale ou minimale, sauf si l'évaluation de l'expression2 est NULL pour toutes les lignes.

Ces clauses ne sont pas compatibles avec les types de données suivants : ARRAYSTRUCT

Exemple

Dans cet exemple, les précipitations moyennes sont renvoyée pour l'année la plus récente, soit 2001.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

En premier lieu, la requête obtient les lignes présentant la valeur maximale de la colonne year. Celles-ci sont au nombre de deux :

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Enfin, la requête calcule la moyenne des valeurs de la colonne inches (9 et 1) avec le résultat suivant :

+---------+
| average |
+---------+
| 5       |
+---------+