Expressions, fonctions et opérateurs en SQL standard

Cette page présente les expressions BigQuery, y compris les fonctions et les opérateurs.libe

Règles relatives aux appels de fonctions

Les règles suivantes s'appliquent à toutes les fonctions, sauf indication contraire explicite dans la description de la fonction :

Pour les fonctions qui acceptent les types numériques, si l'un des opérandes est à virgule flottante et que l'autre est d'un autre type numérique, les deux opérandes sont convertis en FLOAT64 avant l'évaluation de la fonction.
Si un opérande est NULL, le résultat est NULL, sauf pour l'opérateur "IS".
Pour les fonctions prenant en compte le fuseau horaire (indiqué dans la description de la fonction), celui par défaut (UTC) est utilisé si aucun fuseau horaire n'est spécifié.

Préfixe SAFE.

Syntaxe :

SAFE.function_name()

Description

Si vous commencez une fonction par le préfixe SAFE., elle renvoie NULL au lieu d'une erreur. Le préfixe SAFE. n'empêche que les erreurs de la fonction préfixée elle-même : il n'empêche pas celles qui se produisent lors de l'évaluation d'expressions d'argument. Le préfixe SAFE. n'empêche que les erreurs causées par la valeur des entrées de la fonction, telles que les erreurs "valeur hors plage". D'autres erreurs, telles que des erreurs internes ou système, peuvent toujours se produire. Si la fonction ne renvoie pas d'erreur, SAFE. n'a aucun effet sur le résultat. Si la fonction ne renvoie jamais d'erreur, comme RAND, alors SAFE. n'a aucun effet.

Les opérateurs, tels que + et =, ne sont pas compatibles avec le préfixe SAFE.. Pour éviter les erreurs dues à une opération de division, utilisez SAFE_DIVIDE. Certains opérateurs, tels que IN, ARRAY et UNNEST, ressemblent à des fonctions, mais ne sont pas compatibles avec le préfixe SAFE.. Les fonctions CAST et EXTRACT n'acceptent pas non plus le préfixe SAFE.. Pour éviter les erreurs dues au casting, utilisez SAFE_CAST.

Exemple

Dans l'exemple suivant, la première utilisation de la fonction SUBSTR renvoie normalement une erreur, car la fonction n'accepte pas les arguments de longueur comportant des valeurs négatives. Cependant, en raison de la présence du préfixe SAFE., la fonction renvoie NULL à la place. La seconde utilisation de la fonction SUBSTR fournit le résultat attendu : le préfixe SAFE. n'a pas d'effet.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Fonctions compatibles

BigQuery accepte l'utilisation du préfixe SAFE. avec la plupart des fonctions scalaires pouvant générer des erreurs, y compris les fonctions STRING, les fonctions mathématiques, les fonctions DATE, les fonctions DATETIME et les fonctions TIMESTAMP. BigQuery ne permet pas l'utilisation du préfixe SAFE. avec des fonctions d'agrégation, des fonctions analytiques ou des fonctions définies par l'utilisateur.

Appeler des fonctions persistantes définies par l'utilisateur

Après avoir créé une fonction persistante définie par l'utilisateur, vous pouvez l'appeler comme n'importe quelle autre fonction, précédée du nom de l'ensemble de données dans lequel elle est définie en tant que préfixe.

Syntaxe

[`project_name`].dataset_name.function_name([parameter_value[, ...]])

Pour appeler une fonction définie par l'utilisateur dans un projet autre que celui que vous utilisez pour exécuter la requête, l'élément project_name est requis.

Exemples

L'exemple suivant crée une fonction définie par l'utilisateur nommée multiply_by_three, puis l'appelle depuis le même projet.

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

L'exemple suivant appelle une fonction persistante définie par l'utilisateur depuis un projet différent.


CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24

Règles de conversion

La "conversion" comprend, sans toutefois s'y limiter, le casting et la coercition.

Le casting est une conversion explicite qui utilise la fonction CAST().
La coercition est une conversion implicite, que BigQuery effectue automatiquement dans les conditions décrites dans la suite de cette section.

Certaines conversions ont également leurs propres noms de fonction, tels que PARSE_DATE(). Pour en savoir plus sur ces fonctions, consultez la section Fonctions de conversion.

Tableau comparatif

Le tableau ci-dessous récapitule toutes les options disponibles associées à la fonction CAST et à la coercition pour les types de données BigQuery. "Coercition vers" s'applique à toutes les expressions d'un type de données particulier (par exemple, une colonne), mais les littéraux et les paramètres peuvent également faire l'objet d'une coercition. Pour en savoir plus, consultez les sections Coercition de littéraux et Coercition de paramètres.

Type source	Casting vers	Coercition vers
INT64	BOOL INT64 NUMERIC BIGNUMERIC FLOAT64 STRING	NUMERIC BIGNUMERIC FLOAT64
NUMERIC	INT64 NUMERIC BIGNUMERIC FLOAT64 STRING	BIGNUMERIC FLOAT64
BIGNUMERIC	INT64 NUMERIC BIGNUMERIC FLOAT64 STRING	FLOAT64
FLOAT64	INT64 NUMERIC BIGNUMERIC FLOAT64 STRING
BOOL	BOOL INT64 STRING
CHAÎNE	BOOL INT64 NUMERIC BIGNUMERIC FLOAT64 STRING BYTES DATE DATETIME TIME TIMESTAMP
BYTES	STRING BYTES
DATE	STRING DATE DATETIME TIMESTAMP	DATETIME
DATETIME	STRING DATE DATETIME TIME TIMESTAMP
TIME	STRING TIME
TIMESTAMP	STRING DATE DATETIME TIME TIMESTAMP
ARRAY	ARRAY
STRUCT	STRUCT

Casting

La plupart des types de données peuvent être convertis d'un type à un autre à l'aide de la fonction CAST. En cas d'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer le casting. Si vous souhaitez protéger vos requêtes contre ce type d'erreur, vous pouvez utiliser SAFE_CAST. Pour en savoir plus sur les règles CAST et SAFE_CAST, ainsi que sur les autres fonctions de casting, consultez la section Fonctions de conversion.

Coercition

Si nécessaire, BigQuery convertit (par coercition) le type de résultat d'une expression d'argument en un autre type pour faire correspondre les signatures de fonction. Par exemple, si la fonction "func()" est définie pour accepter un seul argument de type FLOAT64 et qu'une expression (dont le type de résultat est INT64) est utilisée en tant qu'argument, le résultat de l'expression sera "converti" dans le type FLOAT64 avant que la valeur de "func()" soit calculée.

Coercition de littéraux

BigQuery permet les coercitions de littéraux suivantes :

Type de données d'entrée	Type de données de résultat	Notes
Littéral de type STRING	DATE DATETIME TIME TIMESTAMP

La coercition de littéraux est nécessaire lorsque le type réel des littéraux est différent du type attendu par la fonction en question. Par exemple, si la fonction func() utilise un argument DATE, l'expression func("2014-09-27") est valide, car le littéral STRING "2014-09-27" est converti par coercition en DATE.

La conversion de littéraux est évaluée au moment de l'analyse et génère une erreur si le littéral d'entrée ne peut pas être converti dans le type cible.

Remarque : Les littéraux de chaîne ne sont pas convertis par coercition en types numériques.

Coercition de paramètres

BigQuery permet les coercitions de paramètres suivantes :

Type de données d'entrée	Type de données de résultat
Paramètre de type STRING

Si la valeur du paramètre ne peut pas être convertie par coercition dans le type cible, une erreur est générée.

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 BigQuery.

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

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 RESPECT NULLS était spécifié. Les lignes pour lesquelles la valeur de expression est définie sur NULL sont prises en compte et peuvent être sélectionnées.

Types d'arguments acceptés

Tous

Clause facultative

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.

Types de données renvoyées

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     |
+-----------+

SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

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 :

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de ARRAY_AGG().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
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 aucune de ces clauses n'est spécifiée, les valeurs NULL sont incluses dans le résultat. Si un tableau du résultat final de la requête contient un élément NULL, une erreur est générée.
ORDER BY : spécifie l'ordre des valeurs.
- Pour chaque clé de tri, le sens de tri par défaut est ASC.
- NULL : dans le contexte de la clause ORDER BY, les valeurs NULL sont la plus petite valeur possible, c'est-à-dire que les valeurs NULL apparaissent en premier dans les tris ASC et en dernier dans les tris DESC.
- Types de données avec virgule flottante : consultez la section Sémantique à virgule flottante portant sur le tri et le regroupement.
- Si DISTINCT est également spécifié, la clé de tri doit être identique à expression.
- Si ORDER BY n'est pas spécifié, l'ordre des éléments dans le tableau de sortie est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.
LIMIT : spécifie le nombre maximal d'entrées expression dans le résultat. La limite n doit être une constante de type INT64.

Type de données renvoyé

ARRAY

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

Exemples

SELECT FORMAT("%T", 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 FORMAT("%T", 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 FORMAT("%T", 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] |
+-------------------+

SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

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

SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

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

SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

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

SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

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 (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 d'arguments acceptés

ARRAY

Clauses facultatives

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

ORDER BY : spécifie l'ordre des valeurs.
- Pour chaque clé de tri, le sens de tri par défaut est ASC.
- Le tri de tableaux n'est pas accepté, et la clé de tri ne peut donc pas être identique à expression.
- NULL : dans le contexte de la clause ORDER BY, les valeurs NULL sont la plus petite valeur possible, c'est-à-dire que les valeurs NULL apparaissent en premier dans les tris ASC et en dernier dans les tris DESC.
- Types de données avec virgule flottante : consultez la section Sémantique à virgule flottante portant sur le tri et le regroupement.
- Si ORDER BY n'est pas spécifié, l'ordre des éléments dans le tableau de sortie est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.
LIMIT : spécifie le nombre maximal d'entrées expression dans le résultat. La limite s'applique au nombre de tableaux d'entrée, pas au nombre d'éléments figurant dans les tableaux. Un tableau vide compte pour une entrée. Un tableau NULL n'est pas comptabilisé. La limite n doit être une constante de type INT64.

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 FORMAT("%T", 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] |
+-----------------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

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

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG([DISTINCT] expression)  [OVER (...)]

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 :

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de AVG().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
RÉSULTAT	FLOAT64	NUMERIC	BIGNUMERIC	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 |
+------+

SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

Description

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

Types d'arguments acceptés

INT64

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(expression)

Description

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

Types d'arguments acceptés

INT64

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)

Description

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

Types d'arguments acceptés

INT64

Clause facultative

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

Types de données renvoyées

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(*) [OVER (...)]

2. COUNT([DISTINCT] expression) [OVER (...)]

Description

Renvoie le nombre de lignes dans l'entrée.
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 :

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Types de données renvoyées

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
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------+------------+--------------+
| x    | count_star | count_dist_x |
+------+------------+--------------+
| 1    | 3          | 2            |
| 4    | 3          | 2            |
| 4    | 3          | 2            |
| 5    | 1          | 1            |
+------+------------+--------------+

SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

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

Clause facultative

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.

Types de données renvoyées

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            |
+--------------+--------------+

SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

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

Type de données renvoyées

BOOL

Exemples

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

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

LOGICAL_OR

LOGICAL_OR(expression)

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

Type de données renvoyées

BOOL

Exemples

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

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

MAX

MAX(expression)  [OVER (...)]

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 : ARRAYSTRUCT GEOGRAPHY

Clause facultative

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.

Types de données renvoyées

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  |
+-----+

SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

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 : ARRAYSTRUCT GEOGRAPHY

Clause facultative

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.

Types de données renvoyées

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   |
+-----+

SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

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 :

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de STRING_AGG().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
ORDER BY : spécifie l'ordre des valeurs.
- Pour chaque clé de tri, le sens de tri par défaut est ASC.
- NULL : dans le contexte de la clause ORDER BY, les valeurs NULL sont la plus petite valeur possible, c'est-à-dire que les valeurs NULL apparaissent en premier dans les tris ASC et en dernier dans les tris DESC.
- Types de données avec virgule flottante : consultez la section Sémantique à virgule flottante portant sur le tri et le regroupement.
- Si DISTINCT est également spécifié, la clé de tri doit être identique à expression.
- Si ORDER BY n'est pas spécifié, l'ordre des éléments dans le tableau de sortie est non déterministe, ce qui implique que vous pouvez recevoir un résultat différent chaque fois que vous utilisez cette fonction.
LIMIT : spécifie le nombre maximal d'entrées expression dans le résultat. La limite s'applique au nombre de chaînes d'entrée, pas au nombre de caractères ou d'octets que contiennent les entrées. Une chaîne vide compte pour une entrée. Une chaîne NULL n'est pas comptabilisée. La limite n doit être une constante de type INT64.

Types de données renvoyées

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 |
+-----------------------+

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

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

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

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

SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

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

SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

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

SUM

SUM([DISTINCT] expression)  [OVER (...)]

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 :

OVER : spécifie une fenêtre. Consultez la page Fonctions analytiques.
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
RÉSULTAT	INT64	NUMERIC	BIGNUMERIC	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
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+

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

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+

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

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

Fonctions d'agrégation statistique

BigQuery accepte les fonctions d'agrégation statistique ci-après.

CORR

CORR(X1, X2)  [OVER (...)]

Description

Renvoie le coefficient de corrélation de Pearson d'un ensemble de paires de nombres. Pour chaque paire de nombres, le premier nombre est la variable dépendante et le second est la variable indépendante. Le résultat est compris entre -1 et 1. Un résultat égal à 0 indique une absence de corrélation.

Tous les types numériques sont acceptés. Si l'entrée est NUMERIC ou BIGNUMERIC, l'agrégation interne est stable avec la sortie finale convertie en FLOAT64. Sinon, l'entrée est convertie en une valeur FLOAT64 avant l'agrégation, ce qui génère un résultat potentiellement instable.

Cette fonction ignore les paires d'entrées qui contiennent au moins une valeur NULL. S'il y a moins de deux paires d'entrées sans valeur NULL, cette fonction renvoie NULL.

Clause facultative

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques.

Type de données renvoyé

FLOAT64

COVAR_POP

COVAR_POP(X1, X2)  [OVER (...)]

Description

Renvoie la covariance de population d'un ensemble de paires de nombres. Le premier nombre est la variable dépendante et le second est la variable indépendante. Le résultat est compris entre -Inf et +Inf.

Cette fonction ignore les paires d'entrées qui contiennent au moins une valeur NULL. En l'absence de paires d'entrées sans valeur NULL, cette fonction renvoie NULL. S'il y a une seule paire d'entrées sans valeur NULL, cette fonction renvoie 0.

Clause facultative

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques.

Type de données renvoyé

FLOAT64

COVAR_SAMP

COVAR_SAMP(X1, X2)  [OVER (...)]

Description

Renvoie la covariance d'échantillon d'un ensemble de paires de nombres. Le premier nombre est la variable dépendante et le second est la variable indépendante. Le résultat est compris entre -Inf et +Inf.

Cette fonction ignore les paires d'entrées qui contiennent au moins une valeur NULL. S'il y a moins de deux paires d'entrées sans valeur NULL, cette fonction renvoie NULL.

Clause facultative

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques.

Type de données renvoyé

FLOAT64

STDDEV_POP

STDDEV_POP([DISTINCT] expression)  [OVER (...)]

Description

Renvoie l'écart type (biaisé) de population des valeurs. Le résultat renvoyé est compris entre 0 et +Inf.

Cette fonction ignore toutes les entrées NULL. Si toutes les entrées sont ignorées, elle renvoie NULL.

Si cette fonction reçoit une seule entrée non-NULL, elle renvoie 0.

Clauses facultatives

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

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de STDDEV_POP().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

FLOAT64

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression)  [OVER (...)]

Description

Renvoie l'écart type (non biaisé) d'échantillon des valeurs. Le résultat renvoyé est compris entre 0 et +Inf.

Cette fonction ignore toutes les entrées NULL. S'il y a moins de deux entrées non-NULL, elle renvoie NULL.

Clauses facultatives

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

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de STDDEV_SAMP().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

FLOAT64

STDDEV

STDDEV([DISTINCT] expression)  [OVER (...)]

Description

Un alias de STDDEV_SAMP.

VAR_POP

VAR_POP([DISTINCT] expression)  [OVER (...)]

Description

Renvoie la variance (biaisée) de population des valeurs. Le résultat renvoyé est compris entre 0 et +Inf.

Cette fonction ignore toutes les entrées NULL. Si toutes les entrées sont ignorées, elle renvoie NULL.

Si cette fonction reçoit une seule entrée non-NULL, elle renvoie 0.

Clauses facultatives

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

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de VAR_POP().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

FLOAT64

VAR_SAMP

VAR_SAMP([DISTINCT] expression)  [OVER (...)]

Description

Renvoie la variance (non biaisée) d'échantillon des valeurs. Le résultat renvoyé est compris entre 0 et +Inf.

Cette fonction ignore toutes les entrées NULL. S'il y a moins de deux entrées non-NULL, elle renvoie NULL.

Clauses facultatives

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

OVER : spécifie une fenêtre. Consultez la page sur les fonctions analytiques. Cette clause est actuellement incompatible avec toutes les autres clauses de VAR_SAMP().
DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.

Type de données renvoyé

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression)  [OVER (...)]

Description

Un alias de VAR_SAMP.

Fonctions d'agrégation approximative

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. Ces fonctions requièrent généralement moins de mémoire que les fonctions d'agrégation exacte telles que COUNT(DISTINCT ...), mais elles engendrent également une incertitude statistique. Cela rend l'agrégation approximative appropriée pour les flux de données volumineux pour lesquels l'utilisation linéaire de la mémoire est difficile, ainsi que pour les données qui sont déjà approximatives.

Les fonctions d'agrégation approximative de cette section fonctionnent directement sur les données d'entrée, plutôt que sur une estimation intermédiaire des données. Ces fonctions ne permettent pas aux utilisateurs de spécifier la précision de l'estimation avec des résumés. Si vous souhaitez spécifier la précision avec des résumés, consultez les sections suivantes :

Fonctions HyperLogLog++ pour estimer la cardinalité.

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ées

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 :

DISTINCT : chaque valeur distincte d'expression n'est agrégée qu'une seule fois dans le résultat.
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 aucune valeur n'est spécifiée, les valeurs NULL sont exclues du résultat. Si un tableau du résultat final de la requête contient un élément NULL, une erreur est générée.

Type de données renvoyé

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 de la valeur expression donne NULL pour toutes les lignes.

Exemples

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([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([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.

Type de données renvoyé

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 count contient une valeur 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 l'un des éléments suivants :

INT64
NUMERIC
BIGNUMERIC
FLOAT64

number doit être de type INT64.

Type de données renvoyé

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}] |
+----------------------------+

Fonctions HyperLogLog++

L'algorithme HyperLogLog++ (HLL++) estime la cardinalité à partir des résumés. Si vous ne souhaitez pas utiliser les résumés et que vous n'avez pas besoin d'une précision personnalisée, envisagez d'utiliser des fonctions d'agrégation approximative avec une précision définie par le système.

Les fonctions HLL++ sont des fonctions d'agrégation approximative. Elles requièrent généralement moins de mémoire que les fonctions d'agrégation exacte, telles que COUNT(DISTINCT), mais elles engendrent également une incertitude statistique. Ainsi, les fonctions HLL++ sont adaptées aux flux de données volumineux pour lesquels l'utilisation linéaire de la mémoire est difficile, ainsi que pour les données qui sont déjà approximatives.

BigQuery accepte les fonctions HLL++ suivantes :

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

Description

Fonction d'agrégation qui prend une ou plusieurs valeurs input et les agrège dans un nouveau résumé HLL++. Chaque résumé est représenté à l'aide du type de données BYTES. Vous pouvez ensuite fusionner des résumés à l'aide de HLL_COUNT.MERGE ou de HLL_COUNT.MERGE_PARTIAL. Si aucune fusion n'est nécessaire, vous pouvez extraire le nombre final de valeurs distinctes du résumé à l'aide de HLL_COUNT.EXTRACT.

Cette fonction accepte un paramètre facultatif, precision. Ce dernier définit la précision de l'estimation au détriment de la mémoire supplémentaire requise pour traiter les résumés ou les stocker sur disque. Le tableau suivant indique les valeurs de précision autorisées, la taille maximale de résumé par groupe et l'intervalle de confiance (IC) des précisions types :

Précision	Taille maximale de résumé (Kio)	IC à 65 %	IC à 95 %	IC à 99 %
10	1	±3,25 %	±6,50 %	±9,75 %
11	2	±2,30 %	±4,60 %	±6,89 %
12	4	±1,63 %	±3,25 %	±4,88 %
13	8	±1,15 %	±2,30 %	±3,45 %
14	16	±0,81 %	±1,63 %	±2,44 %
15 (par défaut)	32	±0,57 %	±1,15 %	±1,72 %
16	64	±0,41 %	±0,81 %	±1,22 %
17	128	±0,29 %	±0,57 %	±0,86 %
18	256	±0,20 %	±0,41 %	±0,61 %
19	512	±0,14 %	±0,29 %	±0,43 %
20	1 024	±0,10 %	±0,20 %	±0,30 %
21	2 048	±0,07 %	±0,14 %	±0,22 %
22	4 096	±0,05 %	±0,10 %	±0,15 %
23	8 192	±0,04 %	±0,07 %	±0,11 %
24	16 384	±0,03 %	±0,05 %	±0,08 %

Si l'entrée est NULL, cette fonction renvoie NULL.

Pour plus d'informations, consultez l'article HyperLogLog dans la pratique : Ingénierie algorithmique d'un algorithme avancé d'estimation de la cardinalité.

Types d'entrée acceptés

INT64, NUMERIC, BIGNUMERIC, STRING, BYTES

Type renvoyé

BYTES

Exemple

SELECT
  HLL_COUNT.INIT(respondent) AS respondents_hll,
  flavor,
  country
FROM UNNEST([
  STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
  (1, "Chocolate", "CH"),
  (2, "Chocolate", "US"),
  (2, "Strawberry", "US")])
GROUP BY flavor, country;

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

Description

Fonction d'agrégation qui renvoie la cardinalité de plusieurs résumés HLL++ en calculant leur union.

Chaque sketch doit avoir la même précision et être initialisée sur le même type. Les tentatives de fusion de résumés ayant des précisions différentes ou pour des types différents entraînent une erreur. Par exemple, vous ne pouvez pas fusionner un résumé initialisé à partir de données INT64 avec un résumé initialisé à partir de données STRING.

Cette fonction ignore les valeurs NULL lors de la fusion de résumés. Si la fusion ne se produit sur aucune ligne ou seulement sur des valeurs NULL, la fonction renvoie 0.

Types d'entrée acceptés

BYTES

Type renvoyé

INT64

Exemple

SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

Description

Fonction d'agrégation qui prend une ou plusieurs entrées sketch HLL++ et les fusionne dans un nouveau résumé.

Cette fonction renvoie NULL s'il n'y a aucune entrée ou si toutes les entrées sont NULL.

Types d'entrée acceptés

BYTES

Type renvoyé

BYTES

Exemple

SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

Description

Fonction scalaire qui extrait une estimation de la cardinalité d'un seul résumé HLL++.

Si sketch est NULL, cette fonction renvoie une estimation de la cardinalité égale à 0.

Types d'entrée acceptés

BYTES

Type renvoyé

INT64

Exemple

SELECT
  flavor,
  country,
  HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country);

+------------+---------+-----------------+
| flavor     | country | num_respondents |
+------------+---------+-----------------+
| Vanilla    | CH      | 1               |
| Chocolate  | CH      | 1               |
| Chocolate  | US      | 1               |
| Strawberry | US      | 1               |
+------------+---------+-----------------+

À propos de l'algorithme HLL++

L'algorithme HLL++ améliore l'algorithme HLL en évaluant plus précisément les cardinalités de toutes tailles. L'algorithme HLL++ inclut une fonction de hachage 64 bits, une représentation creuse pour réduire les besoins en mémoire, ainsi que la correction de biais empirique pour les faibles estimations de cardinalité.

À propos des résumés

Un résumé est une synthèse d'un flux de données volumineux. Vous pouvez extraire des statistiques d'un résumé pour estimer des statistiques particulières des données d'origine ou fusionner des résumés pour synthétiser plusieurs flux de données. Un résumé présente les caractéristiques suivantes :

Il compresse les données brutes en une représentation en mémoire fixe.
Il est asymptotiquement plus petit que l'entrée.
Il s'agit de la forme sérialisée d'une structure de données sous-linéaire en mémoire.
Il nécessite généralement moins de mémoire que l'entrée utilisée pour la création.

Les résumés permettent l'intégration à d'autres systèmes. Par exemple, il est possible de créer des résumés dans des applications externes, telles que Cloud Dataflow, ou Apache Spark, et de les utiliser dans BigQuery et inversement. Les résumés permettent également de créer des agrégations intermédiaires pour les fonctions non additives telles que COUNT(DISTINCT).

Fonctions de numérotation

Les sections suivantes décrivent les fonctions de navigation acceptées par BigQuery. Ces fonctions sont un sous-ensemble des fonctions analytiques. Pour plus d'informations sur le fonctionnement des fonctions analytiques, consultez la page Concepts de fonction analytique. Pour en savoir plus sur l'utilisation des fonctions de numérotation, consultez la page Concepts des fonctions de numérotation.

Exigences de la clause OVER :

PARTITION BY : facultatif.
ORDER BY : obligatoire, sauf pour ROW_NUMBER().
window_frame_clause : non autorisée.

RANK

Description

Renvoie le rang ordinal (de base 1) de chaque ligne de la partition triée. Toutes les lignes homologues reçoivent la même valeur de rang. La ligne ou l'ensemble de lignes homologues suivant reçoit une valeur de rang qui est incrémentée en fonction du nombre d'homologues ayant le rang précédent, contrairement à DENSE_RANK, qui incrémente toujours la valeur d'une unité.

Types d'arguments acceptés

INT64

DENSE_RANK

Description

Renvoie le rang ordinal (de base 1) de chaque ligne de la partition de fenêtre. Toutes les lignes homologues reçoivent la même valeur de rang, et la valeur de rang suivante est incrémentée d'une unité.

Types d'arguments acceptés

INT64

PERCENT_RANK

Description

Renvoie le rang en centile d'une ligne, défini par (RK-1)/(NR-1). RK correspond au rang (RANK) de la ligne et NR correspond au nombre de lignes dans la partition. Renvoie 0 si NR=1.

Types d'arguments acceptés

FLOAT64

CUME_DIST

Description

Renvoie le rang relatif d'une ligne, défini par NP/NR. NP correspond au nombre de lignes qui précèdent la ligne actuelle ou qui en sont les homologues. NR correspond au nombre de lignes dans la partition.

Types d'arguments acceptés

FLOAT64

NTILE

NTILE(constant_integer_expression)

Description

Cette fonction divise les lignes en ensembles constant_integer_expression en fonction de l'ordre des lignes et renvoie le numéro d'ensemble en base 1 attribué à chaque ligne. Le nombre de lignes dans les ensembles peut différer d'une unité au maximum. Les valeurs restantes (le nombre de lignes restantes divisé par le nombre d'ensembles) sont distribuées de sorte qu'il y en ait une par ensemble, en commençant par l'ensemble 1. Si constant_integer_expression renvoie une valeur négative, NULL ou 0, une erreur est générée.

Types d'arguments acceptés

INT64

ROW_NUMBER

Description

Ne nécessite pas la clause ORDER BY. Renvoie le numéro ordinal séquentiel (de base 1) de chaque ligne pour chaque partition triée. Si la clause ORDER BY n'est pas spécifiée, le résultat est non déterministe.

Types d'arguments acceptés

INT64

Fonctions sur bits

BigQuery accepte les fonctions sur bits ci-après.

BIT_COUNT

BIT_COUNT(expression)

Description

L'entrée, expression, doit être un entier ou de type BYTES.

Renvoie le nombre de bits définis dans l'entrée expression. Pour les entiers signés, il s'agit du nombre de bits sous forme de complément à deux.

Type de données renvoyé

INT64

Exemple

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

Fonctions de conversion

BigQuery accepte les fonctions de conversion suivantes. Ces conversions de type de données sont explicites, mais certaines conversions peuvent se produire de manière implicite. Vous pouvez en savoir plus sur la conversion implicite et explicite avec ce lien.

Présentation de CAST

CAST(expression AS typename)

Description

La syntaxe "Cast" est utilisée dans une requête pour indiquer que le type de résultat d'une expression doit être converti dans un autre type.

En cas d'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer le casting. Si vous souhaitez protéger vos requêtes contre ce type d'erreur, vous pouvez utiliser SAFE_CAST.

Lors des castings entre des types compatibles, s'il est impossible d'établir une correspondance entre la valeur d'origine et le domaine cible, des erreurs d'exécution se produisent. Par exemple, si vous tentez de caster une valeur BYTES en STRING alors que la séquence d'octets n'est pas une valeur UTF-8 valide, une erreur d'exécution se produit.

Exemples

La requête suivante renvoie "true" si la valeur de x est 1, "false" pour toutes les autres valeurs qui ne sont pas NULL et NULL si x a la valeur NULL.

CAST(x=1 AS STRING)

CAST AS ARRAY

CAST(expression AS ARRAY<element_type>)

Description

BigQuery accepte le casting vers le type ARRAY. Le paramètre expression peut représenter une expression pour les types de données suivants :

ARRAY

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
ARRAY	ARRAY	Le type ARRAY doit être exactement le même.

CAST AS BIGNUMERIC

CAST(expression AS BIGNUMERIC)

Description

BigQuery accepte le casting vers le type BIGNUMERIC. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
FLOAT64
NUMERIC
BIGNUMERIC
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
FLOAT64	BIGNUMERIC	Le nombre à virgule flottante sera arrondi à la valeur la plus éloignée à mi-chemin du zéro. Si vous castez une valeur `NaN`, `+inf` ou `-inf`, une erreur est renvoyée. Le casting d'une valeur non comprise dans la plage de `BIGNUMERIC` renvoie une erreur de dépassement de capacité.
CHAÎNE	BIGNUMERIC	Le littéral numérique contenu dans la valeur `STRING` ne doit pas dépasser la plage ou la précision maximale du type `BIGNUMERIC` sous peine de produire une erreur. Si le nombre de chiffres après la virgule est supérieur à 38, la valeur `BIGNUMERIC` résultante sera arrondie à la valeur la plus éloignée à mi-chemin du zéro de sorte qu'elle comporte 38 chiffres après la virgule.

CAST AS BOOL

CAST(expression AS BOOL)

Description

BigQuery accepte le casting vers le type BOOL. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
BOOL
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
INT64	BOOL	Renvoie `FALSE` si la valeur de `x` est `0`, ou `TRUE` si ce n'est pas le cas.
CHAÎNE	BOOL	Renvoie `TRUE` si la valeur de `x` est `"true"` et `FALSE` si la valeur de `x` est `"false"`. Toutes les autres valeurs de `x` sont considérées comme non valides. Elles génèrent une erreur au lieu d'être castées en BOOL. Les valeurs STRING ne sont pas sensibles à la casse lorsqu'elles sont converties en BOOL.

CAST AS BYTES

CAST(expression AS BYTES)

Description

BigQuery accepte le casting vers le type BYTES. Le paramètre expression peut représenter une expression pour les types de données suivants :

BYTES
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
CHAÎNE	BYTES	Les STRING sont converties en BYTES par le biais de l'encodage UTF-8. Par exemple, la conversion en BYTES de la STRING "©" donne une séquence de 2 octets dont les valeurs hexadécimales sont C2 et A9.

CAST AS DATE

CAST(expression AS DATE)

Description

BigQuery accepte le casting vers le type DATE. Le paramètre expression peut représenter une expression pour les types de données suivants :

CHAÎNE
TIME
DATETIME
TIMESTAMP

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
CHAÎNE	DATE	Le casting d'une chaîne en date ne dépend pas du fuseau horaire et la chaîne doit être conforme au format de littéral de date accepté. Si l'expression de chaîne n'est pas valide ou représente une date en dehors de la plage de valeurs minimale-maximale acceptée, une erreur est générée.
TIMESTAMP	DATE	Le casting d'un horodatage en date tronque effectivement l'horodatage du fuseau horaire par défaut.

CAST AS DATETIME

CAST(expression AS DATETIME)

Description

BigQuery accepte le casting vers le type DATETIME. Le paramètre expression peut représenter une expression pour les types de données suivants :

CHAÎNE
TIME
DATETIME
TIMESTAMP

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
CHAÎNE	DATETIME	Le casting d'une chaîne en date/heure ne dépend pas du fuseau horaire et la chaîne doit être conforme au format de la valeur littérale de date/heure acceptée. Si l'expression de chaîne n'est pas valide ou représente une date/heure en dehors de la plage de valeurs minimale-maximale acceptée, une erreur est générée.
TIMESTAMP	DATETIME	Le casting d'un horodatage en date/heure tronque effectivement l'horodatage du fuseau horaire par défaut.

CAST AS FLOAT64

CAST(expression AS FLOAT64)

Description

BigQuery accepte le casting vers les types à virgule flottante. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
FLOAT64
NUMERIC
BIGNUMERIC
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
INT64	FLOAT64	Renvoie une valeur à virgule flottante proche, mais potentiellement non exacte.
NUMERIC	FLOAT64	NUMERIC convertira l'expression en nombre à virgule flottante le plus proche avec une possible perte de précision.
BIGNUMERIC	FLOAT64	BIGNUMERIC convertira l'expression en nombre à virgule flottante le plus proche avec une possible perte de précision.
CHAÎNE	FLOAT64	Renvoie `x` sous la forme d'une valeur à virgule flottante, en l'interprétant comme ayant le même format qu'une valeur littérale à virgule flottante valide. Accepte également le casting de `"[+,-]inf"` à `[,-]Infinity`, `"[+,-]infinity"` à `[,-]Infinity` et `"[+,-]nan"` à `NaN`. Les conversions ne sont pas sensibles à la casse.

CAST AS INT64

CAST(expression AS INT64)

Description

BigQuery accepte le casting vers les types entiers. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
FLOAT64
NUMERIC
BIGNUMERIC
BOOL
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
FLOAT64	INT64	Renvoie la valeur entière la plus proche. Les cas à mi-chemin tels que 1,5 ou -0,5 sont arrondis à la valeur la plus éloignée de zéro.
BOOL	INT64	Renvoie `1` si la valeur de `x` est `TRUE`, ou `0` si ce n'est pas le cas.
CHAÎNE	INT64	Une chaîne hexadécimale peut être castée en nombre entier. Par exemple, la valeur `0x123` en `291` ou la valeur `-0x123` en `-291`.

Exemples

Si vous utilisez des chaînes hexadécimales (0x123), vous pouvez les caster en nombres entiers :

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123    | -291       |
+-----------+------------+

CAST AS NUMERIC

CAST(expression AS NUMERIC)

Description

BigQuery accepte le casting vers le type NUMERIC. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
FLOAT64
NUMERIC
BIGNUMERIC
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
FLOAT64	NUMERIC	Le nombre à virgule flottante sera arrondi à la valeur la plus éloignée à mi-chemin du zéro. Si vous castez une valeur `NaN`, `+inf` ou `-inf`, une erreur est renvoyée. Le casting d'une valeur non comprise dans la plage de `NUMERIC` renvoie une erreur de dépassement de capacité.
CHAÎNE	NUMERIC	Le littéral numérique contenu dans la valeur `STRING` ne doit pas dépasser la plage ou la précision maximale du type `NUMERIC` sous peine de produire une erreur. Si le nombre de chiffres après la virgule est supérieur à neuf, la valeur `NUMERIC` résultante sera arrondie à la valeur la plus éloignée à mi-chemin du zéro de sorte qu'elle comporte neuf chiffres après la virgule.

CAST AS STRING

CAST(expression AS STRING)

Description

BigQuery accepte le casting vers le type STRING. Le paramètre expression peut représenter une expression pour les types de données suivants :

INT64
FLOAT64
NUMERIC
BIGNUMERIC
BOOL
BYTES
TIME
DATE
DATETIME
TIMESTAMP
CHAÎNE

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
FLOAT64	STRING	Renvoie une représentation de chaîne approximative.
BOOL	CHAÎNE	Renvoie `"true"` si la valeur de `x` est `TRUE`, ou `"false"` si ce n'est pas le cas.
BYTES	STRING	Renvoie `x` interprété en tant que STRING UTF-8. Par exemple, en cas de casting du littéral BYTES `b'\xc2\xa9'` en STRING, ce littéral est interprété en tant que valeur UTF-8 et se présente sous la forme du caractère Unicode "©". Une erreur se produit si `x` n'est pas une valeur UTF-8 valide.
TIME	CHAÎNE	Le casting d'un type d'heure en chaîne ne dépend pas du fuseau horaire et son résultat se présente au format `HH:MM:SS`.
DATE	CHAÎNE	Le casting d'un type de date en chaîne ne dépend pas du fuseau horaire et son résultat se présente sous la forme `YYYY-MM-DD`.
DATETIME	CHAÎNE	Le casting d'un type de date/heure en chaîne ne dépend pas du fuseau horaire et son résultat se présente au format `YYYY-MM-DD HH:MM:SS`.
TIMESTAMP	CHAÎNE	Lors du casting de types d'horodatage en chaînes, l'horodatage est interprété à l'aide du fuseau horaire par défaut, à savoir UTC. Le nombre de décimales (valeurs inférieures à la seconde) générées dépend du nombre de zéros dans la partie inférieure à la seconde : la fonction CAST tronquera zéro, trois ou six chiffres.

Exemples

CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+

CAST AS STRUCT

CAST(expression AS STRUCT)

Description

BigQuery accepte le casting vers le type STRUCT. Le paramètre expression peut représenter une expression pour les types de données suivants :

STRUCT

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
STRUCT	STRUCT	Autorisé si les conditions suivantes sont remplies : Les deux STRUCT ont le même nombre de champs. Les types de champ STRUCT d'origine peuvent être explicitement convertis en types de champ STRUCT cibles correspondants (tels que définis par l'ordre des champs, et non par leur nom).

CAST AS TIME

CAST(expression AS TIME)

Description

BigQuery accepte le casting vers le type TIME. Le paramètre expression peut représenter une expression pour les types de données suivants :

CHAÎNE
TIME
DATETIME
TIMESTAMP

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
CHAÎNE	TIME	Le casting d'une chaîne en heure ne dépend pas du fuseau horaire et la chaîne doit être conforme au format de la valeur littérale d'heure acceptée. Si l'expression de chaîne n'est pas valide ou représente une heure en dehors de la plage de valeurs minimale-maximale acceptée, une erreur est générée.

CAST AS TIMESTAMP

CAST(expression AS TIMESTAMP)

Description

BigQuery accepte le casting vers le type TIMESTAMP. Le paramètre expression peut représenter une expression pour les types de données suivants :

CHAÎNE
TIME
DATETIME
TIMESTAMP

Règles de conversion

De	Jusqu'au	Règle(s) appliquée(s) lors du casting de `x`
CHAÎNE	TIMESTAMP	Lors du casting d'une chaîne en horodatage, `string_expression` doit être conforme aux formats de littéral d'horodatage acceptés. Dans le cas contraire, une erreur d'exécution se produit. L'expression `string_expression` peut contenir un fuseau horaire. Si un fuseau horaire est spécifié dans `string_expression`, il est utilisé pour la conversion. Dans le cas contraire, le fuseau horaire par défaut (UTC) est utilisé. Si la chaîne comporte moins de six chiffres, elle est implicitement élargie. Une erreur est générée si l'expression `string_expression` n'est pas valide, si elle comporte plus de six décimales (c.-à-d. si la précision est supérieure à la microseconde) ou si elle représente une valeur temporelle en dehors de la plage d'horodatage acceptée.
DATE	TIMESTAMP	Le casting d'une date en horodatage interprète `date_expression` comme heure de début de la journée (minuit) dans le fuseau horaire par défaut (UTC).
DATETIME	TIMESTAMP	Le casting d'une date/heure en horodatage interprète `datetime_expression` comme heure de début de la journée (minuit) dans le fuseau horaire par défaut (UTC).

SAFE_CAST

SAFE_CAST(expression AS typename)

Description

En cas d'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer le casting. Par exemple, la requête suivante génère une erreur :

SELECT CAST("apple" AS INT64) AS not_a_number;

Si vous souhaitez protéger vos requêtes contre ce type d'erreur, vous pouvez utiliser SAFE_CAST. La fonction SAFE_CAST est identique à CAST, sauf qu'elle renvoie NULL au lieu de générer une erreur.

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

Si vous castez des octets en chaînes, vous pouvez également utiliser la fonction SAFE_CONVERT_BYTES_TO_STRING. Tous les caractères UTF-8 non valides sont remplacés par le caractère de remplacement Unicode U+FFFD. Pour en savoir plus, consultez la section sur la fonction SAFE_CONVERT_BYTES_TO_STRING.

Autres fonctions de conversion

Pour en savoir plus sur ces fonctions de conversion, consultez les autres pages de documentation suivantes :

Fonction de conversion	De	To
ARRAY_TO_STRING	ARRAY	CHAÎNE
DATE	Différents types de données	DATE
DATETIME	Différents types de données	DATETIME
FROM_BASE32	CHAÎNE	BYTES
FROM_BASE64	CHAÎNE	BYTES
FROM_HEX	CHAÎNE	BYTES
PARSE_DATE	CHAÎNE	DATE
PARSE_DATETIME	CHAÎNE	DATETIME
PARSE_TIME	CHAÎNE	TIME
PARSE_TIMESTAMP	CHAÎNE	TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING	BYTES	CHAÎNE
STRING	TIMESTAMP	CHAÎNE
TIME	Différents types de données	TIME
TIMESTAMP	Différents types de données	TIMESTAMP
TO_BASE32	BYTES	CHAÎNE
TO_BASE64	BYTES	CHAÎNE
TO_HEX	BYTES	CHAÎNE

Fonctions mathématiques

Toutes les fonctions mathématiques se comportent de la façon suivante :

Elles renvoient NULL si l'un des paramètres d'entrée est NULL.
Elles renvoient NaN si l'un des arguments est NaN.

ABS

ABS(X)

Description

Calcule la valeur absolue. Renvoie une erreur si l'argument est un entier et que la valeur de sortie ne peut pas être représentée avec le même type. Cela ne peut se produire que pour la plus grande valeur d'entrée négative, qui n'a pas de représentation positive.

X	ABS(X)
25	25
-25	25
`+inf`	`+inf`
`-inf`	`+inf`

Type de données renvoyé

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

SIGN

SIGN(X)

Description

Renvoie respectivement -1, 0 ou +1 pour les arguments "négatif", "zéro" et "positif". Pour les arguments à virgule flottante, cette fonction ne fait pas la distinction entre zéro positif et zéro négatif.

X	SIGN(X)
25	+1
0	0
-25	-1
NaN	NaN

Type de données renvoyé

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

IS_INF

IS_INF(X)

Description

Renvoie TRUE si la valeur correspond à l'infini positif ou négatif.

X	IS_INF(X)
`+inf`	`TRUE`
`-inf`	`TRUE`
25	`FALSE`

IS_NAN

IS_NAN(X)

Description

Renvoie TRUE si la valeur est une valeur NaN.

X	IS_NAN(X)
`NaN`	`TRUE`
25	`FALSE`

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Description

Divise X par Y ; cette fonction n'échoue jamais. Renvoie une valeur FLOAT64. Contrairement à l'opérateur de division (/), cette fonction ne génère pas d'erreur en cas de division par zéro ou de débordement.

X	O	IEEE_DIVIDE(X, Y)
20.0	4.0	5,0
0,0	25,0	0,0
25,0	0,0	`+inf`
-25,0	0,0	`-inf`
0,0	0,0	`NaN`
0,0	`NaN`	`NaN`
`NaN`	0,0	`NaN`
`+inf`	`+inf`	`NaN`
`-inf`	`-inf`	`NaN`

RAND

RAND()

Description

Génère une valeur pseudo-aléatoire de type FLOAT64 dans la plage [0, 1), 0 étant inclus et 1 étant exclu.

SQRT

SQRT(X)

Description

Calcule la racine carrée de X. Génère une erreur si X est inférieur à 0.

X	SQRT(X)
`25.0`	`5.0`
`+inf`	`+inf`
`X < 0`	Erreur

Type de données renvoyé

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

POW

POW(X, Y)

Description

Renvoie la valeur de X élevée à la puissance Y. Si le résultat est insuffisant et qu'il n'est pas représentable, la fonction renvoie une valeur "zéro".

X	O	POW(X, Y)
2.0	3,0	8.0
1.0	Toute valeur, y compris `NaN`	1.0
Toute valeur, y compris `NaN`	0	1,0
-1,0	`+inf`	1.0
-1,0	`-inf`	1,0
ABS(X) < 1	`-inf`	`+inf`
ABS(X) > 1	`-inf`	0,0
ABS(X) < 1	`+inf`	0,0
ABS(X) > 1	`+inf`	`+inf`
`-inf`	Y < 0	0,0
`-inf`	Y > 0	`-inf` si Y est un entier impair, `+inf` dans le cas contraire
`+inf`	Y < 0	0
`+inf`	Y > 0	`+inf`
Valeur finie < 0	Non entier	Erreur
0	Valeur finie < 0	Erreur

Type de données renvoyé

Le type de données renvoyé est déterminé par les types des arguments à l'aide du tableau suivant.

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

POWER

POWER(X, Y)

Description

Synonyme de POW(X, Y).

EXP

EXP(X)

Description

Calcule e à la puissance X. Cette fonction est également appelée "fonction exponentielle naturelle". Si le résultat est insuffisant, cette fonction renvoie un zéro. Elle génère une erreur si le résultat déborde.

X	EXP(X)
0,0	1.0
`+inf`	`+inf`
`-inf`	0,0

Type de données renvoyé

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

LN

LN(X)

Description

Calcule le logarithme népérien de X. Génère une erreur si X est inférieur ou égal à zéro.

X	LN(X)
1.0	0,0
`+inf`	`+inf`
`X < 0`	Erreur

Type de données renvoyé

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

LOG

LOG(X [, Y])

Description

Si seul X est présent, LOG est un synonyme de LN. Si Y est également présent, LOG calcule le logarithme de X en base Y.

X	Y	LOG(X, Y)
100,0	10,0	2.0
`-inf`	Toute valeur	`NaN`
Toute valeur	`+inf`	`NaN`
`+inf`	0,0 < Y < 1,0	`-inf`
`+inf`	Y > 1,0	`+inf`
X <= 0	Toute valeur	Erreur
Toute valeur	Y <= 0	Erreur
Toute valeur	1.0	Erreur

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

LOG10

LOG10(X)

Description

Identique à LOG, mais calcule le logarithme en base 10.

X	LOG10(X)
100,0	2.0
`-inf`	`NaN`
`+inf`	`NaN`
X <= 0	Erreur

Type de données renvoyé

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

GREATEST

GREATEST(X1,...,XN)

Description

Renvoie la plus grande valeur parmi "X1,...,XN" (identifiée à l'aide de la comparaison "<"). Si une partie de X1,...,XN est NULL, la valeur renvoyée est NULL.

X1,...,XN	GREATEST(X1,...,XN)
3,5,1	5

Type de données renvoyé

Type de données des valeurs d'entrée

LEAST

LEAST(X1,...,XN)

Description

Renvoie la plus petite valeur parmi "X1,...,XN" (identifiée à l'aide de la comparaison ">"). Si une partie de X1,...,XN est NULL, la valeur renvoyée est NULL.

X1,...,XN	LEAST(X1,...,XN)
3,5,1	1

Type de données renvoyé

Type de données des valeurs d'entrée

DIV

DIV(X, Y)

Description

Renvoie le résultat de la division d'un entier X par un entier Y. Une division par zéro renvoie une erreur. Une division par -1 peut entraîner un débordement.

X	O	DIV(X, Y)
20	4	5
0	20	0
20	0	Erreur

Type de données renvoyé

Le type de données renvoyé est déterminé par les types des arguments à l'aide du tableau suivant.

ENTRÉE	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Description

Équivaut à l'opérateur de division (X / Y), mais renvoie NULL si une erreur se produit, par exemple une erreur de division par zéro.

X	O	SAFE_DIVIDE(X, Y)
20	4	5
0	20	`0`
20	0	`NULL`

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Description

Équivaut à l'opérateur de multiplication (*), mais renvoie NULL si un débordement se produit.

X	O	SAFE_MULTIPLY(X, Y)
20	4	80

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

Description

Équivaut à l'opérateur moins unaire (-), mais renvoie NULL en cas de débordement.

X	SAFE_NEGATE(X)
+1	-1
-1	+1
0	0

Type de données renvoyé

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

SAFE_ADD

SAFE_ADD(X, Y)

Description

Équivaut à l'opérateur d'addition (+), mais renvoie NULL en cas de débordement.

X	O	SAFE_ADD(X, Y)
5	4	9

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Description

Renvoie le résultat de Y soustrait de X. Équivaut à l'opérateur de soustraction (-), mais renvoie NULL en cas de débordement.

X	O	SAFE_SUBTRACT(X, Y)
5	4	1

Type de données renvoyé

ENTRÉE	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

MOD

MOD(X, Y)

Description

Fonction modulo : renvoie le reste de la division de X par Y. La valeur renvoyée a le même signe que X. Une erreur est générée si Y est égal à 0.

X	O	MOD(X, Y)
25	12	1
25	0	Erreur

Type de données renvoyé

Le type de données renvoyé est déterminé par les types des arguments à l'aide du tableau suivant.

ENTRÉE	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

ARRONDI

ROUND(X [, N])

Description

Si seul X est présent, ROUND arrondit X au nombre entier le plus proche. Si N est présent, ROUND arrondit X à N décimales après la virgule. Si N est négatif, ROUND arrondit les chiffres à gauche de la virgule. Cette fonction arrondit les cas à mi-chemin à la valeur la plus éloignée de zéro. Cette fonction génère une erreur en cas de débordement.

X	ROUND(X)
2.0	2,0
2,3	2.0
2,8	3,0
2.5	3,0
-2,3	-2,0
-2,8	-3,0
-2,5	-3,0
0	0
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

Type de données renvoyé

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

TRUNC

TRUNC(X [, N])

Description

Si seul X est présent, TRUNC arrondit X au nombre entier le plus proche dont la valeur absolue n'est pas supérieure à celle de X. Si N est également présent, la fonction TRUNC se comporte comme ROUND(X, N), mais elle arrondit toujours en direction de zéro et ne déborde jamais.

X	TRUNC(X)
2.0	2,0
2,3	2.0
2.8	2.0
2.5	2.0
-2,3	-2,0
-2,8	-2,0
-2,5	-2,0
0	0
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

Type de données renvoyé

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

CEIL

CEIL(X)

Description

Renvoie la plus petite valeur intégrale parmi celles qui ne sont pas inférieures à X.

X	CEIL(X)
2.0	2,0
2,3	3,0
2.8	3,0
2.5	3,0
-2,3	-2,0
-2,8	-2,0
-2,5	-2,0
0	0
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

Type de données renvoyé

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

CEILING

CEILING(X)

Description

Synonyme de CEIL(X)

FLOOR

FLOOR(X)

Description

Renvoie la plus grande valeur intégrale parmi celles qui ne sont pas supérieures à X.

X	FLOOR(X)
2,0	2,0
2,3	2.0
2.8	2.0
2.5	2.0
-2,3	-3,0
-2,8	-3,0
-2,5	-3,0
0	0
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

Type de données renvoyé

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

COS

COS(X)

Description

Calcule le cosinus de X où X est spécifié en radians. Cette fonction n'échoue jamais.

X	COS(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`

COSH

COSH(X)

Description

Calcule le cosinus hyperbolique de X où X est spécifié en radians. Cette fonction génère une erreur en cas de débordement.

X	COSH(X)
`+inf`	`+inf`
`-inf`	`+inf`
`NaN`	`NaN`

ACOS

ACOS(X)

Description

Calcule la valeur principale du cosinus inverse de X. La valeur renvoyée est comprise dans l'intervalle [0, π]. Cette fonction génère une erreur si X se situe en dehors de la plage [-1, 1].

X	ACOS(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
X < -1	Erreur
X > 1	Erreur

ACOSH

ACOSH(X)

Description

Calcule le cosinus hyperbolique inverse de X. Cette fonction génère une erreur si X est une valeur inférieure à 1.

X	ACOSH(X)
`+inf`	`+inf`
`-inf`	`NaN`
`NaN`	`NaN`
X < 1	Erreur

SIN

SIN(X)

Description

Calcule le sinus de X où X est spécifié en radians. Cette fonction n'échoue jamais.

X	SIN(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`

SINH

SINH(X)

Description

Calcule le sinus hyperbolique de X où X est spécifié en radians. Cette fonction génère une erreur en cas de débordement.

X	SINH(X)
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

ASIN

ASIN(X)

Description

Calcule la valeur principale du sinus inverse de X. La valeur renvoyée se situe dans la plage [-π/2, π/2]. Cette fonction génère une erreur si X se situe en dehors de la plage [-1, 1].

X	ASIN(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
X < -1	Erreur
X > 1	Erreur

ASINH

ASINH(X)

Description

Calcule le sinus hyperbolique inverse de X. Cette fonction n'échoue pas.

X	ASINH(X)
`+inf`	`+inf`
`-inf`	`-inf`
`NaN`	`NaN`

TAN

TAN(X)

Description

Calcule la tangente de X où X est spécifié en radians. Cette fonction génère une erreur en cas de débordement.

X	TAN(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`

TANH

TANH(X)

Description

Calcule la tangente hyperbolique de X où X est spécifié en radians. Cette fonction n'échoue pas.

X	TANH(X)
`+inf`	1.0
`-inf`	-1,0
`NaN`	`NaN`

ATAN

ATAN(X)

Description

Calcule la valeur principale de la tangente inverse de X. La valeur renvoyée se situe dans la plage [-π/2, π/2]. Cette fonction n'échoue pas.

X	ATAN(X)
`+inf`	π/2
`-inf`	-π/2
`NaN`	`NaN`

ATANH

ATANH(X)

Description

Calcule la tangente hyperbolique inverse de X. Génère une erreur si X se situe en dehors de la plage [-1, 1].

X	ATANH(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
X < -1	Erreur
X > 1	Erreur

ATAN2

ATAN2(X, Y)

Description

Calcule la valeur principale de la tangente inverse de Y/X en utilisant les signes des deux arguments pour déterminer le quadrant. La valeur renvoyée se situe dans la plage [-π,π].

X	O	ATAN2(X, Y)
`NaN`	Toute valeur	`NaN`
Toute valeur	`NaN`	`NaN`
0,0	0,0	0,0
Valeur finie positive	`-inf`	π
Valeur finie négative	`-inf`	-π
Valeur finie	`+inf`	0,0
`+inf`	Valeur finie	π/2
`-inf`	Valeur finie	-π/2
`+inf`	`-inf`	¾π
`-inf`	`-inf`	-¾π
`+inf`	`+inf`	π/4
`-inf`	`+inf`	-π/4

RANGE_BUCKET

RANGE_BUCKET(point, boundaries_array)

Description

RANGE_BUCKET analyse un tableau trié et renvoie la position en base 0 de la limite supérieure du point. Cela peut être utile si vous devez regrouper vos données pour créer des partitions, des histogrammes, des règles définies par l'entreprise, etc.

RANGE_BUCKET respecte les règles suivantes :

Si le point existe dans le tableau, cette fonction renvoie l'index de la prochaine valeur la plus élevée.

RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value

Si le point n'existe pas dans le tableau, mais qu'il se trouve entre deux valeurs, elle renvoie l'index de la valeur la plus élevée.
```
RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
```
Si le point est inférieur à la première valeur du tableau, la fonction renvoie 0.
```
RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
```
Si le point est supérieur ou égal à la dernière valeur du tableau, la fonction renvoie la longueur du tableau.
```
RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
```
Si le tableau est vide, la fonction renvoie 0.
```
RANGE_BUCKET(80, []) -- 0 is return value
```

Si le point est NULL ou NaN, elle renvoie NULL.

RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value

Le type de données pour le point et le tableau doivent être compatibles.

RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure

L'exécution échoue dans les cas suivants :

Le tableau contient une valeur NaN ou NULL.

RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure

Le tableau n'est pas trié par ordre croissant.

RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure

Paramètres

point : une valeur générique.
boundaries_array : un tableau générique de valeurs.

Valeur renvoyée

INT64

Exemples

Dans une table nommée students, vérifiez le nombre d'enregistrements présents dans chaque ensemble age_group, en fonction de l'âge des élèves :

age_group 0 (âge < 10 ans)
age_group 1 (âge >= 10 ans, âge < 20 ans)
age_group 2 (âge >= 20 ans, âge < 30 ans)
age_group 3 (âge >= 30 ans)

WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age UNION ALL
  SELECT 32 AS age UNION ALL
  SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+

Fonctions de navigation

Les sections suivantes décrivent les fonctions de navigation acceptées par BigQuery. Ces fonctions sont un sous-ensemble des fonctions analytiques. Pour plus d'informations sur le fonctionnement des fonctions analytiques, consultez la page Concepts de fonction analytique. Pour en savoir plus sur l'utilisation des fonctions de navigation, consultez la page Concepts des fonctions de navigation.

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Description

Renvoie la valeur de value_expression pour la première ligne du cadre de fenêtrage actuel.

Cette fonction inclut les valeurs NULL dans le calcul, sauf si IGNORE NULLS est présent. Si IGNORE NULLS est présent, la fonction exclut les valeurs NULL du calcul.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type de données qu'une expression peut renvoyer.

Type de données renvoyé

Type identique à value_expression.

Exemples

L'exemple suivant calcule le meilleur temps pour chaque division.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  FIRST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              |
+-----------------+-------------+----------+--------------+------------------+

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

Description

Renvoie la valeur de value_expression pour la dernière ligne du cadre de fenêtrage actuel.

Cette fonction inclut les valeurs NULL dans le calcul, sauf si IGNORE NULLS est présent. Si IGNORE NULLS est présent, la fonction exclut les valeurs NULL du calcul.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type de données qu'une expression peut renvoyer.

Type de données renvoyé

Type identique à value_expression.

Exemples

L'exemple suivant calcule le temps le moins bon pour chaque division.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
  TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  LAST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             |
| Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              |
| Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              |
| Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              |
| Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                |
| Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              |
| Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              |
| Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               |
| Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                |
+-----------------+-------------+----------+--------------+------------------+

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])

Description

Renvoie la valeur de value_expression à la nième ligne du cadre de fenêtrage actuel, où "nième" est défini par constant_integer_expression. Renvoie NULL s'il n'y a aucune ligne de ce type.

Cette fonction inclut les valeurs NULL dans le calcul, sauf si IGNORE NULLS est présent. Si IGNORE NULLS est présent, la fonction exclut les valeurs NULL du calcul.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type de données pouvant être renvoyé à partir d'une expression.
constant_integer_expression peut correspondre à n'importe quelle expression constante qui renvoie un entier.

Type de données renvoyé

Type identique à value_expression.

Exemples

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
  SELECT name,
  finish_time,
  division,finishers,
  FIRST_VALUE(finish_time)
    OVER w1 AS fastest_time,
  NTH_VALUE(finish_time, 2)
    OVER w1 as second_fastest
  FROM finishers
  WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));

+-----------------+-------------+----------+--------------+----------------+
| name            | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       |
+-----------------+-------------+----------+--------------+----------------+

LEAD

LEAD (value_expression[, offset [, default_expression]])

Description

Renvoie la valeur de value_expression sur une ligne suivante. La modification de la valeur offset change la ligne suivante qui est renvoyée. La valeur par défaut, 1, indique la première ligne suivante dans le cadre de fenêtrage. Une erreur se produit si offset est défini sur une valeur NULL ou négative.

Le paramètre default_expression facultatif est utilisé s'il n'y a pas de ligne dans le cadre de fenêtrage au décalage spécifié. Cette expression doit être constante, et son type doit pouvoir être converti implicitement (par coercition) dans le type de value_expression. Si aucune valeur n'est spécifiée, default_expression est défini par défaut sur NULL.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type de données pouvant être renvoyé à partir d'une expression.
offset doit être un paramètre ou un littéral entier non négatif.
default_expression doit être compatible avec le type de l'expression de valeur.

Type de données renvoyé

Type identique à value_expression.

Exemples

L'exemple suivant illustre une utilisation de base de la fonction LEAD.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;

+-----------------+-------------+----------+-----------------+
| name            | finish_time | division | followed_by     |
+-----------------+-------------+----------+-----------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL            |
| Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     |
| Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     |
| Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  |
| Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL            |
| Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews |
| Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   |
| Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      |
| Suzy Slane      | 03:06:24    | F35-39   | NULL            |
+-----------------+-------------+----------+-----------------+

L'exemple suivant utilise le paramètre offset facultatif.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | NULL             |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL             |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | NULL             |
| Suzy Slane      | 03:06:24    | F35-39   | NULL             |
+-----------------+-------------+----------+------------------+

L'exemple suivant remplace les valeurs NULL par une valeur par défaut.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody           |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | Nobody           |
| Lauren Reasoner | 03:10:14    | F30-34   | Nobody           |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | Nobody           |
| Suzy Slane      | 03:06:24    | F35-39   | Nobody           |
+-----------------+-------------+----------+------------------+

LAG

LAG (value_expression[, offset [, default_expression]])

Description

Renvoie la valeur de value_expression sur une ligne précédente. La modification de la valeur offset change la ligne précédente qui est renvoyée. La valeur par défaut, 1, indique la première ligne précédente dans le cadre de fenêtrage. Une erreur se produit si offset est défini sur une valeur NULL ou négative.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type de données pouvant être renvoyé à partir d'une expression.
offset doit être un paramètre ou un littéral entier non négatif.
default_expression doit être compatible avec le type de l'expression de valeur.

Type de données renvoyé

Type identique à value_expression.

Exemples

L'exemple suivant illustre une utilisation de base de la fonction LAG.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

L'exemple suivant utilise le paramètre offset facultatif.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL              |
| Sophia Liu      | 02:51:45    | F30-34   | NULL              |
| Nikki Leith     | 02:59:01    | F30-34   | NULL              |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL              |
| Lauren Matthews | 03:01:17    | F35-39   | NULL              |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

L'exemple suivant remplace les valeurs NULL par une valeur par défaut.

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody            |
| Sophia Liu      | 02:51:45    | F30-34   | Nobody            |
| Nikki Leith     | 02:59:01    | F30-34   | Nobody            |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            |
| Lauren Matthews | 03:01:17    | F35-39   | Nobody            |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Description

Calcule la valeur de centile spécifiée pour "value_expression", avec une interpolation linéaire.

Cette fonction ignore les valeurs NULL en cas d'absence de RESPECT NULLS. Si RESPECT NULLS existe :

Une interpolation entre deux valeurs NULL renvoie NULL.
Une interpolation entre une valeur NULL et une valeur non-NULL renvoie la valeur non-NULL.

Types d'arguments acceptés

value_expression et percentile doivent posséder l'un des types suivants :
- NUMERIC
- BIGNUMERIC
- FLOAT64
percentile doit être un littéral compris dans la plage [0, 1].

Type de données renvoyé

Le type de données renvoyé est déterminé par les types des arguments à l'aide du tableau suivant.

ENTRÉE	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64

Exemples

L'exemple suivant calcule la valeur de certains centiles à partir d'une colonne de valeurs, en ignorant les valeurs NULL.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

L'exemple suivant calcule la valeur de certains centiles à partir d'une colonne de valeurs, en respectant les valeurs NULL.

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+------+-------------+--------+--------------+-----+
| min  | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0           | 1      | 2.6          | 3   |
+------+-------------+--------+--------------+-----+

PERCENTILE_DISC

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])

Description

Calcule la valeur de centile spécifiée pour un paramètre discret value_expression. La valeur renvoyée est la première valeur triée de value_expression avec une distribution cumulative supérieure ou égale à la valeur percentile donnée.

Cette fonction ignore les valeurs NULL sauf si RESPECT NULLS est présent.

Types d'arguments acceptés

value_expression peut correspondre à n'importe quel type triable.
percentile doit être un littéral compris dans la plage [0, 1], avec l'un des types suivants :
- NUMERIC
- BIGNUMERIC
- FLOAT64

Type de données renvoyé

Type identique à value_expression.

Exemples

L'exemple suivant calcule la valeur de certains centiles à partir d'une colonne de valeurs, en ignorant les valeurs NULL.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+-----+--------+-----+
| x    | min | median | max |
+------+-----+--------+-----+
| c    | a   | b      | c   |
| NULL | a   | b      | c   |
| b    | a   | b      | c   |
| a    | a   | b      | c   |
+------+-----+--------+-----+

L'exemple suivant calcule la valeur de certains centiles à partir d'une colonne de valeurs, en respectant les valeurs NULL.

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+------+--------+-----+
| x    | min  | median | max |
+------+------+--------+-----+
| c    | NULL | a      | c   |
| NULL | NULL | a      | c   |
| b    | NULL | a      | c   |
| a    | NULL | a      | c   |
+------+------+--------+-----+

Fonctions analytiques d'agrégation

Les sections suivantes décrivent les fonctions analytiques d'agrégation acceptées par BigQuery. Pour plus d'informations sur le fonctionnement des fonctions analytiques, consultez la page Concepts de fonction analytique. Pour en savoir plus sur l'utilisation des fonctions analytiques d'agrégation, consultez la page Concepts des fonctions analytiques d'agrégation.

BigQuery accepte les fonctions d'agrégation suivantes comme fonctions analytiques :

Exigences de la clause OVER :

PARTITION BY : facultatif.
ORDER BY : facultatif. Non autorisé si DISTINCT est présent.
window_frame_clause : facultatif. Non autorisé si DISTINCT est présent.

Exemple :

COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)

SUM(DISTINCT x) OVER ()

Fonctions de hachage

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

Description

Calcule l'empreinte de l'entrée STRING ou BYTES à l'aide de la fonction Fingerprint64 de la bibliothèque Open Source FarmHash. Le résultat renvoyé par cette fonction pour une entrée spécifique ne change jamais.

Type renvoyé

INT64

Exemples

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

MD5

MD5(input)

Description

Calcule le hachage de l'entrée à l'aide de l'algorithme MD5. L'entrée peut être STRING ou BYTES. La version "STRING" traite l'entrée comme un tableau d'octets.

Cette fonction renvoie 16 octets.

Type renvoyé

BYTES

Exemple

SELECT MD5("Hello World") as md5;

-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5                      |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+

SHA1

SHA1(input)

Description

Calcule le hachage de l'entrée à l'aide de l'algorithme SHA-1. L'entrée peut être STRING ou BYTES. La version "STRING" traite l'entrée comme un tableau d'octets.

Cette fonction renvoie 20 octets.

Type renvoyé

BYTES

Exemple

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

Description

Calcule le hachage de l'entrée à l'aide de l'algorithme SHA-256. L'entrée peut être STRING ou BYTES. La version "STRING" traite l'entrée comme un tableau d'octets.

Cette fonction renvoie 32 octets.

Type renvoyé

BYTES

Exemple

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

Description

Calcule le hachage de l'entrée à l'aide de l'algorithme SHA-512. L'entrée peut être STRING ou BYTES. La version "STRING" traite l'entrée comme un tableau d'octets.

Cette fonction renvoie 64 octets.

Type renvoyé

BYTES

Exemple

SELECT SHA512("Hello World") as sha512;

Fonctions de chaîne

Ces fonctions de chaîne acceptent deux types de valeurs différents : les types de données STRING et BYTES. Les valeurs de type STRING doivent être des chaînes UTF-8 correctement formées.

Les fonctions qui renvoient des valeurs de position, par exemple STRPOS, encodent ces positions au format INT64. La valeur 1 fait référence au premier caractère (ou octet), la valeur 2 au deuxième, et ainsi de suite. La valeur 0 indique un index non valide. Lorsque le traitement concerne des valeurs de type STRING, les positions renvoyées font référence aux positions des caractères.

Toutes les comparaisons de chaînes sont effectuées octet par octet, sans prise en compte de l'équivalence canonique Unicode.

ASCII

ASCII(value)

Description

Renvoie le code ASCII pour le premier caractère ou octet de value. Renvoie 0 si value est vide ou si le code ASCII est 0 pour le premier caractère ou octet.

Type renvoyé

INT64

Exemples

SELECT ASCII('abcd') as A, ASCII('a') as B, ASCII('') as C, ASCII(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 97    | 97    | 0     | NULL  |
+-------+-------+-------+-------+

BYTE_LENGTH

BYTE_LENGTH(value)

Description

Renvoie la longueur de la valeur STRING ou BYTES en BYTES, qu'il s'agisse d'une valeur de type STRING ou BYTES.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

Description

Renvoie la longueur de la valeur STRING en caractères.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Description

Synonyme de CHAR_LENGTH.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHR

CHR(value)

Description

Prend un point de code Unicode et renvoie le caractère correspondant à ce point de code. Chaque point de code valide doit se situer dans les plages [0, 0xD7FF] et [0xE000, 0x10FFFF]. Renvoie une chaîne vide si le point de code est 0. Si un point de code Unicode non valide est spécifié, une erreur est renvoyée.

Pour utiliser un tableau de points de code Unicode, consultez la section CODE_POINTS_TO_STRING.

Type renvoyé

STRING

Exemples

SELECT CHR(65) AS A, CHR(255) AS B, CHR(513) AS C, CHR(1024)  AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| A     | ÿ     | ȁ     | Ѐ     |
+-------+-------+-------+-------+

SELECT CHR(97) AS A, CHR(0xF9B5) AS B, CHR(0) AS C, CHR(NULL) AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| a     | 例    |       | NULL  |
+-------+-------+-------+-------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

Description

Accepte en entrée un tableau de points de code ASCII étendu (ARRAY de INT64) et renvoie une valeur BYTES.

Pour découvrir comment convertir une valeur de type BYTES en un tableau de points de code, consultez la section TO_CODE_POINTS.

Type renvoyé

BYTES

Exemples

Voici un exemple de base utilisant CODE_POINTS_TO_BYTES.

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

L'exemple suivant utilise un algorithme ROT13 (décalage de 13 caractères pour chaque lettre) pour encoder une chaîne.

SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' and b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' and b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
  ) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Description

Accepte en entrée un tableau de points de code Unicode (ARRAY de INT64) et renvoie une valeur STRING. Si la valeur d'un point de code est 0, il ne renvoie pas de caractère pour celui-ci dans STRING.

Pour découvrir comment convertir une valeur de type STRING en un tableau de points de code, consultez la section TO_CODE_POINTS.

Type renvoyé

STRING

Exemples

Voici des exemples de base qui utilisent CODE_POINTS_TO_STRING.

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+

SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

L'exemple suivant calcule la fréquence des lettres dans un ensemble de mots.

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

Description

Concatène une ou plusieurs valeurs en un seul résultat. Toutes les valeurs doivent être de type BYTES ou des types de données pouvant être convertis en STRING.

La fonction renvoie NULL si l'un des arguments d'entrée est NULL.

Type renvoyé

STRING ou BYTES

Exemples

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

SELECT CONCAT("Summer", " ", 1923) as release_date;

+---------------------+
| release_date        |
+---------------------+
| Summer 1923         |
+---------------------+


With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

Description

Prend deux valeurs STRING ou BYTES. Renvoie TRUE si la deuxième valeur est un suffixe de la première.

Type renvoyé

BOOL

Exemples

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

BigQuery accepte une fonction FORMAT() pour la mise en forme des chaînes. Cette fonction est semblable à la fonction C printf. Elle génère une STRING à partir d'une chaîne de format contenant zéro, un ou plusieurs spécificateurs de format, ainsi qu'une liste (de longueur variable) d'arguments supplémentaires correspondant aux spécificateurs de format. Voici quelques exemples :

Description	Instruction	Résultat
Entier simple	FORMAT("%d", 10)	10
Entier avec espaces de remplissage à gauche	FORMAT("\|%10d\|", 11)	\| 11\|
Entier avec zéros de remplissage à gauche	FORMAT("+%010d+", 12)	+0000000012+
Entier avec virgules	FORMAT("%'d", 123456789)	123,456,789
STRING	FORMAT("-%s-", 'abcd efg')	-abcd efg-
FLOAT64	FORMAT("%f %E", 1.1, 2.2)	1.100000 2.200000E+00
DATE	FORMAT("%t", date "2015-09-01")	2015-09-01
TIMESTAMP	FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles")	2015‑09‑01 19:34:56+00

La fonction FORMAT() ne fournit pas une mise en forme entièrement personnalisable pour tous les types et valeurs, ni une mise en forme prenant en compte les paramètres régionaux.

Si une mise en forme personnalisée est nécessaire pour un type, vous devez d'abord formater celui-ci à l'aide de fonctions spécifiques au type, telles que FORMAT_DATE() ou FORMAT_TIMESTAMP(). Par exemple :

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

Renvoie :

date: January 02, 2015!

Syntaxe

La syntaxe de FORMAT() accepte en entrée une chaîne de format et une liste d'arguments de longueur variable, puis produit un résultat de type STRING :

FORMAT(format_string, ...)

L'expression format_string peut contenir zéro, un ou plusieurs spécificateurs de format. Chaque spécificateur de format est introduit par le symbole % et doit être mappé sur un ou plusieurs des arguments restants. Dans la plupart des cas, il s'agit d'un mappage de type "un à un", sauf lorsque le spécificateur * est présent. Par exemple, %.*i correspond à deux arguments : un argument de longueur et un argument de nombre entier signé. Si le nombre d'arguments liés aux spécificateurs de format n'est pas identique au nombre total d'arguments, une erreur se produit.

Spécificateurs de format acceptés

Le spécificateur de format de la fonction FORMAT() suit ce prototype :

%[flags][width][.precision]specifier

Les spécificateurs de format acceptés sont identifiés dans le tableau ci-après. Les écarts par rapport à printf() sont indiqués en italique.

Spécificateur	Description	Exemples	Types
`d` ou `i`	Entier décimal	392	INT64
`o`	Octal	610	INT64*
`x`	Entier hexadécimal	7fa	INT64*
`X`	Entier hexadécimal (majuscule)	7FA	INT64*
`f`	Notation décimale [-](partie entière).(partie fractionnaire) pour les valeurs finies. Minuscule pour les valeurs non finies.	392.650000 inf nan	NUMERIC BIGNUMERIC FLOAT64
`F`	Notation décimale [-](partie entière).(partie fractionnaire) pour les valeurs finies. Majuscule pour les valeurs non finies.	392.650000 INF NAN	NUMERIC BIGNUMERIC FLOAT64
`e`	Notation scientifique (mantisse/exposant), minuscule	3.926500e+02 inf nan	NUMERIC BIGNUMERIC FLOAT64
`E`	Notation scientifique (mantisse/exposant), majuscule	3.926500E+02 INF NAN	NUMERIC BIGNUMERIC FLOAT64
`g`	Notation décimale ou scientifique, en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée. Minuscule. Consultez la section Comportement de %g et %G pour plus de détails.	392.65 3.9265e+07 inf nan	NUMERIC BIGNUMERIC FLOAT64
`G`	Notation décimale ou scientifique, en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée. Majuscule. Consultez la section Comportement de %g et %G pour plus de détails.	392,65 3,9265E+07 INF NAN	NUMERIC BIGNUMERIC FLOAT64
`s`	Chaîne de caractères	exemple	STRING
`t`	Renvoie une chaîne imprimable représentant la valeur. Ce résultat est souvent semblable à celui du casting de l'argument au format `STRING`. Consultez la section Comportement de %t et de %T pour plus de détails.	sample 2014‑01‑01	<tous>
`T`	Génère une chaîne qui est une constante BigQuery valide, dont le type est semblable à celui de la valeur. Il peut contenir plus de caractères ou se présenter sous forme de chaîne. Consultez la section Comportement de %t et de %T pour plus de détails.	'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01'	<tous>
`%`	'%%' produit un seul '%'	%	n/a

^* Les spécificateurs %o, %x et %X génèrent une erreur si des valeurs négatives sont utilisées.

Le spécificateur de format peut éventuellement contenir les sous-spécificateurs identifiés ci-dessus dans le prototype de spécificateur.

Ces sous-spécificateurs doivent être conformes aux spécifications suivantes.

Options

Options	Description
`-`	Justifie à gauche dans la largeur de champ donnée ; la justification à droite est la valeur par défaut (consultez la rubrique relative aux sous-spécificateurs de largeur).
`+`	Force l'ajout d'un préfixe "plus" ou "moins" (signe `+` ou `-`) avant le résultat, même pour les nombres positifs. Par défaut, seuls les nombres négatifs sont précédés d'un signe `-`.
<espace>	Si aucun signe ne doit être écrit par la suite, un espace est inséré avant la valeur
`#`	Pour "%o", "%x" et "%X", cette option ajoute respectivement le préfixe 0, 0x ou 0X avant la valeur lorsqu'elle est différente de zéro. Pour "%f", "%F", "%e" et "%E", cette option ajoute le point décimal même s'il n'y a pas de partie fractionnaire (sauf dans le cas d'une valeur non finie). Pour "%g" et "%G", cette option ajoute le point décimal même s'il n'y a pas de partie fractionnaire (sauf dans le cas d'une valeur non finie), et ne supprime jamais les zéros de fin après le point décimal.
`0`	Ajoute des zéros (0) au lieu d'espaces à gauche du nombre lorsque l'option de remplissage est spécifiée (consultez la rubrique relative aux sous-spécificateurs de largeur).
`'`	Formate les entiers en utilisant le caractère de regroupement approprié. Par exemple : `FORMAT("%'d", 12345678)` renvoie `12,345,678` `FORMAT("%'x", 12345678)` renvoie `bc:614e` `FORMAT("%'o", 55555)` renvoie `15,4403` Cet indicateur ne concerne que les valeurs décimales, hexadécimales et octales.

Les indicateurs peuvent être spécifiés dans n'importe quel ordre. Les options en double ne sont pas considérées comme des erreurs. Les options non pertinentes pour un type d'élément sont ignorées.

Largeur

Largeur	Description
<nombre>	Nombre minimum de caractères à imprimer. Si la valeur à imprimer ne contient pas le nombre minimum de caractères requis, le résultat est complété par des espaces. La valeur n'est pas tronquée, même si le résultat contient plus de caractères.
`*`	La largeur n'est pas spécifiée dans la chaîne de format, mais en tant qu'argument de valeur entière supplémentaire précédant l'argument à formater.

Précision

Précision	Description
`.`<nombre>	Pour les spécificateurs d'entiers "%d", "%i", "%o", "%u", "%x" et "%X", la précision indique le nombre minimal de chiffres à écrire. Si la valeur à écrire comporte moins de chiffres, des zéros sont ajoutés à la fin du résultat. La valeur n'est pas tronquée, même si le résultat contient plus de chiffres. Avec une précision de 0, aucun caractère n'est écrit pour la valeur 0. Pour les spécificateurs "%a", "%A", "%e", "%E", "%f" et "%F", la précision indique le nombre de chiffres à écrire après le point décimal. La valeur par défaut est 6. Pour les spécificateurs "%g" et "%G", la précision indique le nombre de chiffres significatifs à écrire, avant que les zéros de fin après le point décimal ne soient supprimés. La valeur par défaut est 6.
`.*`	La précision n'est pas spécifiée dans la chaîne de format, mais en tant qu'argument de valeur entière supplémentaire précédant l'argument à formater.

Comportement de %g et %G

Les spécificateurs de format %g et %G adoptent la notation décimale (comme les spécificateurs %f et %F) ou la notation scientifique (comme les spécificateurs %e et %E), en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée.

Soit p la précision spécifiée (6 par défaut ; 1 si la précision spécifiée est inférieure à 1). La valeur d'entrée est d'abord convertie en notation scientifique avec la précision (p - 1). Si l'exposant x obtenu est inférieur à -4 ou supérieur ou égal à p, c'est la notation scientifique avec la précision (p - 1) qui est retenue. Dans les autres cas, c'est la notation décimale avec la précision (p - 1 - x) qui est utilisée.

À moins que l'option # ne soit spécifiée, les zéros de fin après le point décimal sont supprimés, et le point décimal est également supprimé si aucun chiffre ne figure après.

Comportement de %t et %T

Les spécificateurs de format %t et %T sont définis pour tous les types. La largeur, la précision et les options se comportent comme pour le spécificateur %s : la largeur correspond à la taille minimale, et la chaîne STRING est complétée pour atteindre cette taille ; la précision est la taille maximale du contenu à afficher, et la chaîne STRING est tronquée pour lui donner cette taille avant l'application du remplissage nécessaire pour atteindre la largeur spécifiée.

Le spécificateur %t est toujours destiné à être une forme lisible de la valeur.

Le spécificateur %T est toujours un littéral SQL valide d'un type similaire, tel qu'un type numérique plus large. Ce littéral n'inclura pas de castings ni de nom de type, sauf dans le cas particulier des valeurs à virgule flottante non finies.

La STRING est formatée comme suit :

Type	%t	%T
`NULL` de n'importe quel type	NULL	NULL
INT64	123	123
NUMERIC	123.0 (toujours avec .0)	NUMERIC "123.0"
FLOAT64	123.0 (toujours avec .0) 123e+10 `inf` `-inf` `NaN`	123.0 (toujours avec .0) 123e+10 CAST("inf" en tant que <type>) CAST("-inf" en tant que <type>) CAST("nan" en tant que <type>)
STRING	Valeur de chaîne sans guillemets	Littéral de chaîne entre guillemets
BYTES	Octets échappés sans guillemets par exemple, abc\x01\x02	Littéral d'octets entre guillemets par exemple, b"abc\x01\x02"
DATE	2011-02-03	DATE "2011-02-03"
TIMESTAMP	2011-02-03 04:05:06+00	TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY	[valeur, valeur, …] où les valeurs sont formatées avec %t	[valeur, valeur, …] où les valeurs sont formatées avec %T
STRUCT	(valeur, valeur, …) où les champs sont formatés avec %t	(valeur, valeur, …) où les champs sont formatés avec %T Cas particuliers : Aucun champ : STRUCT() Un champ : STRUCT(valeur)

Conditions d'erreur

Si un spécificateur de format n'est pas valide, s'il n'est pas compatible avec le type d'argument associé, ou encore si un nombre erroné ou des arguments incorrects sont fournis, une erreur est générée. Par exemple, les expressions <format_string> suivantes ne sont pas valides :

FORMAT('%s', 1)

FORMAT('%')

Gérer des arguments NULL

Une chaîne de format NULL donne une NULL de sortie STRING. Tous les autres arguments sont ignorés dans ce cas.

La fonction produit généralement une valeur NULL si un argument NULL est présent. Par exemple, FORMAT('%i', NULL_expression) génère une NULL STRING en sortie.

Il existe toutefois des exceptions : si le spécificateur de format est %t ou %T (tous deux produisent des STRING qui correspondent effectivement aux sémantiques de la valeur littérale et de CAST), une valeur NULL produit un résultat "NULL" (sans les guillemets) dans la STRING résultante. Par exemple, la fonction :

FORMAT('00-%t-00', NULL_expression);

Renvoie

00-NULL-00

Règles sémantiques supplémentaires

Les valeurs FLOAT64 peuvent être +/-inf ou NaN. Lorsqu'un argument comporte l'une de ces valeurs, le résultat des spécificateurs de format %f, %F, %e, %E, %g, %G et %t est inf, -inf ou nan (ou le même terme en majuscules) selon le cas. Cette règle est cohérente avec la manière dont BigQuery procède au casting de ces valeurs au format STRING. Pour %T, BigQuery renvoie des chaînes entre guillemets pour les valeurs FLOAT64 dont les représentations littérales ne peuvent être que des chaînes.

FROM_BASE32

FROM_BASE32(string_expr)

Description

Convertit l'entrée string_expr encodée en base32 au format BYTES. Pour convertir une valeur de type BYTES en valeur de type STRING encodée en base32, utilisez TO_BASE32.

Type renvoyé

BYTES

Exemple

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

Description

Convertit l'entrée string_expr encodée en base64 au format BYTES. Pour convertir une valeur de type BYTES en valeur de type STRING encodée en base64, utilisez TO_BASE64.

Il existe plusieurs encodages en base64 d'usage courant qui varient selon l'alphabet de 65 caractères ASCII utilisé pour encoder les 64 chiffres et le remplissage. Pour en savoir plus, consultez la norme RFC 4648. Cette fonction attend l'alphabet [A-Za-z0-9+/=].

Type renvoyé

BYTES

Exemple

SELECT FROM_BASE64('/+A=') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| /+A=      |
+-----------+

Pour travailler avec un encodage utilisant un autre alphabet en base64, vous devrez peut-être composer FROM_BASE64 avec la fonction REPLACE. Par exemple, l'encodage base64url sécurisé pour les URL et le nom de fichier couramment utilisé dans la programmation Web utilise -_= comme derniers caractères plutôt que +/=. Pour décoder une chaîne encodée en base64url, remplacez + et / par - et _, respectivement.

SELECT FROM_BASE64(REPLACE(REPLACE("_-A=", "-", "+"), "_", "/")) AS binary;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A=   |
+--------+

FROM_HEX

FROM_HEX(string)

Description

Convertit une STRING encodée en hexadécimal au format BYTES. Renvoie une erreur si la STRING d'entrée contient des caractères non compris dans la plage (0..9, A..F, a..f). La casse des lettres (majuscule/minuscule) n'a pas d'importance. Si la STRING d'entrée contient un nombre impair de caractères, la fonction agit comme si l'entrée commençait par un 0 supplémentaire. Pour convertir une valeur BYTES en STRING encodée en hexadécimal, utilisez TO_HEX.

Type renvoyé

BYTES

Exemple

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

INITCAP

INITCAP(value[, delimiters])

Description

Prend une valeur STRING et la renvoie avec le premier caractère de chaque mot en majuscules et tous les autres caractères en minuscules. Les caractères non alphabétiques ne changent pas.

delimiters est un argument de chaîne facultatif permettant de remplacer l'ensemble de caractères par défaut utilisé pour séparer les mots. Si delimiters n'est pas spécifié, les caractères suivants sont utilisés par défaut :
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -

Si value ou delimiters est NULL, la fonction renvoie NULL.

Type renvoyé

STRING

Exemples

WITH example AS
(
  SELECT "Hello World-everyone!" AS value UNION ALL
  SELECT "tHe dog BARKS loudly+friendly" AS value UNION ALL
  SELECT "apples&oranges;&pears" AS value UNION ALL
  SELECT "καθίσματα ταινιών" AS value
)
SELECT value, INITCAP(value) AS initcap_value FROM example

+-------------------------------+-------------------------------+
| value                         | initcap_value                 |
+-------------------------------+-------------------------------+
| Hello World-everyone!         | Hello World-Everyone!         |
| tHe dog BARKS loudly+friendly | The Dog Barks Loudly+Friendly |
| apples&oranges;&pears         | Apples&Oranges;&Pears         |
| καθίσματα ταινιών             | Καθίσματα Ταινιών             |
+-------------------------------+-------------------------------+

WITH example AS
(
  SELECT "hello WORLD!" AS value, "" AS delimiters UNION ALL
  SELECT "καθίσματα ταιντιώ@ν" AS value, "τ@" AS delimiters UNION ALL
  SELECT "Apples1oranges2pears" AS value, "12" AS delimiters UNION ALL
  SELECT "tHisEisEaESentence" AS value, "E" AS delimiters
)
SELECT value, delimiters, INITCAP(value, delimiters) AS initcap_value FROM example;

+----------------------+------------+----------------------+
| value                | delimiters | initcap_value        |
+----------------------+------------+----------------------+
| hello WORLD!         |            | Hello world!         |
| καθίσματα ταιντιώ@ν  | τ@         | ΚαθίσματΑ τΑιντΙώ@Ν  |
| Apples1oranges2pears | 12         | Apples1Oranges2Pears |
| tHisEisEaESentence   | E          | ThisEIsEAESentence   |
+----------------------+------------+----------------------+

INSTR

INSTR(source_value, search_value[, position[, occurrence]])

Description

Renvoie l'index en base 1 le plus faible de search_value dans source_value. La valeur 0 est renvoyée lorsqu'aucune correspondance n'est trouvée. source_value et search_value doivent être du même type (STRING ou BYTES).

Si position est spécifié, la recherche commence à cette position dans source_value. Sinon, elle commence au début de source_value. Si la valeur de position est négative, la fonction effectue une recherche vers l'arrière en partant de la fin de source_value, et "-1" indique le dernier caractère. La valeur de position ne peut pas être égale à 0.

Si le paramètre occurrence est spécifié, la recherche renvoie la position d'une instance spécifique de search_value dans source_value. Sinon, elle renvoie l'index de la première occurrence. Si la valeur de occurrence est supérieure au nombre de correspondances trouvées, la valeur 0 est renvoyée. Pour occurrence > 1, la fonction recherche des occurrences se chevauchant. En d'autres termes, la fonction recherche des occurrences supplémentaires commençant par le deuxième caractère de l'occurrence précédente. La valeur de occurrence ne peut pas être égale à 0 ni négative.

Type renvoyé

INT64

Exemples

WITH example AS
(SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 2 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 3 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'ann' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 2 as
occurrence
)
SELECT source_value, search_value, position, occurrence, INSTR(source_value,
search_value, position, occurrence) AS instr
FROM example;

+--------------+--------------+----------+------------+-------+
| source_value | search_value | position | occurrence | instr |
+--------------+--------------+----------+------------+-------+
| banana       | an           | 1        | 1          | 2     |
| banana       | an           | 1        | 2          | 4     |
| banana       | an           | 1        | 3          | 0     |
| banana       | an           | 3        | 1          | 4     |
| banana       | an           | -1       | 1          | 4     |
| banana       | an           | -3       | 1          | 4     |
| banana       | ann          | 1        | 1          | 0     |
| helloooo     | oo           | 1        | 1          | 5     |
| helloooo     | oo           | 1        | 2          | 6     |
+--------------+--------------+----------+------------+-------+

LEFT

LEFT(value, length)

Description

Renvoie une valeur STRING ou BYTES constituée du nombre de caractères ou d'octets les plus à gauche de value. length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si value est de type BYTES, length correspond au nombre d'octets les plus à gauche à renvoyer. Si value est de type STRING, length correspond au nombre de caractères les plus à gauche à renvoyer.

Si length est égale à 0, une valeur STRING ou BYTES vide est renvoyée. Si la valeur length est négative, une erreur est renvoyée. Si length dépasse le nombre de caractères ou d'octets de value, la valeur value d'origine est renvoyée.

Type renvoyé

STRING ou BYTES

Exemples

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

+---------+--------------+
| example | left_example |
+---------+--------------+
| apple   | app          |
| banana  | ban          |
| абвгд   | абв          |
+---------+--------------+

WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
+----------+--------------+
| example  | left_example |
+----------+--------------+
| YXBwbGU= | YXBw         |
| YmFuYW5h | YmFu         |
| q83vqrs= | q83v         |
+----------+--------------+

LENGTH

LENGTH(value)

Description

Renvoie la longueur de la valeur STRING ou BYTES. La valeur renvoyée correspond au nombre de caractères pour les arguments STRING et au nombre d'octets pour les arguments BYTES.

Type renvoyé

INT64

Exemples


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

Description

Renvoie une valeur STRING ou BYTES composée de l'élément original_value et du préfixe pattern. return_length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si original_value est de type BYTES, return_length correspond au nombre d'octets. Si original_value est de type STRING, return_length correspond au nombre de caractères.

La valeur par défaut de pattern est une espace.

original_value et pattern doivent correspondre au même type de données.

Si return_length est inférieur ou égal à la longueur original_value, cette fonction renvoie la valeur original_value tronquée à la valeur de return_length. Par exemple, LPAD("hello world", 7); renvoie "hello w".

Si original_value, return_length ou pattern est NULL, cette fonction renvoie NULL.

Cette fonction renvoie une erreur si :

return_length est une valeur négative ;
pattern est vide.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | LPAD     |
|------|-----|----------|
| abc  | 5   | "  abc"  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "  例子" |
+------+-----+----------+

SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | LPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "defdeabc"   |
| abc  | 5   | -       | "--abc"      |
| 例子  | 5   | 中文    | "中文中例子"   |
+------+-----+---------+--------------+

SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | LPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"  abc"         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b" \xab\xcd\xef" |
+-----------------+-----+------------------+

SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | LPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"defdeabc"             |
| b"abc"          | 5   | b"-"    | b"--abc"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+

LOWER

LOWER(value)

Description

Pour les arguments STRING, cette fonction renvoie la chaîne d'origine avec tous les caractères alphabétiques en minuscules. Le mappage entre minuscules et majuscules est effectué en fonction de la base de données de caractères Unicode, sans prise en compte des mappages spécifiques à la langue.

Les arguments BYTES sont quant à eux traités comme du texte ASCII, et les octets supérieurs à 127 ne sont pas modifiés.

Type renvoyé

STRING ou BYTES

Exemples


WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

Description

Identique à TRIM, mais ne supprime que les caractères de début.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

NORMALIZE

NORMALIZE(value[, normalization_mode])

Description

Accepte en entrée une valeur de type valeur de chaîne et la renvoie sous forme de chaîne normalisée.

La normalisation permet de garantir que deux chaînes sont équivalentes. Elle est souvent utilisée dans le cas où deux chaînes génèrent le même résultat à l'écran, mais ont des points de code Unicode différents.

NORMALIZE accepte quatre modes de normalisation facultatifs.

Valeur	Nom	Description
NFC	Forme de normalisation avec composition canonique	Décompose et recompose les caractères par équivalence canonique.
NFKC	Forme de normalisation avec composition de compatibilité	Décompose les caractères par compatibilité, puis les recompose par équivalence canonique.
NFD	Forme de normalisation avec décomposition canonique	Décompose les caractères par équivalence canonique, puis plusieurs caractères de combinaison sont disposés dans un ordre spécifique.
NFKD	Forme de normalisation avec décomposition de compatibilité	Décompose les caractères par compatibilité, puis plusieurs caractères de combinaison sont disposés dans un ordre spécifique.

Le mode de normalisation par défaut est NFC.

Type renvoyé

STRING

Exemples

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true       |
+---+---+------------+

L'exemple ci-dessous permet de normaliser différents caractères d'espacement.

WITH EquivalentNames AS (
  SELECT name
  FROM UNNEST([
      'Jane\u2004Doe',
      'John\u2004Smith',
      'Jane\u2005Doe',
      'Jane\u2006Doe',
      'John Smith']) AS name
)
SELECT
  NORMALIZE(name, NFKC) AS normalized_name,
  COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;

+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith      | 2          |
| Jane Doe        | 3          |
+-----------------+------------+

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

Description

Accepte en entrée une valeur de type STRING, value, et effectue les mêmes actions que NORMALIZE, ainsi que le pli de casse pour les opérations non sensibles à la casse.

NORMALIZE_AND_CASEFOLD accepte quatre modes de normalisation facultatifs.

Valeur	Nom	Description
NFC	Forme de normalisation avec composition canonique	Décompose et recompose les caractères par équivalence canonique.
NFKC	Forme de normalisation avec composition de compatibilité	Décompose les caractères par compatibilité, puis les recompose par équivalence canonique.
NFD	Forme de normalisation avec décomposition canonique	Décompose les caractères par équivalence canonique, puis plusieurs caractères de combinaison sont disposés dans un ordre spécifique.
NFKD	Forme de normalisation avec décomposition de compatibilité	Décompose les caractères par compatibilité, puis plusieurs caractères de combinaison sont disposés dans un ordre spécifique.

Le mode de normalisation par défaut est NFC.

Type renvoyé

STRING

Exemple

WITH Strings AS (
  SELECT '\u2168' AS a, 'IX' AS b UNION ALL
  SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
  NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;

+---+----+-------+-------+------+------+
| a | b  | nfd   | nfc   | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å  | true  | true  | true | true |
+---+----+-------+-------+------+------+

OCTET_LENGTH

OCTET_LENGTH(value)

Alias de BYTE_LENGTH.

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

Description

Renvoie TRUE si value est une correspondance partielle de l'expression régulière (regexp).

Si l'argument regexp n'est pas valide, la fonction renvoie une erreur.

Vous pouvez rechercher une correspondance complète en utilisant ^ (début du texte) et $ (fin du texte). En raison de la priorité des opérateurs d'expression régulière, il est recommandé d'utiliser des parenthèses pour tout élément compris entre ^ et $.

Type renvoyé

BOOL

Exemples

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp[, position[, occurrence]])

Description

Renvoie la sous-chaîne de value qui correspond à l'expression régulière (regexp). Renvoie NULL s'il n'y a pas de correspondance.

Si l'expression régulière comporte un groupe de capture, la fonction renvoie uniquement la sous-chaîne à laquelle correspond ce groupe de capture. Si l'expression ne contient pas de groupe de capture, la fonction renvoie l'intégralité de la sous-chaîne correspondante.

Si position est spécifié, la recherche commence à cette position dans value. Sinon, elle commence au début de value. position doit être un entier positif et ne peut pas être égale à 0. Si la valeur de position est supérieure à la longueur de value, NULL est renvoyé.

Si occurrence est spécifié, la recherche renvoie une occurrence spécifique de regexp dans value. Sinon, elle renvoie la première correspondance. Si la valeur de occurrence est supérieure au nombre de correspondances trouvées, NULL est renvoyé. Pour occurrence > 1, la fonction recherche des occurrences supplémentaires commençant par le caractère qui suit l'occurrence précédente.

Renvoie une erreur si :

l'expression régulière n'est pas valide ;
l'expression régulière comporte plus d'un groupe de capture ;
position n'est pas un entier positif ;
occurrence n'est pas un entier positif.

Type renvoyé

STRING ou BYTES

Exemples

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

WITH example AS
(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position,
1 AS occurrence UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL
SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL
SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3
)
SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------------+---------+----------+------------+--------------+
| value                    | regex   | position | occurrence | regexp_value |
+--------------------------+---------+----------+------------+--------------+
| Hello Helloo and Hellooo | H?ello+ | 1        | 1          | Hello        |
| Hello Helloo and Hellooo | H?ello+ | 1        | 2          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 1        | 3          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 1        | 4          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 2        | 1          | ello         |
| Hello Helloo and Hellooo | H?ello+ | 3        | 1          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 3        | 2          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 3        | 3          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 20       | 1          | NULL         |
| cats&dogs&rabbits        | \w+&    | 1        | 2          | dogs&        |
| cats&dogs&rabbits        | \w+&    | 2        | 3          | NULL         |
+--------------------------+---------+----------+------------+--------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

Description

Renvoie un tableau contenant toutes les sous-chaînes de value qui correspondent à l'expression régulière (regexp).

La fonction REGEXP_EXTRACT_ALL ne renvoie que des correspondances qui ne se chevauchent pas. Par exemple, si vous utilisez cette fonction pour extraire ana de banana, la fonction ne renvoie qu'une sous-chaîne, pas deux.

Type renvoyé

ARRAY de type STRING ou BYTES

Exemples

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_INSTR

REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]])

Description

Renvoie l'index en base 1 le plus faible d'une expression régulière (regexp) dans source_value. Renvoie 0 lorsqu'aucune correspondance n'est trouvée ou que l'expression régulière est vide. Renvoie une erreur si l'expression régulière n'est pas valide ou comporte plus d'un groupe de capture. source_value et regexp doivent être du même type (STRING ou BYTES).

Si le paramètre occurrence est spécifié, la recherche renvoie la position d'une instance spécifique de regexp dans source_value. Sinon, elle renvoie l'index de la première occurrence. Si la valeur de occurrence est supérieure au nombre de correspondances trouvées, la valeur 0 est renvoyée. Pour occurrence > 1, la fonction recherche des occurrences se chevauchant. En d'autres termes, la fonction recherche des occurrences supplémentaires commençant par le deuxième caractère de l'occurrence précédente. La valeur de occurrence ne peut pas être égale à 0 ni négative.

Vous pouvez également utiliser occurrence_position pour spécifier où démarre une position en relation avec une occurrence. Vous disposez des options suivantes : + 0 : renvoie la position de départ de l'occurrence. + 1 : renvoie la première position suivant la fin de l'occurrence. Si la fin de l'occurrence est également la fin de l'entrée, seule la fin de l'occurrence est renvoyée. Par exemple, la longueur d'une chaîne + 1.

Type renvoyé

INT64

Exemples

WITH example AS (
  SELECT 'ab@gmail.com' AS source_value, '@[^.]*' AS regexp UNION ALL
  SELECT 'ab@mail.com', '@[^.]*' UNION ALL
  SELECT 'abc@gmail.com', '@[^.]*' UNION ALL
  SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;

+---------------+--------+-------+
| source_value  | regexp | instr |
+---------------+--------+-------+
| ab@gmail.com  | @[^.]* | 3     |
| ab@mail.com   | @[^.]* | 3     |
| abc@gmail.com | @[^.]* | 4     |
| abc.com       | @[^.]* | 0     |
+---------------+--------+-------+

WITH example AS (
  SELECT 'a@gmail.com b@gmail.com' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 3 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 4)
SELECT
  source_value, regexp, position,
  REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;

+-------------------------+--------+----------+-------+
| source_value            | regexp | position | instr |
+-------------------------+--------+----------+-------+
| a@gmail.com b@gmail.com | @[^.]* | 1        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 2        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 3        | 14    |
| a@gmail.com b@gmail.com | @[^.]* | 4        | 14    |
+-------------------------+--------+----------+-------+

WITH example AS (
  SELECT 'a@gmail.com b@gmail.com c@gmail.com' AS source_value,
         '@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 3)
SELECT
  source_value, regexp, position, occurrence,
  REGEXP_INSTR(source_value, regexp, position, occurrence) AS instr
FROM example;

+-------------------------------------+--------+----------+------------+-------+
| source_value                        | regexp | position | occurrence | instr |
+-------------------------------------+--------+----------+------------+-------+
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 1          | 2     |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 2          | 14    |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 3          | 26    |
+-------------------------------------+--------+----------+------------+-------+

WITH example AS (
  SELECT 'a@gmail.com' AS source_value, '@[^.]*' AS regexp,
         1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
  SELECT 'a@gmail.com', '@[^.]*', 1, 1, 1)
SELECT
  source_value, regexp, position, occurrence, o_position,
  REGEXP_INSTR(source_value, regexp, position, occurrence, o_position) AS instr
FROM example;

+--------------+--------+----------+------------+------------+-------+
| source_value | regexp | position | occurrence | o_position | instr |
+--------------+--------+----------+------------+------------+-------+
| a@gmail.com  | @[^.]* | 1        | 1          | 0          | 2     |
| a@gmail.com  | @[^.]* | 1        | 1          | 1          | 8     |
+--------------+--------+----------+------------+------------+-------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

Description

Renvoie une STRING dans laquelle toutes les sous-chaînes de value qui correspondent à l'expression régulière regexp sont remplacées par l'argument replacement.

Vous pouvez utiliser des chiffres échappés par une barre oblique inverse (\1 à \9) dans l'argument replacement pour insérer du texte associé au groupe entre parenthèses correspondant dans le schéma regexp. Utilisez \0 pour faire référence à l'ensemble du texte associé.

La fonction REGEXP_REPLACE ne remplace que les correspondances qui ne se chevauchent pas. Par exemple, le fait de remplacer ana dans banana n'entraîne qu'un remplacement, pas deux.

Si l'argument regexp n'est pas une expression régulière valide, cette fonction renvoie une erreur.

Type renvoyé

STRING ou BYTES

Exemples

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REGEXP_SUBSTR

REGEXP_SUBSTR(value, regexp[, position[, occurrence]])

Description

Synonyme de REGEXP_EXTRACT.

Type renvoyé

STRING ou BYTES

Exemples

WITH example AS
(SELECT 'Hello World Helloo' AS value, 'H?ello+' AS regex, 1 AS position, 1 AS
occurrence
)
SELECT value, regex, position, occurrence, REGEXP_SUBSTR(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------+---------+----------+------------+--------------+
| value              | regex   | position | occurrence | regexp_value |
+--------------------+---------+----------+------------+--------------+
| Hello World Helloo | H?ello+ | 1        | 1          | Hello        |
+--------------------+---------+----------+------------+--------------+

REPLACE

REPLACE(original_value, from_value, to_value)

Description

Remplace toutes les occurrences de from_value par to_value dans original_value. Si from_value est vide, aucun remplacement n'est effectué.

Type renvoyé

STRING ou BYTES

Exemples

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

Description

Renvoie une valeur STRING ou BYTES constituée de l'élément original_value, ainsi que d'une ou de plusieurs répétitions de celui-ci. Le paramètre repetitions indique le nombre de répétitions de la valeur original_value. Renvoie NULL si original_value ou repetitions est NULL.

Cette fonction renvoie une erreur si la valeur repetitions est négative.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);

+------+------+-----------+
| t    | n    | REPEAT    |
|------|------|-----------|
| abc  | 3    | abcabcabc |
| 例子 | 2    | 例子例子  |
| abc  | NULL | NULL      |
| NULL | 3    | NULL      |
+------+------+-----------+

REVERSE

REVERSE(value)

Description

Renvoie l'inverse de la valeur STRING ou BYTES d'entrée.

Type renvoyé

STRING ou BYTES

Exemples

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RIGHT

RIGHT(value, length)

Description

Renvoie une valeur STRING ou BYTES constituée du nombre de caractères ou d'octets les plus à droite de value. length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si value est de type BYTES, length correspond au nombre d'octets les plus à droite à renvoyer. Si value est de type STRING, length correspond au nombre de caractères les plus à droite à renvoyer.

Type renvoyé

STRING ou BYTES

Exemples

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

+---------+---------------+
| example | right_example |
+---------+---------------+
| apple   | ple           |
| banana  | ana           |
| абвгд   | вгд           |
+---------+---------------+

WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
+----------+---------------+
| example  | right_example |
+----------+---------------+
| YXBwbGU= | cGxl          |
| YmFuYW5h | YW5h          |
| q83vqrs= | 76q7          |
+----------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

Description

Renvoie une valeur STRING ou BYTES constituée de l'élément original_value et du suffixe pattern. Le paramètre return_length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si original_value est BYTES, return_length correspond au nombre d'octets. Si original_value est STRING, return_length correspond au nombre de caractères.

La valeur par défaut de pattern est une espace.

original_value et pattern doivent correspondre au même type de données.

Si original_value, return_length ou pattern est NULL, cette fonction renvoie NULL.

Cette fonction renvoie une erreur si :

return_length est une valeur négative ;
pattern est vide.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | RPAD     |
|------|-----|----------|
| abc  | 5   | "abc  "  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "例子  " |
+------+-----+----------+

SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | RPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "abcdefde"   |
| abc  | 5   | -       | "abc--"      |
| 例子  | 5   | 中文     | "例子中文中"  |
+------+-----+---------+--------------+

SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | RPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"abc  "         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b"\xab\xcd\xef " |
+-----------------+-----+------------------+

SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | RPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"abcdefde"             |
| b"abc"          | 5   | b"-"    | b"abc--"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+

RTRIM

RTRIM(value1[, value2])

Description

Identique à TRIM, mais ne supprime que les caractères de fin.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+

WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Description

Convertit une séquence de BYTES en STRING. Tous les caractères UTF-8 non valides sont remplacés par le caractère de remplacement Unicode U+FFFD.

Type renvoyé

STRING

Exemples

L'instruction suivante renvoie le caractère de remplacement Unicode (�).

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SOUNDEX

SOUNDEX(value)

Description

Renvoie une STRING qui représente le code Soundex pour value.

SOUNDEX produit une représentation phonétique d'une chaîne. Il indexe les mots par le son, selon leur prononciation en anglais. Il est généralement utilisé pour déterminer si deux chaînes, telles que les noms de famille Levine et Lavine, ou les motsto et too, ont une prononciation similaire en anglais.

Le résultat de SOUNDEX est composé d'une lettre suivie de trois chiffres. Les caractères non latins sont ignorés. Si la chaîne restante est vide après la suppression de caractères non latins, une valeur STRING vide est renvoyée.

Type renvoyé

STRING

Exemples

WITH example AS (
  SELECT 'Ashcraft' AS value UNION ALL
  SELECT 'Raven' AS value UNION ALL
  SELECT 'Ribbon' AS value UNION ALL
  SELECT 'apple' AS value UNION ALL
  SELECT 'Hello world!' AS value UNION ALL
  SELECT '  H3##!@llo w00orld!' AS value UNION ALL
  SELECT '#1' AS value UNION ALL
  SELECT NULL AS value
)
SELECT value, SOUNDEX(value) AS soundex
FROM example;

+----------------------+---------+
| value                | soundex |
+----------------------+---------+
| Ashcraft             | A261    |
| Raven                | R150    |
| Ribbon               | R150    |
| apple                | a140    |
| Hello world!         | H464    |
|   H3##!@llo w00orld! | H464    |
| #1                   |         |
| NULL                 | NULL    |
+----------------------+---------+

SPLIT

SPLIT(value[, delimiter])

Description

Répartit value à l'aide de l'argument delimiter.

Pour STRING, le délimiteur par défaut est la virgule (,).

Pour BYTES, vous devez spécifier un délimiteur.

Une requête SPLIT faisant référence à un délimiteur vide produit un tableau de caractères UTF-8 pour les valeurs STRING et un tableau de BYTES pour les valeurs BYTES.

Une requête SPLIT sur une STRING vide renvoie un ARRAY contenant une seule STRING vide.

Type renvoyé

ARRAY de type STRING ou ARRAY de type BYTES

Exemples

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Description

Prend deux valeurs STRING ou BYTES. Renvoie TRUE si la deuxième valeur est un préfixe de la première.

Type renvoyé

BOOL

Exemples

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(value1, value2)

Description

Prend deux valeurs STRING ou BYTES. Renvoie l'index en base 1 de la première occurrence de value2 dans value1. Renvoie 0 si value2 est introuvable.

Type renvoyé

INT64

Exemples

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

Description

Renvoie une sous-chaîne de la valeur STRING ou BYTES fournie. L'argument position est un entier spécifiant la position de départ de la sous-chaîne et "position = 1" indique le premier caractère ou octet. L'argument length correspond au nombre maximal de caractères pour les arguments STRING ou au nombre maximal d'octets pour les arguments BYTES.

Si la valeur de position est négative, la fonction effectue le comptage à partir de la fin de value et "-1" indique le dernier caractère.

Si position est une position située au-delà de l'extrémité gauche de la STRING (position = 0 ou position < -LENGTH(value)), la fonction commence le comptage à partir de "position = 1". Si length dépasse la longueur de value, la fonction renvoie un nombre de caractères inférieur à length.

Si la valeur length est inférieure à 0, la fonction renvoie une erreur.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

SUBSTRING

SUBSTRING(value, position[, length])

Alias de SUBSTR.

TO_BASE32

TO_BASE32(bytes_expr)

Description

Convertit une séquence de valeurs de type BYTES en une valeur de type STRING encodée en base32. Pour convertir une valeur de type STRING encodée en base32 en valeurs de type BYTES, utilisez FROM_BASE32.

Type renvoyé

STRING

Exemple

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

Description

Convertit une séquence de valeurs de type BYTES en une valeur de type STRING encodée en base64. Pour convertir une valeur de type STRING encodée en base64 en valeurs de type BYTES, utilisez FROM_BASE64.

Type renvoyé

STRING

Exemple

SELECT TO_BASE64(b'\377\340') AS base64_string;

+---------------+
| base64_string |
+---------------+
| /+A=          |
+---------------+

Pour travailler avec un encodage utilisant un autre alphabet en base64, vous devrez peut-être composer TO_BASE64 avec la fonction REPLACE. Par exemple, l'encodage base64url sécurisé pour les URL et le nom de fichier couramment utilisé dans la programmation Web utilise -_= comme derniers caractères plutôt que +/=. Pour encoder une chaîne encodée en base64url, remplacez - et _ par + et /, respectivement.

SELECT REPLACE(REPLACE(TO_BASE64(b"\377\340"), "+", "-"), "/", "_") as websafe_base64;

+----------------+
| websafe_base64 |
+----------------+
| _-A=           |
+----------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Description

Prend une valeur et renvoie un tableau de INT64.

Si value est de type STRING, chaque élément du tableau renvoyé représente un point de code. Chaque point de code est compris dans les plages [0, 0xD7FF] et [0xE000, 0x10FFFF].
Si value est de type BYTES, chaque élément du tableau est une valeur de caractère ASCII étendu comprise dans la plage [0, 255].

Pour découvrir comment convertir un tableau de points de code en STRING ou en BYTES, consultez les sections CODE_POINTS_TO_STRING ou CODE_POINTS_TO_BYTES.

Type renvoyé

ARRAY sur INT64

Exemples

L'exemple suivant récupère les points de code pour chaque élément dans un tableau de mots.

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

L'exemple suivant convertit des représentations entières de BYTES en valeurs de caractère ASCII correspondantes.

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

L'exemple suivant montre la différence entre un résultat BYTES et un résultat STRING.

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

Notez que le caractère Ā est représenté sous la forme d'une séquence Unicode de deux octets. Par conséquent, la version BYTES de TO_CODE_POINTS renvoie un tableau avec deux éléments, tandis que la version STRING renvoie un tableau avec un seul élément.

TO_HEX

TO_HEX(bytes)

Description

Convertit une séquence de valeurs de type BYTES en valeur de type STRING hexadécimale. Chaque octet de la STRING est converti en deux caractères hexadécimaux compris dans la plage (0..9, a..f). Pour convertir une valeur STRING encodée en hexadécimal en BYTES, utilisez FROM_HEX.

Type renvoyé

STRING

Exemple

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRANSLATE

TRANSLATE(expression, source_characters, target_characters)

Description

Dans expression, remplace chaque caractère de source_characters par le caractère correspondant de target_characters. Toutes les entrées doivent être du même type (STRING ou BYTES).

Chaque caractère de expression ne peut être traduit qu'une fois.
Un caractère de expression qui n'est pas présent dans source_characters reste inchangé dans expression.
Un caractère de source_characters sans caractère correspondant dans target_characters est omis du résultat.
Un caractère en double dans source_characters génère une erreur.

Type renvoyé

STRING ou BYTES

Exemples

WITH example AS (
  SELECT 'This is a cookie' AS expression, 'sco' AS source_characters, 'zku' AS
  target_characters UNION ALL
  SELECT 'A coaster' AS expression, 'co' AS source_characters, 'k' as
  target_characters
)
SELECT expression, source_characters, target_characters, TRANSLATE(expression,
source_characters, target_characters) AS translate
FROM example;

+------------------+-------------------+-------------------+------------------+
| expression       | source_characters | target_characters | translate        |
+------------------+-------------------+-------------------+------------------+
| This is a cookie | sco               | zku               | Thiz iz a kuukie |
| A coaster        | co                | k                 | A kaster         |
+------------------+-------------------+-------------------+------------------+

TRIM

TRIM(value1[, value2])

Description

Supprime tous les caractères de début et de fin qui correspondent à value2. Si value2 n'est pas spécifiée, tous les espaces blancs de début et de fin (tels que définis par la norme Unicode) sont supprimés. Si le premier argument est de type BYTES, le deuxième argument est obligatoire.

Si value2 contient plusieurs caractères ou octets, la fonction supprime tous les caractères ou octets de début et de fin contenus dans value2.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UNICODE

UNICODE(value)

Description

Renvoie le point de code Unicode pour le premier caractère de value. Renvoie 0 si value est vide ou si le point de code Unicode obtenu est 0.

Type renvoyé

INT64

Exemples

SELECT UNICODE('âbcd') as A, UNICODE('â') as B, UNICODE('') as C, UNICODE(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 226   | 226   | 0     | NULL  |
+-------+-------+-------+-------+

UPPER

UPPER(value)

Description

Pour les arguments STRING, cette fonction renvoie la chaîne d'origine avec tous les caractères alphabétiques en majuscules. Le mappage entre majuscules et minuscules est effectué en fonction de la base de données de caractères Unicode, sans prise en compte des mappages spécifiques à la langue.

Les arguments BYTES sont quant à eux traités comme du texte ASCII, et les octets supérieurs à 127 ne sont pas modifiés.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

Fonctions JSON

BigQuery accepte des fonctions permettant de récupérer des données stockées dans des chaînes au format JSON, ainsi que des fonctions permettant de transformer des données en chaînes au format JSON.

Présentation de la fonction

Fonctions d'extraction JSON standards (recommandé)

Les fonctions suivantes utilisent des guillemets doubles pour échapper les caractères JSONPath non valides : "a.b".

Ce comportement est conforme à la norme ANSI.

Fonction JSON	Description	Type renvoyé
`JSON_QUERY`	Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un entier ou une valeur booléenne.	`STRING` au format JSON
`JSON_VALUE`	Extrait une valeur scalaire. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL `NULL` si une valeur non scalaire est sélectionnée.	`STRING`
`JSON_QUERY_ARRAY`	Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des entiers et des valeurs booléennes.	`ARRAY<JSON-formatted STRING>`
`JSON_VALUE_ARRAY`	Extrait un tableau de valeurs scalaires. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL `NULL` si la valeur sélectionnée n'est pas un tableau, ou s'il ne s'agit pas d'un tableau ne contenant que des valeurs scalaires.	`ARRAY<STRING>`

Anciennes fonctions d'extraction JSON

Les fonctions suivantes utilisent des guillemets simples et des crochets pour échapper les caractères JSONPath non valides : ['a.b'].

Bien que ces fonctions soient compatibles avec BigQuery, nous vous recommandons d'utiliser celles figurant dans le tableau précédent.

Fonction JSON	Description	Type renvoyé
`JSON_EXTRACT`	Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un entier ou une valeur booléenne.	`STRING` au format JSON
`JSON_EXTRACT_SCALAR`	Extrait une valeur scalaire. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL `NULL` si une valeur non scalaire est sélectionnée.	`STRING`
`JSON_EXTRACT_ARRAY`	Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des entiers et des valeurs booléennes.	`ARRAY<JSON-formatted STRING>`
`JSON_EXTRACT_STRING_ARRAY`	Extrait un tableau de valeurs scalaires. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs. Renvoie un résultat SQL `NULL` si la valeur sélectionnée n'est pas un tableau, ou s'il ne s'agit pas d'un tableau ne contenant que des valeurs scalaires.	`ARRAY<STRING>`

Autres fonctions JSON

Fonction JSON	Description	Type renvoyé
`TO_JSON_STRING`	Renvoie la représentation d'une valeur sous forme de chaîne JSON.	`STRING` au format JSON

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Description

Extrait une valeur JSON, telle qu'un tableau ou un objet, ou une valeur scalaire au format JSON, telle qu'une chaîne, un entier ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path renvoie un résultat JSON null, celui-ci est converti en résultat SQL NULL.

Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_EXTRACT. Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des entiers et des valeurs booléennes, utilisez JSON_EXTRACT_SCALAR.

Type renvoyé

STRING au format JSON

Exemples

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+

SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+

SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+

SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Description

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path renvoie un résultat JSON null, celui-ci est converti en résultat SQL NULL.

Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_QUERY. Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des entiers et des valeurs booléennes, utilisez JSON_VALUE.

Type renvoyé

STRING au format JSON

Exemples

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+

SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+

SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+

SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr, json_path)

Description

Extrait une valeur scalaire, puis la renvoie sous forme de chaîne. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Supprime les guillemets les plus externes et l'échappement des valeurs renvoyées. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path renvoie un résultat JSON null ou une valeur non scalaire (en d'autres termes, si json_path fait référence à un objet ou à un tableau), un résultat SQL NULL est renvoyé.

Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des entiers et des valeurs booléennes, utilisez JSON_EXTRACT_SCALAR. Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_EXTRACT.

Type renvoyé

STRING

Exemples

L'exemple suivant compare le mode d'envoi des résultats des fonctions JSON_EXTRACT et JSON_EXTRACT_SCALAR.

SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets, [' ']. Exemple :

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSON_VALUE

JSON_VALUE(json_string_expr, json_path)

Description

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si json_path renvoie un résultat JSON null ou une valeur non scalaire (en d'autres termes, si json_path fait référence à un objet ou à un tableau), un résultat SQL NULL est renvoyé.

Si vous ne souhaitez extraire que des valeurs scalaires telles que des chaînes, des entiers et des valeurs booléennes, utilisez JSON_VALUE. Si vous souhaitez inclure des valeurs non scalaires telles que des tableaux dans l'extraction, utilisez JSON_QUERY.

Type renvoyé

STRING

Exemples

SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles. Exemple :

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs JSON, telles que des tableaux ou des objets, et des valeurs scalaires au format JSON, telles que des chaînes, des entiers et des valeurs booléennes. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que l'intégralité de la chaîne au format JSON est analysée.

Type renvoyé

ARRAY<JSON-FORMATTED STRING>

Exemples

L'opération suivante extrait les éléments d'une chaîne au format JSON dans un tableau de chaînes :

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;

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

L'opération suivante extrait un tableau de chaînes et le convertit en tableau entier :

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

L'opération suivante extrait les valeurs de chaîne d'une chaîne au format JSON dans un tableau :

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

L'opération suivante extrait uniquement les éléments de la propriété fruit dans un tableau :

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Les noms suivants sont équivalents :

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets, [' ']. Exemple :

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

Les exemples suivants examinent la manière dont les requêtes non valides et les tableaux vides sont traités :

Si un chemin JSONPath n'est pas valide, une erreur est renvoyée.
Si une chaîne au format JSON n'est pas valide, le résultat est NULL.
Vous pouvez utiliser des tableaux vides dans la chaîne au format JSON.

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])

Description

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que l'intégralité de la chaîne au format JSON est analysée.

Type renvoyé

ARRAY<JSON-FORMATTED STRING>

Exemples

L'opération suivante extrait les éléments d'une chaîne au format JSON dans un tableau de chaînes :

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

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

L'opération suivante extrait un tableau de chaînes et le convertit en tableau entier :

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

L'opération suivante extrait les valeurs de chaîne d'une chaîne au format JSON dans un tableau :

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

L'opération suivante extrait uniquement les éléments de la propriété fruit dans un tableau :

SELECT JSON_QUERY_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Les noms suivants sont équivalents :

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles : " ". Exemple :

SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

Les exemples suivants montrent la manière dont les requêtes non valides et les tableaux vides sont traités :

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

Description

Extrait un tableau de valeurs scalaires et renvoie un tableau de valeurs scalaires au format chaîne. Une valeur scalaire peut représenter une chaîne, un entier ou une valeur booléenne. Si une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets.

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que l'intégralité de la chaîne au format JSON est analysée.

Type renvoyé

ARRAY<STRING>

Exemples

L'exemple suivant compare le mode de renvoi des résultats pour les fonctions JSON_EXTRACT_ARRAY et JSON_EXTRACT_STRING_ARRAY.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

L'opération suivante extrait les éléments d'une chaîne au format JSON dans un tableau de chaînes :

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-------------------+
| string_array      |
+-------------------+
| [foo, bar, baz]   |
+-------------------+

L'opération suivante extrait un tableau de chaînes et le convertit en tableau entier :

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Les noms suivants sont équivalents :

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets simples et de crochets : [' ']. Exemple :

SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

Les exemples suivants examinent la manière dont les requêtes non valides et les tableaux vides sont traités :

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

Description

json_string_expr : chaîne au format JSON. Exemple :
```
{"class" : {"students" : [{"name" : "Jane"}]}}
```
json_path : format JSONPath. Identifie la ou les valeurs que vous souhaitez obtenir à partir de la chaîne au format JSON. Si ce paramètre facultatif n'est pas renseigné, le symbole JSONPath $ est appliqué, ce qui signifie que l'intégralité de la chaîne au format JSON est analysée.

Type renvoyé

ARRAY<STRING>

Exemples

L'exemple suivant compare le mode de renvoi des résultats pour les fonctions JSON_QUERY_ARRAY et JSON_VALUE_ARRAY.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

L'opération suivante extrait les éléments d'une chaîne au format JSON dans un tableau de chaînes :

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

L'opération suivante extrait un tableau de chaînes et le convertit en tableau entier :

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

Les noms suivants sont équivalents :

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Dans les cas où une clé JSON utilise des caractères JSONPath non valides, vous pouvez les échapper à l'aide de guillemets doubles : " ". Exemple :

SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

Les exemples suivants examinent la manière dont les requêtes non valides et les tableaux vides sont traités :

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Description

Renvoie une représentation de value sous forme de chaîne JSON. Cette fonction accepte un paramètre booléen facultatif appelé pretty_print. Si pretty_print est true, la valeur renvoyée est formatée afin d'of