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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.IGNORE NULLS
ouRESPECT NULLS
: siIGNORE NULLS
est spécifié, les valeursNULL
sont exclues du résultat. SiRESPECT NULLS
est spécifié, les valeursNULL
sont incluses dans le résultat. Si l'une de ces valeurs est spécifiée, les valeursNULL
sont incluses dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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
- Renvoie le nombre de lignes dans l'entrée.
- Renvoie le nombre de lignes où
expression
est évaluée avec une valeur autre queNULL
.
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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 :
DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.HAVING MAX
ouHAVING 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 pourexpression2
une valeur égale à la valeur maximale deexpression2
au sein du groupe. La valeur maximale est égale au résultat deMAX(expression2)
.HAVING MIN
: limite l'ensemble des lignes agrégées par la fonction à celles ayant pourexpression2
une valeur égale à la valeur minimale deexpression2
au sein du groupe. La valeur minimale est égale au résultat deMIN(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 : ARRAY
STRUCT
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 |
+---------+