Cet article est une compilation des fonctions, des opérateurs et des expressions conditionnelles.
Pour en savoir plus sur l'appel de fonction, les règles d'appel de fonction, le préfixe SAFE
et les types spéciaux d'arguments, consultez la page Appels de fonctions.
OPÉRATEURS ET EXPRESSIONS CONDITIONNELLES
Opérateurs
Les opérateurs sont représentés par des caractères spéciaux ou des mots-clés ; ils n'utilisent pas la syntaxe d'appel de fonction. Un opérateur manipule un certain nombre d'entrées de données (ces entrées de données sont également appelées opérandes), puis renvoie un résultat.
Conventions communes :
- Sauf indication contraire, tous les opérateurs renvoient
NULL
lorsque l'un des opérandes estNULL
. - Tous les opérateurs génèrent une erreur lorsque le résultat du calcul entraîne un débordement.
- Pour toutes les opérations en virgule flottante,
+/-inf
etNaN
ne peuvent être renvoyés que si l'un des opérandes est+/-inf
ouNaN
. Dans les autres cas, une erreur est renvoyée.
Priorité des opérateurs
Le tableau ci-dessous répertorie tous les opérateurs de BigQuery, classés de la priorité la plus élevée à la plus faible, c'est-à-dire en fonction de l'ordre selon lequel ils seront évalués dans une instruction.
Ordre de priorité | Opérateur | Types de données d'entrée | Nom | Arité de l'opérateur |
---|---|---|---|---|
1 | Opérateur d'accès aux champs | Structure JSON |
Opérateur d'accès aux champs | Binaire |
Opérateur d'indice de tableau | ARRAY | Position du tableau. Doit être utilisé avec OFFSET ou ORDINAL (consultez la section Fonctions de tableau). | Binaire | |
Opérateur indice JSON | JSON | Nom du champ ou position du tableau au format JSON. | Binaire | |
2 | + | Tous les types numériques | Plus unaire. | Unaire |
- | Tous les types numériques | Moins unaire | Unaire | |
~ | Entier ou BYTES | Opérateur NOT (PAS) au niveau du bit | Unaire | |
3 | * | Tous les types numériques | Multiplication | Binaire |
/ | Tous les types numériques | Division | Binaire | |
|| | STRING, BYTES ou ARRAY<T> | Opérateur de concaténation | Binaire | |
4 | + | Tous les types numériques, DATE avec INT64, INTERVAL | Addition | Binaire |
- | Tous les types numériques, DATE avec INT64, INTERVAL | Soustraction | Binaire | |
5 | << | Entier ou BYTES | Décalage à gauche bit à bit | Binaire |
>> | Entier ou BYTES | Décalage à droite bit à bit | Binaire | |
6 | & | Entier ou BYTES | Opérateur AND (ET) au niveau du bit | Binaire |
7 | ^ | Entier ou BYTES | Opérateur XOR (OU exclusif) au niveau du bit | Binaire |
8 | | | Entier ou BYTES | Opérateur OR (OU) au niveau du bit | Binaire |
9 (Opérateurs de comparaison) | = | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Égal à | Binaire |
< | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Moins de | Binaire | |
> | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Supérieur à | Binaire | |
<= | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Inférieur ou égal à | Binaire | |
>= | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Supérieur ou égal à | Binaire | |
!=, <> | Tout type comparable. Pour obtenir la liste complète, consultez la page Types de données. | Not Equal (Non égal à) | Binaire | |
[NOT] LIKE | STRING et octet | La valeur correspond [ne correspond pas] au modèle spécifié | Binaire | |
[NOT] BETWEEN | Tous types comparables. Pour obtenir la liste complète, consultez la page Types de données. | La valeur est [n'est pas] comprise dans la plage spécifiée | Binaire | |
[NOT] IN | Tous types comparables. Pour obtenir la liste complète, consultez la page Types de données. | La valeur est [n'est pas] comprise dans l'ensemble de valeurs spécifié | Binaire | |
IS [NOT] NULL |
Tous | La valeur est [n'est pas] NULL |
Unaire | |
IS [NOT] TRUE | BOOL | La valeur est [n'est pas] TRUE. | Unaire | |
IS [NOT] FALSE | BOOL | La valeur est [n'est pas] FALSE. | Unaire | |
10 | NOT | BOOL | Logical NOT (Opérateur logique PAS) | Unaire |
11 | AND | BOOL | Logical AND (Opérateur logique ET) | Binaire |
12 | OR | BOOL | Logical OR (Opérateur logique OU) | Binaire |
Les opérateurs ayant le même niveau de priorité sont considérés selon un schéma "associatif gauche". Cela signifie que le regroupement de ces opérateurs commence par la gauche, puis se poursuit vers la droite. Par exemple, l'expression :
x AND y AND z
est interprétée comme :
( ( x AND y ) AND z )
L'expression :
x * y / z
est interprétée comme :
( ( x * y ) / z )
Tous les opérateurs de comparaison ont la même priorité, mais les opérateurs de comparaison ne sont pas associatifs. Par conséquent, des parenthèses sont nécessaires afin de résoudre les ambiguïtés. Exemple :
(x < y) IS FALSE
Opérateur d'accès aux champs
expression.fieldname[. ...]
Description
Récupère la valeur d'un champ. Également appelé "opérateur point". Peut être utilisé pour accéder à des champs imbriqués. Par exemple, expression.fieldname1.fieldname2
.
Types d'entrée
STRUCT
JSON
Type renvoyé
- Pour
STRUCT
: type de données SQL defieldname
. Si un champ est introuvable dans la structure, une erreur est générée. - Pour
JSON
:JSON
. Si un champ est introuvable dans une valeur JSON, une valeur SQLNULL
est renvoyée.
Exemple
Dans l'exemple suivant, l'expression est t.customer
, et les opérations d'accès aux champs sont .address
et .country
. Une opération est l'application d'un opérateur (.
) à des opérandes spécifiques (dans ce cas,address
et country
ou, plus précisément, t.customer
et address
pour la première opération, et t.customer.address
et country
pour la deuxième opération).
WITH orders AS (
SELECT STRUCT(STRUCT('Yonge Street' AS street, 'Canada' AS country) AS address) AS customer
)
SELECT t.customer.address.country FROM orders AS t;
+---------+
| country |
+---------+
| Canada |
+---------+
Opérateur d'indice de tableau
array_expression[array_subscript_specifier]
array_subscript_specifier:
position_keyword(index)
position_keyword:
{ OFFSET | SAFE_OFFSET | ORDINAL | SAFE_ORDINAL }
Description
Récupère une valeur d'un tableau à un emplacement spécifique.
Types d'entrée
array_expression
: le tableau d'entrée.position_keyword
: la position où commence l'index dans le tableau et la manière dont les index hors plage sont gérés. Vous disposez des options suivantes :OFFSET
: l'index commence à zéro. Cette fonction génère une erreur si l'index est en dehors de la plage.SAFE_OFFSET
: l'index commence à zéro. RenvoieNULL
si l'index est en dehors de la plage.ORDINAL
: l'index commence à un. Cette fonction génère une erreur si l'index est en dehors de la plage.SAFE_ORDINAL
: l'index commence à un. RenvoieNULL
si l'index est en dehors de la plage.
index
: entier représentant une position spécifique dans le tableau.
Type renvoyé
T
où array_expression
est ARRAY<T>
.
Exemples
Dans cet exemple, l'opérateur d'indice de tableau est utilisé pour renvoyer des valeurs à des emplacements spécifiques dans item_array
. Cet exemple montre également ce qui se produit lorsque vous référencez un index (6
) dans un tableau hors plage. Si le préfixe SAFE
est inclus, NULL
est renvoyé. Sinon, une erreur est générée.
WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
item_array,
item_array[OFFSET(1)] AS item_offset,
item_array[ORDINAL(1)] AS item_ordinal,
item_array[SAFE_OFFSET(6)] AS item_safe_offset,
FROM Items
+----------------------------------+--------------+--------------+------------------+
| item_array | item_offset | item_ordinal | item_safe_offset |
+----------------------------------+--------------+--------------+------------------+
| [coffee, tea, milk] | tea | coffee | NULL |
+----------------------------------+--------------+--------------+------------------+
Dans l'exemple suivant, lorsque vous référencez un index dans un tableau situé en dehors de la plage et que le préfixe SAFE
n'est pas inclus, une erreur est générée.
WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
item_array[OFFSET(6)] AS item_offset
FROM Items
-- Error. OFFSET(6) is out of range.
Opérateur indice JSON
json_expression[array_element_id]
json_expression[field_name]
Description
Récupère la valeur d'un élément de tableau ou d'un champ dans une expression JSON. Peut être utilisé pour accéder à des données imbriquées.
Types d'entrée
JSON expression
: expressionJSON
contenant un élément de tableau ou un champ à renvoyer.[array_element_id]
: expressionINT64
qui représente un index basé sur zéro dans le tableau. Si une valeur négative est saisie, ou si la valeur est supérieure ou égale à la taille du tableau, ou si l'expression JSON ne représente pas un tableau JSON, une valeur SQLNULL
est renvoyée.[field_name]
: expressionSTRING
qui représente le nom d'un champ au format JSON. Si le nom du champ est introuvable ou si l'expression JSON n'est pas un objet JSON, une valeur SQLNULL
est renvoyée.
Type renvoyé
JSON
Exemple
Dans l'exemple suivant :
json_value
est une expression JSON..class
est un accès à un champ JSON..students
est un accès à un champ JSON.[0]
est une expression d'indice JSON avec un décalage d'élément qui accède au zéro élément d'un tableau de la valeur JSON.['name']
est une expression d'indice JSON avec un nom de champ qui accède à un champ.
SELECT json_value.class.students[0]['name'] AS first_student
FROM
UNNEST(
[
JSON '{"class" : {"students" : [{"name" : "Jane"}]}}',
JSON '{"class" : {"students" : []}}',
JSON '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])
AS json_value;
+-----------------+
| first_student |
+-----------------+
| "Jane" |
| NULL |
| "John" |
+-----------------+
Opérateurs arithmétiques
Tous les opérateurs arithmétiques acceptent les entrées de type numérique T, et les résultats sont de type T sauf indication contraire dans la description ci-dessous :
Nom | Syntaxe |
---|---|
Addition | X + Y |
Soustraction | X - Y |
Multiplication | X * Y |
Division | X / Y |
Plus unaire | + X |
Moins unaire | - X |
REMARQUE : Les opérations de division par zéro renvoient une erreur. Pour qu'un résultat différent soit renvoyé, utilisez la fonction IEEE_DIVIDE ou la fonction SAFE_DIVIDE.
Types de résultats pour l'addition, la soustraction et la multiplication :
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 |
Types de résultats pour la division :
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 |
Types de résultats pour Plus unaire :
ENTRÉE | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
RÉSULTAT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
Types de résultats pour Moins unaire :
ENTRÉE | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
RÉSULTAT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
Opérateurs arithmétiques pour les dates
Les opérateurs "+" et "-" peuvent être utilisés pour les opérations arithmétiques sur les dates.
date_expression + int64_expression
int64_expression + date_expression
date_expression - int64_expression
Description
Ajoute les jours int64_expression
à date_expression
ou les en soustrait. Cela équivaut aux fonctions DATE_ADD
ou DATE_SUB
, lorsque l'intervalle est exprimé en jours.
Type de données renvoyé
DATE
Exemple
SELECT DATE "2020-09-22" + 1 AS day_later, DATE "2020-09-22" - 7 AS week_ago
+------------+------------+
| day_later | week_ago |
+------------+------------+
| 2020-09-23 | 2020-09-15 |
+------------+------------+
Soustraction de date et heure
date_expression - date_expression
timestamp_expression - timestamp_expression
datetime_expression - datetime_expression
Description
Calcule la différence entre deux valeurs de date et heure sous forme d'intervalle.
Type de données renvoyé
INTERVAL
Exemple
SELECT
DATE "2021-05-20" - DATE "2020-04-19" AS date_diff,
TIMESTAMP "2021-06-01 12:34:56.789" - TIMESTAMP "2021-05-31 00:00:00" AS time_diff
+-------------------+------------------------+
| date_diff | time_diff |
+-------------------+------------------------+
| 0-0 396 0:0:0 | 0-0 0 36:34:56.789 |
+-------------------+------------------------+
Opérateurs arithmétiques pour les intervalles
Ajout et soustraction
date_expression + interval_expression = DATETIME
date_expression - interval_expression = DATETIME
timestamp_expression + interval_expression = TIMESTAMP
timestamp_expression - interval_expression = TIMESTAMP
datetime_expression + interval_expression = DATETIME
datetime_expression - interval_expression = DATETIME
Description
Ajoute un intervalle à une valeur de date et d'heure ou soustrait un intervalle d'une valeur de date et d'heure Exemple
SELECT
DATE "2021-04-20" + INTERVAL 25 HOUR AS date_plus,
TIMESTAMP "2021-05-02 00:01:02.345" - INTERVAL 10 SECOND AS time_minus;
+-------------------------+--------------------------------+
| date_plus | time_minus |
+-------------------------+--------------------------------+
| 2021-04-21 01:00:00 | 2021-05-02 00:00:52.345+00 |
+-------------------------+--------------------------------+
Multiplication et division
interval_expression * integer_expression = INTERVAL
interval_expression / integer_expression = INTERVAL
Description
Multiplie ou divise une valeur d'intervalle par un entier.
Exemple
SELECT
INTERVAL '1:2:3' HOUR TO SECOND * 10 AS mul1,
INTERVAL 35 SECOND * 4 AS mul2,
INTERVAL 10 YEAR / 3 AS div1,
INTERVAL 1 MONTH / 12 AS div2
+----------------+--------------+-------------+--------------+
| mul1 | mul2 | div1 | div2 |
+----------------+--------------+-------------+--------------+
| 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 |
+----------------+--------------+-------------+--------------+
Opérateurs bit à bit
Tous les opérateurs fonctionnant au niveau du bit renvoient le même type et la même longueur que le premier opérande.
Nom | Syntaxe | Type de données d'entrée | Description |
---|---|---|---|
Opérateur NOT (PAS) au niveau du bit | ~ X | Entier ou BYTES | Effectue une négation logique sur chaque bit, en formant le complément de la valeur binaire donnée pour chaque bit. |
Opérateur OR (OU) au niveau du bit | X | Y | X : entier ou BYTES
Y : même type que X |
Prend deux combinaisons binaires de longueur égale et effectue l'opération logique "OU inclusif" sur chaque paire de bits correspondants. Cet opérateur génère une erreur si X et Y sont des séquences BYTES de longueurs différentes. |
Opérateur XOR (OU exclusif) au niveau du bit | X ^ Y | X : entier ou BYTES
Y : même type que X |
Prend deux combinaisons binaires de longueur égale et effectue l'opération logique "OU exclusif" sur chaque paire de bits correspondants. Cet opérateur génère une erreur si X et Y sont des séquences BYTES de longueurs différentes. |
Opérateur AND (ET) au niveau du bit | X & Y | X : entier ou BYTES
Y : même type que X |
Prend deux combinaisons binaires de longueur égale et effectue l'opération logique "ET" sur chaque paire de bits correspondants. Cet opérateur génère une erreur si X et Y sont des séquences BYTES de longueurs différentes. |
Décalage à gauche | X << Y | X : entier ou BYTES
Y : INT64 |
Décale le premier opérande X vers la gauche. Cet opérateur renvoie 0 ou une séquence d'octets de b'\x00' si le deuxième opérande Y est supérieur ou égal à la longueur en bits du premier opérande X (par exemple, 64 si X est de type INT64). Cet opérateur génère une erreur si Y est négatif. |
Décalage à droite | X >> Y | X : entier ou BYTES
Y : INT64 |
Décale le premier opérande X vers la droite. Cet opérateur n'effectue pas d'extension de bit de signe avec un type signé (c'est-à-dire qu'il remplit les bits vacants à gauche avec des 0). Cet opérateur renvoie 0 ou une séquence d'octets de b'\x00' si le deuxième opérande Y est supérieur ou égal à la longueur en bits du premier opérande X (par exemple, 64 si X est de type INT64). Cet opérateur génère une erreur si Y est négatif. |
Opérateurs logiques
BigQuery est compatible avec les opérateurs logiques AND
, OR
et NOT
.
Les opérateurs logiques n'autorisent que des entrées de type BOOL ou NULL
et utilisent une logique à trois valeurs pour produire un résultat. Le résultat peut être TRUE
, FALSE
ou NULL
:
x | y | x AND y | x OR y |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | TRUE | FALSE | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | TRUE | NULL | TRUE |
NULL | FALSE | FALSE | NULL |
NULL | NULL | NULL | NULL |
x | NOT x |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
Exemples
Les exemples de cette section font référence à une table nommée entry_table
:
+-------+
| entry |
+-------+
| a |
| b |
| c |
| NULL |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'
-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL
+-------+
| entry |
+-------+
| a |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')
-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL
+-------+
| entry |
+-------+
| b |
| c |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL
-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE
+-------+
| entry |
+-------+
| NULL |
+-------+
Opérateurs de comparaison
Les comparaisons renvoient toujours une valeur de type BOOL. Les comparaisons nécessitent généralement que les deux opérandes soient du même type. Si les opérandes sont de types différents et que BigQuery peut convertir les valeurs de ces types en un type commun sans perte de précision, BigQuery les convertit généralement (par coercition) dans ce type commun pour la comparaison. En pareil cas, BigQuery va généralement convertir (par coercition) des types littéraux en type "non littéral" si un tel type est présent. Les types de données comparables sont définis dans la section Types de données.
Les types STRUCT n'acceptent que quatre opérateurs de comparaison : égal (=), différent (!= et <>) et "dans" (IN).
Les règles suivantes s'appliquent lors de la comparaison de ces types de données :
- FLOAT64 : toutes les comparaisons avec NaN renvoient FALSE, sauf pour
!=
et<>
qui renvoient TRUE. - BOOL : FALSE est inférieur à TRUE.
- STRING : les chaînes sont comparées point de code par point de code. Par conséquent, pour garantir que des chaînes canoniquement équivalentes seront bien considérées comme égales, il est nécessaire de les normaliser en premier lieu.
NULL
: la convention tient ici. Toute opération avec une entréeNULL
renvoieNULL
.
Nom | Syntaxe | Description |
---|---|---|
Less Than (Inférieur à) | X < Y | Renvoie TRUE si X est inférieur à Y. Cet opérateur accepte la spécification de classement. |
Less Than or Equal To (Inférieur ou égal à) | X <= Y | Renvoie TRUE si X est inférieur ou égal à Y. Cet opérateur accepte la spécification de classement. |
Greater Than (Supérieur à) | X > Y | Renvoie TRUE si X est supérieur à Y. Cet opérateur accepte la spécification de classement. |
Greater Than or Equal To (Supérieur ou égal à) | X >= Y | Renvoie TRUE si X est supérieur ou égal à Y. Cet opérateur accepte la spécification de classement. |
Égal à | X = Y | Renvoie TRUE si X est égal à Y. Cet opérateur accepte la spécification de classement. |
Not Equal (Non égal à) | X != Y X <> Y |
Renvoie TRUE si X n'est pas égal à Y. Cet opérateur accepte la spécification de classement. |
BETWEEN | X [NOT] BETWEEN Y AND Z |
Renvoie TRUE (vrai) si X est [n'est pas] compris dans la plage spécifiée. Le résultat de "X BETWEEN Y AND Z" équivaut à "Y <= X AND X <= Z", mais X n'est évalué qu'une fois dans le premier cas. Cet opérateur accepte la spécification de classement. |
LIKE | X [NOT] LIKE Y | Vérifie si la STRING dans le premier opérande X correspond à un schéma spécifié par le deuxième opérande Y. Les expressions peuvent contenir les caractères suivants :
|
IN | Multiple (voir ci-dessous) |
Renvoie FALSE si l'opérande de droite est vide. Renvoie NULL si l'opérande de gauche est NULL . Renvoie TRUE ou NULL (mais jamais FALSE) si l'opérande de droite contient NULL . Les arguments situés de part et d'autre de l'opérateur "IN" sont des expressions génériques. Il n'est pas absolument nécessaire que l'un des deux opérandes soit un littéral, bien que l'utilisation d'un littéral à droite soit l'approche la plus courante. X n'est évalué qu'une fois.
Cet opérateur accepte généralement la spécification de classement.
|
Lorsque vous effectuez un test d'égalité pour des valeurs ayant un type de données STRUCT, il est possible qu'un ou plusieurs champs aient la valeur NULL
. Dans ces cas :
- Si toutes les valeurs de champ non nulles sont égales, la comparaison renvoie "NULL".
- Si certaines valeurs de champ non nulles ne sont pas égales, la comparaison renvoie "FALSE".
Le tableau suivant montre comment les types de données STRUCT sont comparés lorsqu'ils contiennent des champs dont la valeur est NULL
.
Struct1 | Struct2 | Struct1 = Struct2 |
---|---|---|
STRUCT(1, NULL) |
STRUCT(1, NULL) |
NULL |
STRUCT(1, NULL) |
STRUCT(2, NULL) |
FALSE |
STRUCT(1,2) |
STRUCT(1, NULL) |
NULL |
Opérateur EXISTS
EXISTS ( subquery )
Description
Renvoie TRUE
si la sous-requête génère une ou plusieurs lignes. Renvoie FALSE
si la sous-requête ne génère aucune ligne. Ne renvoie jamais NULL
. Pour en savoir plus sur l'utilisation d'une sous-requête avec EXISTS
, consultez la page Sous-requêtes EXISTS
.
Exemples
Dans cet exemple, l'opérateur EXISTS
renvoie FALSE
, car il n'existe aucune ligne dans Words
dont le sens est south
:
WITH Words AS (
SELECT 'Intend' as value, 'east' as direction UNION ALL
SELECT 'Secure', 'north' UNION ALL
SELECT 'Clarity', 'west'
)
SELECT EXISTS ( SELECT value FROM Words WHERE direction = 'south' ) as result;
+--------+
| result |
+--------+
| FALSE |
+--------+
Opérateur IN
L'opérateur IN
accepte la syntaxe suivante :
search_value [NOT] IN value_set
value_set:
{
(expression[, ...])
| (subquery)
| UNNEST(array_expression)
}
Description
Recherche une même valeur dans un ensemble de valeurs.
Les règles sémantiques s'appliquent, mais en général, IN
renvoie TRUE
si une valeur égale est trouvée, FALSE
si une valeur égale est exclue, sinon NULL
. NOT IN
renvoie FALSE
si une valeur égale est trouvée, TRUE
si une valeur égale est exclue, sinon NULL
.
search_value
: expression comparée à un ensemble de valeurs.value_set
: une ou plusieurs valeurs à comparer à une valeur de recherche.(expression[, ...])
: liste d'expressions.(subquery)
: sous-requête qui renvoie une seule colonne. Les valeurs de cette colonne correspondent à l'ensemble de valeurs. Si aucune ligne n'est générée, l'ensemble de valeurs est vide.UNNEST(array_expression)
: opérateur UNNEST qui renvoie une colonne de valeurs à partir d'une expression de tableau. Cela équivaut à :IN (SELECT element FROM UNNEST(array_expression) AS element)
Lorsque l'opérateur IN
est utilisé, la sémantique suivante s'applique dans l'ordre suivant :
- Renvoie
FALSE
sivalue_set
est vide. - Renvoie
NULL
sisearch_value
est défini surNULL
. - Renvoie
TRUE
sivalue_set
contient une valeur égale àsearch_value
. - Renvoie
NULL
sivalue_set
contient une valeurNULL
. - Cela renvoie
FALSE
.
Lorsque l'opérateur NOT IN
est utilisé, la sémantique suivante s'applique dans l'ordre suivant :
- Renvoie
TRUE
sivalue_set
est vide. - Renvoie
NULL
sisearch_value
est défini surNULL
. - Renvoie
FALSE
sivalue_set
contient une valeur égale àsearch_value
. - Renvoie
NULL
sivalue_set
contient une valeurNULL
. - Cela renvoie
TRUE
.
Cet opérateur est généralement compatible avec le classement, mais pas x [NOT] IN UNNEST
.
La sémantique de :
x IN (y, z, ...)
est définie comme équivalente à :
(x = y) OR (x = z) OR ...
et les formulaires de sous-requête et de tableau sont définis de la même manière.
x NOT IN ...
équivaut à :
NOT(x IN ...)
Le formulaire UNNEST
traite une analyse de tableau comme UNNEST
dans la clause FROM :
x [NOT] IN UNNEST(<array expression>)
Ce formulaire est souvent utilisé avec les paramètres ARRAY
. Exemple :
x IN UNNEST(@array_parameter)
Consultez la rubrique Tableaux pour plus d'informations sur l'utilisation de cette syntaxe.
IN
peut être utilisé avec des clés composées de plusieurs parties à l'aide de la syntaxe du constructeur de structure.
Exemple :
(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )
Pour en savoir plus, consultez la section Type de structure.
Type de données renvoyé
BOOL
Exemples
Vous pouvez émuler des tables temporaires pour Words
et Items
dans les exemples suivants à l'aide des clauses WITH
:
WITH Words AS (
SELECT 'Intend' as value UNION ALL
SELECT 'Secure' UNION ALL
SELECT 'Clarity' UNION ALL
SELECT 'Peace' UNION ALL
SELECT 'Intend'
)
SELECT * FROM Words;
+----------+
| value |
+----------+
| Intend |
| Secure |
| Clarity |
| Peace |
| Intend |
+----------+
WITH
Items AS (
SELECT STRUCT('blue' AS color, 'round' AS shape) AS info UNION ALL
SELECT STRUCT('blue', 'square') UNION ALL
SELECT STRUCT('red', 'round')
)
SELECT * FROM Items;
+----------------------------+
| info |
+----------------------------+
| {blue color, round shape} |
| {blue color, square shape} |
| {red color, round shape} |
+----------------------------+
Exemple avec IN
et une expression :
SELECT * FROM Words WHERE value IN ('Intend', 'Secure');
+----------+
| value |
+----------+
| Intend |
| Secure |
| Intend |
+----------+
Exemple avec NOT IN
et une expression :
SELECT * FROM Words WHERE value NOT IN ('Intend');
+----------+
| value |
+----------+
| Secure |
| Clarity |
| Peace |
+----------+
Exemple avec IN
, une sous-requête scalaire et une expression :
SELECT * FROM Words WHERE value IN ((SELECT 'Intend'), 'Clarity');
+----------+
| value |
+----------+
| Intend |
| Clarity |
| Intend |
+----------+
Exemple avec IN
et une opération UNNEST
:
SELECT * FROM Words WHERE value IN UNNEST(['Secure', 'Clarity']);
+----------+
| value |
+----------+
| Secure |
| Clarity |
+----------+
Exemple avec IN
et STRUCT
:
SELECT
(SELECT AS STRUCT Items.info) as item
FROM
Items
WHERE (info.shape, info.color) IN (('round', 'blue'));
+------------------------------------+
| item |
+------------------------------------+
| { {blue color, round shape} info } |
+------------------------------------+
Opérateurs IS
Les opérateurs IS renvoient TRUE ou FALSE pour la condition testée. Ils ne renvoient jamais NULL
, même pour les entrées NULL
, contrairement aux fonctions IS_INF et IS_NAN définies dans la section Fonctions mathématiques. Si NOT est présent, la valeur BOOL de sortie est inversée.
Syntaxe de la fonction | Type de données d'entrée | Type de données de résultat | Description |
---|---|---|---|
X IS [NOT] NULL |
N'importe quel type de valeur | BOOL | Renvoie TRUE si l'évaluation de l'opérande X donne NULL , renvoie FALSE dans le cas contraire. |
X IS [NOT] TRUE |
BOOL | BOOL | Renvoie TRUE si l'évaluation de l'opérande BOOL donne TRUE, renvoie FALSE dans le cas contraire. |
X IS [NOT] FALSE |
BOOL | BOOL | Renvoie TRUE si l'évaluation de l'opérande BOOL donne TRUE, renvoie FALSE dans le cas contraire. |
Opérateur IS DISTINCT FROM
expression_1 IS [NOT] DISTINCT FROM expression_2
Description
IS DISTINCT FROM
renvoie TRUE
si les valeurs d'entrée sont considérées comme distinctes les unes des autres par DISTINCT
et GROUP BY
. Sinon, renvoie FALSE
.
a IS DISTINCT FROM b
correspondant à TRUE
équivaut à :
SELECT COUNT(DISTINCT x) FROM UNNEST([a,b]) x
renvoie2
.SELECT * FROM UNNEST([a,b]) x GROUP BY x
renvoie 2 lignes.
a IS DISTINCT FROM b
équivaut à NOT (a = b)
, à l'exception des cas suivants :
- Cet opérateur ne renvoie jamais
NULL
, donc les valeursNULL
sont considérées comme différentes des valeurs nonNULL
, mais pas comme d'autres valeursNULL
. - Les valeurs
NaN
sont considérées comme différentes des valeurs nonNaN
, mais pas comme d'autres valeursNaN
.
Types d'entrée
expression_1
: première valeur à comparer. Il peut s'agir d'un type de données groupée,NULL
ouNaN
.expression_2
: deuxième valeur à comparer. Il peut s'agir d'un type de données groupée,NULL
ouNaN
.NOT
: si cette valeur est présente, la valeurBOOL
de sortie est inversée.
Type renvoyé
BOOL
Exemples
Celles-ci renvoient TRUE
:
SELECT 1 IS DISTINCT FROM 2
SELECT 1 IS DISTINCT FROM NULL
SELECT 1 IS NOT DISTINCT FROM 1
SELECT NULL IS NOT DISTINCT FROM NULL
Celles-ci renvoient FALSE
:
SELECT NULL IS DISTINCT FROM NULL
SELECT 1 IS DISTINCT FROM 1
SELECT 1 IS NOT DISTINCT FROM 2
SELECT 1 IS NOT DISTINCT FROM NULL
Opérateur de concaténation
L'opérateur de concaténation combine plusieurs valeurs en une seule.
Syntaxe de la fonction | Type de données d'entrée | Type de données de résultat |
---|---|---|
STRING || STRING [ || ... ] |
STRING | STRING |
BYTES || BYTES [ || ... ] |
BYTES | STRING |
ARRAY<T> || ARRAY<T> [ || ... ] |
ARRAY<T> | ARRAY<T> |
Expressions conditionnelles
Les expressions conditionnelles imposent des contraintes sur l'ordre d'évaluation de leurs entrées. En substance, elles sont évaluées de gauche à droite (avec possibilité de court-circuitage) et l'évaluation ne porte que sur la valeur de sortie qui a été choisie. Par opposition, les entrées des fonctions standards sont quant à elles toutes évaluées avant l'appel de la fonction. L'option de court-circuitage disponible avec les expressions conditionnelles peut être exploitée pour la gestion des erreurs ou l'optimisation des performances.
Expression CASE
CASE expr
WHEN expr_to_match THEN result
[ ... ]
[ ELSE else_result ]
END
Description
Compare successivement expr
à la valeur expr_to_match
de chaque clause WHEN
et renvoie le premier résultat pour lequel cette comparaison retourne "true". Les clauses WHEN
restantes et else_result
ne sont pas évalués. Si la comparaison expr = expr_to_match
renvoie false ou NULL pour toutes les clauses WHEN
, renvoie else_result
si celui-ci est présent, renvoie NULL s'il ne l'est pas.
expr
et expr_to_match
peuvent correspondre à n'importe quel type. Ils doivent pouvoir être convertis implicitement (coercition) en un supertype commun ; les comparaisons d'égalité sont effectuées sur des valeurs "contraintes". Il peut y avoir plusieurs types result
. Les expressions result
et else_result
doivent pouvoir être converties (coercition) en un supertype commun.
Cette expression accepte la spécification de classement.
Type de données renvoyé
Supertype de result
[, ...] et else_result
.
Exemple
WITH Numbers AS (
SELECT 90 as A, 2 as B UNION ALL
SELECT 50, 8 UNION ALL
SELECT 60, 6 UNION ALL
SELECT 50, 10
)
SELECT
A,
B,
CASE A
WHEN 90 THEN 'red'
WHEN 50 THEN 'blue'
ELSE 'green'
END
AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 8 | blue |
| 60 | 6 | green |
| 50 | 10 | blue |
+------------------+
CASE
CASE
WHEN condition THEN result
[ ... ]
[ ELSE else_result ]
END
Description
Évalue la condition de chaque clause WHEN
successive et renvoie le premier résultat pour lequel la condition est "true". Les autres clauses WHEN
et else_result
ne sont pas évaluées. Si toutes les conditions sont "false" ou NULL, la fonction renvoie else_result
si celui-ci est présent et NULL s'il ne l'est pas.
condition
doit être une expression booléenne. Il peut y avoir plusieurs types result
.
Les expressions result
et else_result
doivent pouvoir être converties implicitement (coercition) en un supertype commun.
Cette expression accepte la spécification de classement.
Type de données renvoyé
Supertype de result
[, ...] et else_result
.
Exemple
WITH Numbers AS (
SELECT 90 as A, 2 as B UNION ALL
SELECT 50, 6 UNION ALL
SELECT 20, 10
)
SELECT
A,
B,
CASE
WHEN A > 60 THEN 'red'
WHEN A > 30 THEN 'blue'
ELSE 'green'
END
AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 6 | blue |
| 20 | 10 | green |
+------------------+
COALESCE
COALESCE(expr[, ...])
Description
Renvoie la valeur de la première expression non nulle. Les expressions restantes ne sont pas évaluées. Une expression en entrée peut être de n'importe quel type. Il existe plusieurs types d'expressions en entrée. Toutes les expressions en entrée doivent pouvoir être converties implicitement (coercition) en un supertype commun.
Type de données renvoyé
Supertype de expr
[, ...].
Exemples
SELECT COALESCE('A', 'B', 'C') as result
+--------+
| result |
+--------+
| A |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result
+--------+
| result |
+--------+
| B |
+--------+
IF
IF(expr, true_result, else_result)
Description
Si expr
est "true", la fonction renvoie true_result
, sinon elle renvoie else_result
.
else_result
n'est pas évalué si expr
est "true". true_result
n'est pas évalué si expr
est "false" ou NULL.
expr
doit être une expression booléenne. true_result
et else_result
doivent pouvoir être convertis (coercition) en un supertype commun.
Type de données renvoyé
Supertype de true_result
et else_result
.
Exemple
WITH Numbers AS (
SELECT 10 as A, 20 as B UNION ALL
SELECT 50, 30 UNION ALL
SELECT 60, 60
)
SELECT
A,
B,
IF(A < B, 'true', 'false') AS result
FROM Numbers
+------------------+
| A | B | result |
+------------------+
| 10 | 20 | true |
| 50 | 30 | false |
| 60 | 60 | false |
+------------------+
IFNULL
IFNULL(expr, null_result)
Description
Si la valeur de expr
est NULL, renvoie null_result
. Sinon, la fonction renvoie expr
. Si la valeur de expr
n'est pas NULL, null_result
n'est pas évalué.
expr
et null_result
peuvent être de n'importe quel type et doivent pouvoir être convertis implicitement (coercition) en un supertype commun. Synonyme de COALESCE(expr, null_result)
.
Type de données renvoyé
Supertype de expr
ou null_result
.
Exemples
SELECT IFNULL(NULL, 0) as result
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT IFNULL(10, 0) as result
+--------+
| result |
+--------+
| 10 |
+--------+
NULLIF
NULLIF(expr, expr_to_match)
Description
Renvoie NULL si expr = expr_to_match
est "true", sinon elle renvoie expr
.
expr
et expr_to_match
doivent pouvoir être convertis implicitement (coercition) en un supertype commun et doivent être comparables.
Cette expression accepte la spécification de classement.
Type de données renvoyé
Supertype de expr
et expr_to_match
.
Exemple
SELECT NULLIF(0, 0) as result
+--------+
| result |
+--------+
| NULL |
+--------+
SELECT NULLIF(10, 0) as result
+--------+
| result |
+--------+
| 10 |
+--------+
FONCTIONS
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 deARRAY_AGG()
.DISTINCT
: chaque valeur distincte d'expression
n'est agrégée qu'une seule fois dans le résultat.IGNORE NULLS
ouRESPECT NULLS
: siIGNORE NULLS
est spécifié, les valeursNULL
sont exclues du résultat. SiRESPECT NULLS
est spécifié, les valeursNULL
sont incluses dans le résultat. Si aucune de ces clauses n'est spécifiée, les valeursNULL
sont incluses dans le résultat. Si un tableau du résultat final de la requête contient un élémentNULL
, 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 trisASC
et en dernier dans les trisDESC
. - 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.
- Pour chaque clé de tri, le sens de tri par défaut est
LIMIT
: spécifie le nombre maximal d'entréesexpression
dans le résultat. La limiten
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 ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT 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 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] |
+-------------------+
WITH vals AS
(
SELECT 1 x UNION ALL
SELECT -2 x UNION ALL
SELECT 3 x UNION ALL
SELECT -2 x UNION ALL
SELECT 1 x UNION ALL
)
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;
+------------+
| array_agg |
+------------+
| [-2, 1, 3] |
+------------+
WITH vals AS
(
SELECT 1 x, 'a' y UNION ALL
SELECT 1 x, 'b' y UNION ALL
SELECT 2 x, 'a' y UNION ALL
SELECT 2 x, 'c' y
)
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;
+---------------+
| x | array_agg |
+---------------+
| 1 | [a, b] |
| 2 | [a, c] |
+---------------+
SELECT
x,
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 trisASC
et en dernier dans les trisDESC
. - 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.
- Pour chaque clé de tri, le sens de tri par défaut est
LIMIT
: spécifie le nombre maximal d'entréesexpression
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 limiten
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 deAVG()
.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 |
+---------+
COUNT
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 queNULL
.
Cette fonction avec DISTINCT accepte la spécification de classement.
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
Vous pouvez utiliser la fonction COUNT
pour renvoyer le nombre de lignes d'une table ou le nombre de valeurs distinctes d'une expression. Exemple :
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 |
+------+------------+---------+
Si vous souhaitez compter le nombre de valeurs distinctes d'une expression pour laquelle une certaine condition est remplie, vous pouvez utiliser cette recette :
COUNT(DISTINCT IF(condition, expression, NULL))
Ici, IF
renvoie la valeur de expression
si condition
est TRUE
, ou NULL
dans le cas contraire. L'élément COUNT(DISTINCT ...)
environnant ignore les valeurs NULL
. Il ne comptabilise donc que les valeurs distinctes de expression
pour lesquelles condition
est TRUE
.
Par exemple, pour compter le nombre de valeurs positives distinctes de x
:
SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;
+-------------------+
| distinct_positive |
+-------------------+
| 3 |
+-------------------+
Ou pour compter le nombre de dates distinctes auxquelles un certain type d'événement s'est produit :
WITH Events AS (
SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
AS distinct_dates_with_failures
FROM Events;
+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2 |
+------------------------------+
NB.SI
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.
Étant donné que expression
doit être un BOOL
, le formulaire COUNTIF(DISTINCT ...)
n'est pas accepté. Cela ne serait pas utile : il n'y a qu'une seule valeur distincte de TRUE
.
Généralement, lorsqu'une personne souhaite combiner COUNTIF
et DISTINCT
, elle souhaite compter le nombre de valeurs distinctes d'une expression pour laquelle une certaine condition est remplie. Pour ce faire, vous pouvez utiliser la recette suivante :
COUNT(DISTINCT IF(condition, expression, NULL))
Notez que cela utilise COUNT
, et non COUNTIF
. la partie IF
a été déplacée à l'intérieur.
Pour en savoir plus, consultez les exemples pour COUNT
.
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
.
Cette fonction accepte la spécification de classement.
Types d'arguments acceptés
Tout type de données pouvant être triées.
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
.
Cette fonction accepte la spécification de classement.
Types d'arguments acceptés
Tout type de données pouvant être triées.
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.
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.
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 deSTRING_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 trisASC
et en dernier dans les trisDESC
. - 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.
- Pour chaque clé de tri, le sens de tri par défaut est
LIMIT
: spécifie le nombre maximal d'entréesexpression
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 limiten
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é et INTERVAL.
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 | INTERVAL |
---|---|---|---|---|---|
RÉSULTAT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 | INTERVAL |
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
.
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. 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
.
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
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
.
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 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 deSTDDEV_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
.
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 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 deSTDDEV_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
.
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 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 deVAR_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
.
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 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 deVAR_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
ouRESPECT NULLS
: siIGNORE NULLS
est spécifié, les valeursNULL
sont exclues du résultat. SiRESPECT NULLS
est spécifié, les valeursNULL
sont incluses dans le résultat. Si aucune valeur n'est spécifiée, les valeursNULL
sont exclues du résultat. Si un tableau du résultat final de la requête contient un élémentNULL
, 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 être initialisé sur le même type. Les tentatives de fusion de résumés pour différents types génèrent 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
.
Si les résumés fusionnés ont été initialisés avec des précisions différentes, la précision sera réduite à la précision la plus basse impliquée dans la fusion.
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é.
Chaque sketch
doit être initialisé sur le même type. Les tentatives de fusion de résumés pour différents types génèrent 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
.
Si les résumés fusionnés ont été initialisés avec des précisions différentes, la précision sera réduite à la précision la plus basse impliquée dans la fusion. Par exemple, si MERGE_PARTIAL
rencontre des esquisses de précision 14 et 15, la nouvelle esquisse renvoyée aura la précision 14.
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, et obtenir un exemple de comparaison de RANK
, DENSE_RANK
et ROW_NUMBER
, consultez la page Concepts des fonctions de numérotation.
Exigences de la clause OVER
:
PARTITION BY
: facultatif.ORDER BY
: obligatoire, sauf pourROW_NUMBER()
.window_frame_clause
: non autorisée.
RANK
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é.
Type renvoyé
INT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
DENSE_RANK
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é.
Type renvoyé
INT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
DENSE_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
PERCENT_RANK
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.
Type renvoyé
FLOAT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
PERCENT_RANK() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+---------------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+---------------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.33333333333333331 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.33333333333333331 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.66666666666666663 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 |
+-----------------+------------------------+----------+---------------------+
CUME_DIST
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.
Type renvoyé
FLOAT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
CUME_DIST() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 0.25 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 0.75 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 0.75 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 1 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 0.25 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 0.5 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 0.75 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 1 |
+-----------------+------------------------+----------+-------------+
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.
Type renvoyé
INT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
NTILE(3) OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 1 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 3 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 1 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 2 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 3 |
+-----------------+------------------------+----------+-------------+
ROW_NUMBER
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.
Type renvoyé
INT64
Exemple
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 2:59:01', 'F30-34')
SELECT name,
finish_time,
division,
ROW_NUMBER() OVER (PARTITION BY division ORDER BY finish_time ASC) AS finish_rank
FROM finishers;
+-----------------+------------------------+----------+-------------+
| name | finish_time | division | finish_rank |
+-----------------+------------------------+----------+-------------+
| Sophia Liu | 2016-10-18 09:51:45+00 | F30-34 | 1 |
| Meghan Lederer | 2016-10-18 09:59:01+00 | F30-34 | 2 |
| Nikki Leith | 2016-10-18 09:59:01+00 | F30-34 | 3 |
| Jen Edwards | 2016-10-18 10:06:36+00 | F30-34 | 4 |
| Lisa Stelzner | 2016-10-18 09:54:11+00 | F35-39 | 1 |
| Lauren Matthews | 2016-10-18 10:01:17+00 | F35-39 | 2 |
| Desiree Berry | 2016-10-18 10:05:42+00 | F35-39 | 3 |
| Suzy Slane | 2016-10-18 10:06:24+00 | F35-39 | 4 |
+-----------------+------------------------+----------+-------------+
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 [format_clause])
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.
Certains castings peuvent inclure une clause de format, qui fournit des instructions sur la façon de procéder au casting. Par exemple, vous pouvez demander à un casting de convertir une séquence d'octets en chaîne encodée en BASE64 au lieu d'une chaîne encodée en UTF-8.
La structure de la clause de format est unique à chaque type de casting et des informations supplémentaires sont disponibles dans la section correspondant à ce casting.
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
STRING
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
STRING
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 [format_clause])
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
STRING
Clause de format
Lorsqu'une expression d'un type est castée dans un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur le casting de ce type. Vous pouvez utiliser la clause de format de cette section si expression
est de type STRING
.
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 [format_clause])
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 :
STRING
TIME
DATETIME
TIMESTAMP
Clause de format
Lorsqu'une expression d'un type est castée dans un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur le casting de ce type. Vous pouvez utiliser la clause de format de cette section si expression
est de type STRING
.
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 [format_clause])
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 :
STRING
TIME
DATETIME
TIMESTAMP
Clause de format
Lorsqu'une expression d'un type est castée dans un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur le casting de ce type. Vous pouvez utiliser la clause de format de cette section si expression
est de type STRING
.
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
STRING
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
STRING
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 INTERVAL
CAST(expression AS INTERVAL)
Description
BigQuery est compatible avec la diffusion vers la valeur INTERVAL. Le paramètre expression
peut représenter une expression pour les types de données suivants :
STRING
Règles de conversion
De | Jusqu'au | Règle(s) appliquée(s) lors du casting de x |
---|---|---|
CHAÎNE | INTERVAL | Lors du casting d'une chaîne en intervalle, la chaîne doit être conforme à la durée ISO 8601 ou au format littéral d'intervalle "YM DH:M:SF". Les formats de littéral d'intervalle partiel sont également acceptés s'ils ne sont pas ambigus, par exemple "H:M:S". Si l'expression de chaîne n'est pas valide ou représente un intervalle en dehors de la plage de valeurs minimale-maximale acceptée, une erreur est générée. |
Exemples
SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
'1-2 3 10:20:30.456',
'1-2',
'10:20:30',
'P1Y2M3D',
'PT10H20M30,456S'
]) input
+--------------------+--------------------+
| input | output |
+--------------------+--------------------+
| 1-2 3 10:20:30.456 | 1-2 3 10:20:30.456 |
| 1-2 | 1-2 0 0:0:0 |
| 10:20:30 | 0-0 0 10:20:30 |
| P1Y2M3D | 1-2 3 0:0:0 |
| PT10H20M30,456S | 0-0 0 10:20:30.456 |
+--------------------+--------------------+
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
STRING
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 [format_clause [AT TIME ZONE timezone_expr]])
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
INTERVAL
STRING
Clause de format
Lorsqu'une expression d'un type est castée vers un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur la façon de procéder au casting.
Vous pouvez utiliser la clause de format de cette section si expression
correspond à l'un des types de données suivants :
INT64
FLOAT64
NUMERIC
BIGNUMERIC
BYTES
TIME
DATE
DATETIME
TIMESTAMP
La clause de format pour STRING
comporte une clause supplémentaire facultative appelée AT TIME ZONE timezone_expr
, que vous pouvez utiliser pour spécifier un fuseau horaire spécifique à utiliser lors du formatage d'un TIMESTAMP
. Si cette clause facultative n'est pas incluse lors de la mise en forme d'un TIMESTAMP
, votre fuseau horaire actuel est utilisé.
Pour plus d'informations, consultez les articles suivants :
- Formater des octets en tant que chaîne
- Formater date et heure en tant que chaîne
- Formater le type numérique en tant que 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. |
INTERVAL | CHAÎNE |
Le casting d'un intervalle en chaîne se présente sous la forme Y-M D H:M:S .
|
Exemples
SELECT CAST(CURRENT_DATE() AS STRING) AS current_date
+---------------+
| current_date |
+---------------+
| 2021-03-09 |
+---------------+
SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day
+-------------+
| current_day |
+-------------+
| MONDAY |
+-------------+
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string
-- Results depend upon where this query was executed.
+------------------------------+
| date_time_to_string |
+------------------------------+
| 2008-12-24 16:00:00 -08:00 |
+------------------------------+
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string
-- Because the time zone is specified, the result is always the same.
+------------------------------+
| date_time_to_string |
+------------------------------+
| 2008-12-25 05:30:00 +05:30 |
+------------------------------+
SELECT CAST(INTERVAL 3 DAY AS STRING) AS interval_to_string
+--------------------+
| interval_to_string |
+--------------------+
| 0-0 3 0:0:0 |
+--------------------+
SELECT CAST(
INTERVAL "1-2 3 4:5:6.789" YEAR TO SECOND
AS STRING) AS interval_to_string
+--------------------+
| interval_to_string |
+--------------------+
| 1-2 3 4:5:6.789 |
+--------------------+
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 :
|
CAST AS TIME
CAST(expression AS TIME [format_clause])
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 :
STRING
TIME
DATETIME
TIMESTAMP
Clause de format
Lorsqu'une expression d'un type est castée dans un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur le casting de ce type. Vous pouvez utiliser la clause de format de cette section si expression
est de type STRING
.
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 [format_clause [AT TIME ZONE timezone_expr]])
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 :
STRING
TIME
DATETIME
TIMESTAMP
Clause de format
Lorsqu'une expression d'un type est castée dans un autre type, vous pouvez utiliser la clause de format pour fournir des instructions sur le casting de ce type. Vous pouvez utiliser la clause de format de cette section si expression
est de type STRING
.
La clause de format pour TIMESTAMP
comporte une clause facultative supplémentaire appelée AT TIME ZONE timezone_expr
, que vous pouvez utiliser pour spécifier un fuseau horaire spécifique à utiliser lors de la mise en forme. Si cette clause facultative n'est pas incluse, votre fuseau horaire actuel est utilisé.
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 dans le fuseau horaire par défaut (UTC).
La plupart des valeurs date/heure valides possèdent exactement un horodatage correspondant dans chaque fuseau horaire. Toutefois, il existe certaines combinaisons de valeurs date/heure valides et de fuseaux horaires qui disposent d'aucune ou de deux valeurs d'horodatage correspondantes. Cela se produit dans un fuseau horaire lorsque l'heure est avancée ou reculée, par exemple lors du passage à l'heure d'été. Lorsqu'il existe deux horodatages valides, le plus ancien est utilisé. En l'absence d'horodatage valide, la durée de l'intervalle de temps (généralement une heure) est ajoutée à la valeur date/heure. |
Exemples
L'exemple suivant convertit un horodatage au format de chaîne sous forme d'horodatage :
SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp
-- Results depend upon where this query was executed.
+-----------------------------+
| as_timestamp |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+
Les exemples suivants castent une date et une heure au format chaîne en tant qu'horodatage. Ces exemples renvoient le même résultat que l'exemple précédent.
SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp
SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp
SELECT CAST('06/02/2020 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp
PARSE_BIGNUMERIC
PARSE_BIGNUMERIC(string_expression)
Description
Convertit une chaîne en une valeur BIGNUMERIC
.
La valeur littérale numérique contenue dans la chaîne ne doit pas dépasser la plage ou la précision maximale du type BIGNUMERIC
, sinon une erreur se produit. Si le nombre de chiffres après le séparateur décimal 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 le séparateur décimal.
-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_BIGNUMERIC("123.45") AS parsed
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
-- This example shows how a string with an exponent is parsed.
SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed
+-----------------------------------------+
| parsed |
+-----------------------------------------+
| 123400000000000000000000000000000000000 |
+-----------------------------------------+
-- This example shows the rounding when digits after the decimal point exceeds 38.
SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed
+------------------------------------------+
| parsed |
+------------------------------------------+
| 1.12345678901234567890123456789012345679 |
+------------------------------------------+
Cette fonction est semblable à l'utilisation de la fonction CAST AS BIGNUMERIC
, à la différence que la fonction PARSE_BIGNUMERIC
n'accepte que les entrées de chaîne et autorise les éléments suivants dans la chaîne :
- Espaces entre le signe (+/-) et le nombre
- Signes (+/-) après le nombre
Règles pour les chaînes d'entrée valides :
Règle | Exemple d'entrée | Résultat |
---|---|---|
La chaîne ne peut contenir que des chiffres, des virgules, des séparateurs décimaux et des signes. | "- 12,34567,89.0" | -123456789 |
Les espaces blancs sont autorisés n'importe où, sauf entre les chiffres. | " - 12.345 " | -12.345 |
Seuls les chiffres et les virgules sont autorisés avant le séparateur décimal. | " 12,345,678" | 12345678 |
Seuls les chiffres sont autorisés après le séparateur décimal. | "1.234 " | 1.234 |
Utilisez E ou e pour les exposants. Après la valeur e , les chiffres et un indicateur de signe initial sont autorisés.
|
" 123.45e-1" | 12.345 |
Si la partie entière n'est pas vide, elle doit contenir au moins un chiffre. | " 0,.12 -" | -0.12 |
Si la chaîne contient un séparateur décimal, elle doit contenir au moins un chiffre. | " .1" | 0,1 |
La chaîne ne peut pas contenir plus d'un signe. | " 0.5 +" | 0,5 |
Type de données renvoyé
BIGNUMERIC
Exemples
Cet exemple montre une entrée avec des espaces avant, après et entre le signe et le nombre :
SELECT PARSE_BIGNUMERIC(" - 12.34 ") as parsed;
+--------+
| parsed |
+--------+
| -12.34 |
+--------+
Cet exemple montre une entrée avec un exposant ainsi que le signe après le nombre :
SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;
+--------+
| parsed |
+--------+
| -1.234 |
+--------+
Cet exemple présente une entrée avec plusieurs virgules dans la partie entière du nombre :
SELECT PARSE_BIGNUMERIC(" 1,2,,3,.45 + ") as parsed;
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
Cet exemple montre une entrée avec un séparateur décimal et aucun chiffre dans la partie entière du nombre :
SELECT PARSE_BIGNUMERIC(".1234 ") as parsed;
+--------+
| parsed |
+--------+
| 0.1234 |
+--------+
Exemples d'entrées non valides
Cet exemple n'est pas valide, car la partie entière du nombre ne contient pas de chiffres :
SELECT PARSE_BIGNUMERIC(",,,.1234 ") as parsed;
Cet exemple n'est pas valide, car il y a des espaces entre les chiffres :
SELECT PARSE_BIGNUMERIC("1 23.4 5 ") as parsed;
Cet exemple n'est pas valide, car le nombre est vide, à l'exception d'un exposant :
SELECT PARSE_BIGNUMERIC(" e1 ") as parsed;
Cet exemple n'est pas valide, car la chaîne contient plusieurs signes :
SELECT PARSE_BIGNUMERIC(" - 12.3 - ") as parsed;
Cet exemple n'est pas valide, car la valeur du nombre se situe en dehors de la plage de BIGNUMERIC
:
SELECT PARSE_BIGNUMERIC("12.34E100 ") as parsed;
Cet exemple n'est pas valide, car la chaîne contient des caractères non valides :
SELECT PARSE_BIGNUMERIC("$12.34") as parsed;
PARSE_NUMERIC
PARSE_NUMERIC(string_expression)
Description
Convertit une chaîne en une valeur NUMERIC
.
La valeur littérale numérique contenue dans la chaîne ne doit pas dépasser la plage ou la précision maximale du type NUMERIC
, sinon une erreur se produit. Si le nombre de chiffres après la virgule est supérieur à neuf, la valeur NUMERIC
résultante est arrondie à la valeur la plus éloignée à mi-chemin du zéro de sorte qu'elle comporte neuf chiffres après la virgule.
-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_NUMERIC("123.45") AS parsed
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
-- This example shows how a string with an exponent is parsed.
SELECT PARSE_NUMERIC("12.34E27") as parsed
+-------------------------------+
| parsed |
+-------------------------------+
| 12340000000000000000000000000 |
+-------------------------------+
-- This example shows the rounding when digits after the decimal point exceeds 9.
SELECT PARSE_NUMERIC("1.0123456789") as parsed
+-------------+
| parsed |
+-------------+
| 1.012345679 |
+-------------+
Cette fonction est semblable à l'utilisation de la fonction CAST AS NUMERIC
, à la différence que la fonction PARSE_NUMERIC
n'accepte que les entrées de chaîne et autorise ce qui suit :
- Espaces entre le signe (+/-) et le nombre
- Signes (+/-) après le nombre
Règles pour les chaînes d'entrée valides :
Règle | Exemple d'entrée | Résultat |
---|---|---|
La chaîne ne peut contenir que des chiffres, des virgules, des séparateurs décimaux et des signes. | "- 12,34567,89.0" | -123456789 |
Les espaces blancs sont autorisés n'importe où, sauf entre les chiffres. | " - 12.345 " | -12.345 |
Seuls les chiffres et les virgules sont autorisés avant le séparateur décimal. | " 12,345,678" | 12345678 |
Seuls les chiffres sont autorisés après le séparateur décimal. | "1.234 " | 1.234 |
Utilisez E ou e pour les exposants. Après la valeur e , les chiffres et un indicateur de signe initial sont autorisés.
|
" 123.45e-1" | 12.345 |
Si la partie entière n'est pas vide, elle doit contenir au moins un chiffre. | " 0,.12 -" | -0.12 |
Si la chaîne contient un séparateur décimal, elle doit contenir au moins un chiffre. | " .1" | 0,1 |
La chaîne ne peut pas contenir plus d'un signe. | " 0.5 +" | 0,5 |
Type de données renvoyé
NUMERIC
Exemples
Cet exemple montre une entrée avec des espaces avant, après et entre le signe et le nombre :
SELECT PARSE_NUMERIC(" - 12.34 ") as parsed;
+--------+
| parsed |
+--------+
| -12.34 |
+--------+
Cet exemple montre une entrée avec un exposant ainsi que le signe après le nombre :
SELECT PARSE_NUMERIC("12.34e-1-") as parsed;
+--------+
| parsed |
+--------+
| -1.234 |
+--------+
Cet exemple présente une entrée avec plusieurs virgules dans la partie entière du nombre :
SELECT PARSE_NUMERIC(" 1,2,,3,.45 + ") as parsed;
+--------+
| parsed |
+--------+
| 123.45 |
+--------+
Cet exemple montre une entrée avec un séparateur décimal et aucun chiffre dans la partie entière du nombre :
SELECT PARSE_NUMERIC(".1234 ") as parsed;
+--------+
| parsed |
+--------+
| 0.1234 |
+--------+
Exemples d'entrées non valides
Cet exemple n'est pas valide, car la partie entière du nombre ne contient pas de chiffres :
SELECT PARSE_NUMERIC(",,,.1234 ") as parsed;
Cet exemple n'est pas valide, car il y a des espaces entre les chiffres :
SELECT PARSE_NUMERIC("1 23.4 5 ") as parsed;
Cet exemple n'est pas valide, car le nombre est vide, à l'exception d'un exposant :
SELECT PARSE_NUMERIC(" e1 ") as parsed;
Cet exemple n'est pas valide, car la chaîne contient plusieurs signes :
SELECT PARSE_NUMERIC(" - 12.3 - ") as parsed;
Cet exemple n'est pas valide, car la valeur du nombre se situe en dehors de la plage de BIGNUMERIC
:
SELECT PARSE_NUMERIC("12.34E100 ") as parsed;
Cet exemple n'est pas valide, car la chaîne contient des caractères non valides :
SELECT PARSE_NUMERIC("$12.34") as parsed;
SAFE_CAST
SAFE_CAST(expression AS typename [format_clause])
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 | STRING |
BOOL | JSON | BOOL |
DATE | Différents types de données | DATE |
DATETIME | Différents types de données | DATETIME |
FLOAT64 | JSON | FLOAT64 |
FROM_BASE32 | CHAÎNE | BYTES |
FROM_BASE64 | CHAÎNE | BYTES |
FROM_HEX | CHAÎNE | BYTES |
INT64 | JSON | INT64 |
PARSE_DATE | CHAÎNE | DATE |
PARSE_DATETIME | CHAÎNE | DATETIME |
PARSE_JSON | CHAÎNE | JSON |
PARSE_TIME | CHAÎNE | TIME |
PARSE_TIMESTAMP | CHAÎNE | TIMESTAMP |
SAFE_CONVERT_BYTES_TO_STRING | BYTES | CHAÎNE |
STRING | TIMESTAMP | STRING |
STRING | JSON | 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 |
TO_JSON | Tous les types de données | JSON |
TO_JSON_STRING | Tous les types de données | CHAÎNE |
Clause de format pour CAST
format_clause:
FORMAT format_model
format_model:
format_string_expression
La clause de format peut être utilisée dans certaines fonctions CAST
. Vous utilisez une clause de format pour fournir des instructions sur la façon de procéder à un casting. Par exemple, vous pouvez demander à un casting de convertir une séquence d'octets en chaîne encodée en BASE64 au lieu d'une chaîne encodée en UTF-8.
La clause de format inclut un modèle de format. Le modèle de format peut contenir des éléments de format combinés en tant que chaîne de format.
Formater des octets sous forme de chaîne
CAST(bytes_expression AS STRING FORMAT format_string_expression)
Vous pouvez caster une séquence d'octets vers une chaîne en ajoutant un élément de format à la chaîne. Si les octets ne peuvent pas être mis en forme avec un élément de format, une erreur est renvoyée. Si la séquence d'octets est NULL
, le résultat est NULL
. Les éléments de format ne sont pas sensibles à la casse.
Élément de format | Renvoie | Exemple |
---|---|---|
HEX | Convertit une séquence de valeurs de type BYTES en une valeur de type STRING hexadécimale. |
Entrée : b'\x00\x01\xEF\xFF' Sortie : 0001efff |
BASEX | Convertit une séquence de valeurs de type BYTES en une chaîne encodée BASEX. X représente l'un des nombres suivants : 2, 8, 16, 32, 64. |
Entrée en tant que BASE8 : b'\x02\x11\x3B' Sortie : 00410473 |
BASE64M | Convertit une séquence d'octets en une chaîne encodée en BASE64 basée sur la norme rfc 2045 pour MIME. Génère un caractère de nouvelle ligne ("\n") tous les 76 caractères. |
Entrée : b'\xde\xad\xbe\xef' Résultat : 3q2+7w== |
ASCII | Convertit une séquence d'octets de valeur ASCII en chaîne. Si l'entrée contient des octets qui ne sont pas un encodage ASCII valide, une erreur est renvoyée. |
Entrée : b'\x48\x65\x6c\x6c\x6f' Résultat : Hello |
UTF-8 | Convertit une séquence d'octets qui sont des valeurs UTF-8 en une chaîne. Si l'entrée contient des octets qui ne sont pas un encodage UTF-8 valide, une erreur est renvoyée. |
Entrée : b'\x24' Résultat : $ |
UTF8 | Comportement identique à la norme UTF-8. |
Type renvoyé
STRING
Exemple
SELECT CAST(b'\x48\x65\x6c\x6c\x6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;
+-----------------+
| bytes_to_string |
+-----------------+
| Hello |
+-----------------+
Mettre en forme la chaîne en octets
CAST(string_expression AS BYTES FORMAT format_string_expression)
Vous pouvez convertir une chaîne en octets à l'aide d'un élément de format dans la chaîne de format. Si la chaîne ne peut pas être mise en forme avec l'élément de format, une erreur est renvoyée. Les éléments de format ne sont pas sensibles à la casse.
Dans l'expression de chaîne, les caractères d'espacement, tels que \n
, sont ignorés si l'élément de format BASE64
ou BASE64M
est utilisé.
Élément de format | Renvoie | Exemple |
---|---|---|
HEX | Convertit une chaîne encodée en hexadécimal en octets. Si l'entrée contient des caractères qui ne font pas partie de l'alphabet d'encodage HEX (0~9, non sensible à la casse a~f), une erreur est renvoyée. |
Entrée : "0001efff" Résultat : b'\x00\x01\xEF\xFF' |
BASEX | Convertit une chaîne encodée en BASEX en octets. X représente l'un des nombres suivants : 2, 8, 16, 32, 64. Une erreur est renvoyée si l'entrée contient des caractères qui ne font pas partie de l'alphabet d'encodage BASEX, à l'exception des caractères d'espacement si l'élément de format est BASE64. |
Entrée en BASE8 : '00410473' Sortie : b'\x02\x11\x3B' |
BASE64M | Convertit une chaîne encodée en BASE64 en octets. Si l'entrée contient des caractères qui ne sont pas des espaces blancs et qui ne font pas partie de l'alphabet d'encodage BASE64 défini dans la RFC 2045, une erreur est renvoyée. Le décodage BASE64M et BASE64 ont le même comportement. |
Entrée : '3q2+7w==' Sortie : b'\xde\xad\xbe\xef' |
ASCII | Convertit une chaîne ne contenant que des caractères ASCII en octets. Si l'entrée contient des caractères autres que des caractères ASCII, une erreur est renvoyée. |
Entrée : 'Hello' Sortie : b'\x48\x65\x6c\x6c\x6f' |
UTF-8 | Convertit une chaîne en une séquence d'octets UTF-8. |
Entrée : '$' Résultat : b'\x24' |
UTF8 | Comportement identique à la norme UTF-8. |
Type renvoyé
BYTES
Exemple
SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes
+-------------------------+
| string_to_bytes |
+-------------------------+
| b'\x48\x65\x6c\x6c\x6f' |
+-------------------------+
Mettre en forme la date et l'heure sous forme de chaîne
Vous pouvez mettre en forme ces parties de date et heure sous forme de chaîne :
- Formater la partie de l'année sous forme de chaîne
- Formater la partie du mois en tant que chaîne
- Mettre en forme la partie jour sous forme de chaîne
- Formater la partie "heure" sous forme de chaîne
- Formater la partie minute en chaîne
- Formater la partie seconde en tant que chaîne
- Formater l'indicateur méridien sous forme de chaîne
- Formater le fuseau horaire en tant que chaîne
- Formater le littéral en tant que chaîne
La correspondance de casse est prise en charge lorsque vous formatez des parties de date ou d'heure sous forme de chaîne et que la sortie contient des lettres. Pour en savoir plus, consultez la section Correspondance de casse.
Correspondance des casses
Lorsque la sortie d'un élément de format contient des lettres, la casse des lettres de la sortie sont mises en correspondance avec celles de l'élément de format, ce qui signifie que les mots du résultat comportent des majuscules en fonction des majuscules comprises dans l'élément de format. C'est ce qu'on appelle la correspondance de casse. Les règles sont les suivantes :
- Si les deux premières lettres de l'élément sont en majuscules, les mots du résultat sont en majuscules. Exemple :
DAY
=THURSDAY
. - Si la première lettre de l'élément est en majuscules et la deuxième en minuscules, la première lettre de chaque mot du résultat est en majuscule et les autres lettres sont en minuscules. Exemple :
Day
=Thursday
. - Si la première lettre de l'élément est en minuscule, toutes les lettres de la sortie sont en minuscules. Exemple :
day
=thursday
.
Mettre en forme la partie de l'année sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caste un type de données contenant la partie année en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec l'année que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format d'année.
Ces types de données incluent une partie année :
DATE
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
AAAA | Année, quatre chiffres ou plus. |
Entrée : DATE '2018-01-30' Sortie : 2018 Entrée : DATE '76-01-30' Sortie : 0076 Entrée : DATE '10000-01-30' Sortie : 10000 |
YYY | Année, trois derniers chiffres uniquement. |
Entrée : DATE '2018-01-30' Sortie : 018 Entrée : DATE '98-01-30' Sortie : 098 |
YY | Année, les deux derniers chiffres uniquement. |
Entrée : DATE '2018-01-30' Sortie : 18 Entrée : DATE '8-01-30' Sortie : 08 |
O | Année, dernier chiffre uniquement. | Entrée : DATE '2018-01-30' Sortie : 8 |
RRRR | Comportement identique à AAAA. | |
RR | Même comportement que AA. |
Type renvoyé
STRING
Exemple
SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'YYYY') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 2018 |
+---------------------+
Formater la partie du mois en tant que chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caste un type de données contenant la partie mois en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec le mois que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de mois.
Les types de données suivants incluent une partie mois :
DATE
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
MM | mois/2 chiffres | Entrée : DATE '2018-01-30' Résultat : 01 |
LUN | Nom abrégé du mois, composé de trois caractères. Les noms de mois abrégés pour les paramètres régionaux en-US sont les suivants : JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC. La correspondance de casse est compatible. |
Entrée : DATE '2018-01-30' Sortie : JAN |
MOIS | Nom du mois. La mise en correspondance de la casse est compatible. |
Entrée : DATE '2018-01-30' Résultat : JANUARY |
Type renvoyé
STRING
Exemple
SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'MONTH') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| JANUARY |
+---------------------+
Mettre en forme la partie jour sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caste un type de données contenant la partie journée en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec le jour que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format de jour.
Ces types de données incluent une partie jour :
DATE
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
JOUR | Nom du jour de la semaine, localisé. Les espaces sont complétés du côté droit pour que la taille de sortie soit exactement 9. La mise en correspondance de la casse est compatible. |
Entrée : DATE '2020-12-31' Sortie : THURSDAY |
DY | Nom abrégé, composé de trois caractères du jour de la semaine, localisé. Les noms abrégés des jours de la semaine pour les paramètres régionaux en-US sont : MON, TUE, WED, THU, FRI, SAT et SUN. La mise en correspondance de la casse est compatible. |
Entrée : DATE '2020-12-31' Sortie : THU |
D | Jour de la semaine (1 à 7), avec le dimanche comme 1. | Entrée : DATE '2020-12-31' Résultat : 4 |
DD | Jour du mois sur deux chiffres. | Entrée : DATE '2018-12-02' Résultat : 02 |
DDD | Jour à trois chiffres de l'année. | Entrée : DATE '2018-02-03' Résultat : 034 |
Type renvoyé
STRING
Exemple
SELECT CAST(DATE '2018-02-15' AS STRING FORMAT 'DD') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 15 |
+---------------------+
Formater la partie heure sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Convertit un type de données contenant la partie heure en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec l'heure que vous devez formater.format_string_expression
: chaîne contenant les éléments de format, y compris l'élément de format horaire.
Ces types de données incluent une partie d'heure :
TIME
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
HH | Heure du jour, format 12 heures, 2 chiffres. |
Entrée : TIME '21:30:00' Sortie : 09 |
HH12 | Heure du jour, format 12 heures |
Entrée : TIME '21:30:00' Sortie : 09 |
HH24 | Heure du jour, horloge au format 24 heures, 2 chiffres. |
Entrée : TIME '21:30:00' Sortie : 21 |
Type renvoyé
STRING
Exemples
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH24') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 21 |
+---------------------+
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH12') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 09 |
+---------------------+
Mettre en forme l'élément de la minute sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caste un type de données contenant la partie minute en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec la minute que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format minute.
Ces types de données incluent une partie minute :
TIME
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
MI | minute/2 chiffres |
Entrée : TIME '01:02:03' Sortie : 02 |
Type renvoyé
STRING
Exemple
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'MI') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 30 |
+---------------------+
Mettre en forme la partie seconde sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caste un type de données contenant la partie seconde en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec la partie seconde que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format seconde.
Ces types de données incluent une partie "seconde" :
TIME
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
SS | Secondes de la minute, deux chiffres. |
Entrée : TIME '01:02:03' Sortie : 03 |
SSSSS | Secondes du jour, cinq chiffres. |
Entrée : TIME '01:02:03' Sortie : 03723 |
FFn |
Partie fractionnaire de la seconde, n chiffres.
Remplacez n par une valeur comprise entre 1 et 9. Par exemple, "FF5".
La partie fractionnaire de la seconde est arrondie pour s'adapter à la taille de la sortie.
|
Entrée pour FF1 : TIME '01:05:07.16' Sortie : 1 Entrée pour FF2 : TIME '01:05:07.16' Sortie : 16 Entrée pour FF3 : TIME '01:05:07.16' Sortie : 016 |
Type renvoyé
STRING
Exemples
SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'SS') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 25 |
+---------------------+
SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'FF2') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| 16 |
+---------------------+
Mettre en forme la partie de l'indicateur méridien sous forme de chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Convertit un type de données qui contient l'indicateur méridien en chaîne. Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec l'indicateur méridien que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format indicateur méridien.
Ces types de données incluent une partie indicateur méridien :
TIME
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
A.M. | AM si le temps est inférieur à 12, dans le cas contraire P.M., la casse de la sortie est déterminée par la première lettre de l'élément de format. |
Entrée pour AM : TIME '01:02:03' Sortie : AM Entrée A.M. : TIME '16:02:03' Sortie : P.M. Entrée pour a.m. : TIME '01:02:03' Sortie : a.m. Entrée pour a.M. : TIME '01:02:03' Sortie : a.m. |
a.m. | AM si l'heure est inférieure à 12, dans le cas contraire, P.M. La casse des lettres de la sortie est déterminée par la première lettre de l'élément de format. |
Entrée pour AM : TIME '01:02:03' Sortie : AM Entrée pour AM : TIME '16:02:03' Sortie : PM Entrée pour am : TIME '01:02:03' Sortie : am Entrée pour aM : TIME '01:02:03' Sortie : am |
PM | Le résultat est identique à l'élément de format AM. | |
p.m. | Le résultat est identique à l'élément de format AM. |
Type renvoyé
STRING
Exemples
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| PM |
+---------------------+
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;
+---------------------+
| date_time_to_string |
+---------------------+
| AM |
+---------------------+
Mettre en forme la partie du fuseau horaire en tant que chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Caster un type de données contenant la partie "Fuseau horaire" en chaîne Inclut des éléments de format, qui fournissent des instructions pour mener le casting.
expression
: cette expression contient le type de données avec le fuseau horaire que vous devez formater.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format de fuseau horaire.
Ces types de données incluent une partie de fuseau horaire :
DATE
TIME
DATETIME
TIMESTAMP
Si expression
ou format_string_expression
est NULL
, la valeur renvoyée est NULL
. Si format_string_expression
est une chaîne vide, le résultat est une chaîne vide. Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
TZH |
Décalage horaire (heure) pour un fuseau horaire. Cela inclut le signe +/- et l'heure à deux chiffres.
|
Inputstamp : TIMESTAMP '2008-12-25 05:30:00+00' Résultat : −08 |
TZM | Décalage horaire (minute) pour un fuseau horaire. Cela n'inclut que la minute à deux chiffres. | Inputstamp : TIMESTAMP '2008-12-25 05:30:00+00' Résultat : 00 |
Type renvoyé
STRING
Exemples
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH') AS date_time_to_string;
-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| -08 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;
-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| +05 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM') AS date_time_to_string;
-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| 00 |
+---------------------+
SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;
-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| 30 |
+---------------------+
Mettre en forme le littéral en tant que chaîne
CAST(expression AS STRING FORMAT format_string_expression)
Élément de format | Renvoie | Exemple |
---|---|---|
- | Le résultat est identique à l'entrée. | - |
. | Le résultat est identique à l'entrée. | . |
/ | Le résultat est identique à l'entrée. | / |
, | Le résultat est identique à l'entrée. | , |
' | Le résultat est identique à l'entrée. | ' |
; | Le résultat est identique à l'entrée. | ; |
: | Le résultat est identique à l'entrée. | : |
Espace blanc | Le résultat est identique à l'entrée. Les espaces blancs correspondent au caractère d'espacement, ASCII 32. Il ne s'agit pas d'autres types d'espaces, tels que des tabulations ou des nouvelles lignes. Tout caractère d'espace blanc autre que le caractère ASCII 32 dans le modèle de format génère une erreur. | |
"texte" |
La sortie correspond à la valeur entre guillemets doubles. Pour conserver un caractère de guillemet double ou de barre oblique inverse, utilisez la séquence d'échappement \" ou \\ . Les autres séquences d'échappement ne sont pas acceptées.
|
Entrée : "abc" Sortie : abc Entrée : "a\"b\\c" Sortie : a"b\c |
Mettre en forme la chaîne en tant que date et heure
Vous pouvez mettre en forme une chaîne avec les parties de date et heure suivantes :
- Mettre en forme la chaîne en tant que partie d'année
- Mettre en forme la chaîne en tant que partie de mois
- Formater la chaîne comme partie jour
- Mettre en forme la chaîne en tant que partie heure
- Mettre en forme la chaîne en tant que partie minute
- Formater la chaîne comme partie "seconde"
- Formater la chaîne comme partie d'indicateur méridien
- Mettre en forme la chaîne en tant que partie du fuseau horaire
- Formater une chaîne en tant que partie de littéral
Lorsque vous mettez en forme une chaîne avec des parties de date et heure, vous devez suivre les règles de modèle de mise en forme.
Règles de format de modèle
Lorsque vous castez une chaîne en parties de date et d'heure, vous devez vous assurer que le modèle de format est valide. Le modèle de format représente les éléments transmis à CAST(string_expression AS type FORMAT format_string_expression)
en tant que format_string_expression
et est validé conformément aux règles suivantes :
- Il contient au maximum l'un des éléments suivants : indicateur méridien, année, mois, jour, heure.
- Un élément de format non littéral et non blanc ne peut pas apparaître plus d'une fois.
- Si il contient l'élément de format de jour de l'année,
DDD
, il ne peut pas contenir de mois. - S'il contient l'élément de format 24 heures,
HH24
, il ne peut pas contenir l'élément de format 12 heures ni un indicateur méridien. - S'il contient l'élément de format 12 heures,
HH12
ouHH
, il doit également contenir un indicateur méridien. - S'il contient un indicateur méridien, il doit également contenir un élément de format 12 heures.
- S'il contient l'élément de format de seconde de jour,
SSSSS
, il ne peut contenir aucun des éléments suivants : heure, minute, seconde ou indicateur méridien. - Il ne peut pas contenir d'élément de format tel que la valeur qu'il définit n'existe pas dans le type cible. Par exemple, un élément de format horaire tel que
HH24
ne peut pas apparaître dans une chaîne que vous castez en tant queDATE
.
Mettre en forme la chaîne en tant que partie d'année
CAST(string_expression AS type FORMAT format_string_expression)
Caste une année au format de chaîne en un type de données contenant la partie année. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne avec l'année que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie année.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format d'année. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie année :
DATE
DATETIME
TIMESTAMP
Si la partie YEAR
est manquante dans string_expression
et que le type renvoyé inclut cette partie, YEAR
est défini sur l'année en cours.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
AAAA | Si le code est délimité, il correspond à 1 à 5 chiffres. S'il n'est pas délimité, correspond à 4 chiffres. Définit la partie année sur le nombre correspondant. |
Entrée pour MM-DD-YYYY : '03-12-2018' Sortie en tant que DATE : 2018-12-03 Entrée pour YYYY-MMDD : '10000-1203' Sortie en tant que DATE : 10000-12-03 Entrée pour YYYY : '18' Sortie en tant que DATE : 2018-03-01 (supposons que la date actuelle est le 23 mars 2021) |
YYY | Correspond à 3 chiffres. Définit les trois derniers chiffres de la partie de l'année sur le nombre correspondant. |
Entrée pour YYY-MM-DD : '018-12-03' Sortie en tant que DATE : 2018-12-03 Entrée pour YYY-MM-DD : '038-12-03' Sortie en tant que DATE : 2038-12-03 |
YY | Correspond à 2 chiffres. Définit les deux derniers chiffres de la partie de l'année sur le nombre correspondant. |
Entrée pour YY-MM-DD : '18-12-03' Sortie en tant que DATE : 2018-12-03 Entrée pour YY-MM-DD : '38-12-03' Sortie en tant que DATE : 2038-12-03 |
O | Correspond à 1 chiffre. Définit le dernier chiffre de la partie de l'année sur le nombre correspondant. |
Entrée pour Y-MM-DD : '8-12-03' Sortie en tant que DATE : 2008-12-03 |
Y,YYY | Correspond au modèle de 1 à 2 chiffres, à la virgule, puis exactement à 3 chiffres. Définit la partie année sur le nombre correspondant. |
Entrée pour Y,YYY-MM-DD : '2,018-12-03' Sortie en tant que : 2008-12-03 |
RRRR | Comportement identique à AAAA. | |
RR |
Correspond à 2 chiffres. Si les deux chiffres saisis sont compris entre 00 et 49 et que les deux derniers chiffres de l'année en cours sont compris entre 00 et 49, l'année renvoyée comporte les deux premiers chiffres de l'année en cours. Si les deux derniers chiffres de l'année en cours sont compris entre 50 et 99, les 2 premiers chiffres de l'année renvoyée sont supérieurs aux 2 premiers chiffres de l'année en cours d'une valeur de un. Si les deux chiffres saisis sont compris entre 50 et 99, et que les deux derniers chiffres de l'année en cours sont compris entre 00 et 49, les deux premiers chiffres de l'année renvoyée sont inférieurs aux deux premiers chiffres de l'année en cours d'une valeur de un. Si les deux derniers chiffres de l'année en cours sont compris entre 50 et 99, l'année renvoyée comporte les mêmes deux premiers chiffres que l'année en cours. |
Entrée pour RR-MM-DD : '18-12-03' Sortie en tant que DATE : 2018-12-03 (exécuté en 2021) Sortie en tant que DATE : 2118-12-03 (exécuté en 2050) Entrée pour RR-MM-DD : '50-12-03' Sortie en tant que DATE : 2050-12-03 (exécuté en 2021) Sortie en tant que DATE : 2050-12-03 (exécuté en 2050) |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
DATE
DATETIME
TIMESTAMP
Exemples
SELECT CAST('18-12-03' AS DATE FORMAT 'YY-MM-DD') AS string_to_date
+----------------+
| string_to_date |
+----------------+
| 2018-02-03 |
+----------------+
Mettre en forme la chaîne en tant que partie de mois
CAST(string_expression AS type FORMAT format_string_expression)
Caste un mois au format de chaîne en un type de données contenant la partie mois. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne contenant le mois que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Elle doit inclure la partie mois.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de mois. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Les types de données suivants incluent une partie mois :
DATE
DATETIME
TIMESTAMP
Si la partie MONTH
est manquante dans string_expression
et que le type renvoyé inclut cette partie, MONTH
est défini sur le mois en cours.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
MM | Correspond à 2 chiffres. Définit la partie "mois" sur le nombre correspondant. |
Entrée pour MM-JJ-AAAA : '03-12-2018' Sortie en tant que DATE : 2018-12-03 |
LUN | Correspond à trois lettres. Définit la partie du mois sur la chaîne correspondante interprétée comme le nom abrégé du mois. |
Entrée pour MON JJ, AAAA : 'DEC 03 2018' Sortie en tant que DATE : 2018-12-03 |
MOIS | Correspond à 9 lettres. Définit la partie du mois sur la chaîne correspondante interprétée comme le nom du mois. |
Entrée pour MONTH DD, AAAA : 'DECEMBER 03, 2018' Sortie en tant que DATE : 2018-12-03 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
DATE
DATETIME
TIMESTAMP
Exemples
SELECT CAST('DEC 03, 2018' AS DATE FORMAT 'MON DD, YYYY') AS string_to_date
+----------------+
| string_to_date |
+----------------+
| 2018-12-03 |
+----------------+
Mettre en forme la chaîne comme partie jour
CAST(string_expression AS type FORMAT format_string_expression)
Caste un jour au format de chaîne en un type de données contenant la partie jour. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne contenant le jour que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie jour.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format de jour. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie jour :
DATE
DATETIME
TIMESTAMP
Si la partie DAY
est manquante dans string_expression
et que le type renvoyé inclut cette partie, DAY
est défini sur 1
.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
DD | Correspond à 2 chiffres. Définit la partie "jour" du nombre correspondant. |
Entrée pour MONTH DD, AAAA : 'DECEMBER 03, 2018' Sortie en tant que DATE : 2018-12-03 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
DATE
DATETIME
TIMESTAMP
Exemples
SELECT CAST('DECEMBER 03, 2018' AS DATE FORMAT 'MONTH DD, YYYY') AS string_to_date
+----------------+
| string_to_date |
+----------------+
| 2018-12-03 |
+----------------+
Mettre en forme la chaîne en tant que partie heure
CAST(string_expression AS type FORMAT format_string_expression)
Caste une heure au format de chaîne en un type de données contenant la partie heure. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne contenant l'heure que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie heure.format_string_expression
: chaîne contenant les éléments de format, y compris l'élément de format horaire. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie d'heure :
TIME
DATETIME
TIMESTAMP
Si la partie HOUR
est manquante dans string_expression
et que le type renvoyé inclut cette partie, HOUR
est défini sur 0
.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
HH |
Correspond à 2 chiffres. Si le nombre correspondant n est 12 , définit temp = 0 . Sinon, définit temp = n . Si la valeur correspondante de l'élément de format AM/PM est PM, définit temp = n + 12 . Définit la partie heure sur temp .
Un indicateur méridien doit être présent dans le modèle de format, en présence de la valeur HH.
|
Entrée pour HH:MI PM : '03:30 P.M.' Sortie au format TIME : 15:30:00 |
HH12 | Même comportement que HH. | |
HH24 | Correspond à 2 chiffres. Définit la partie heure sur le nombre correspondant. |
Entrée pour HH24:MI : '15:30' Sortie au format TIME : 15:30:00 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
TIME
DATETIME
TIMESTAMP
Exemples
SELECT CAST('15:30' AS TIME FORMAT 'HH24:MI') AS string_to_date_time
+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00 |
+---------------------+
Mettre en forme la chaîne en partie minute
CAST(string_expression AS type FORMAT format_string_expression)
Caste une minute au format de chaîne dans un type de données contenant la partie minute. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne avec la minute que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie minute.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format minute. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie minute :
TIME
DATETIME
TIMESTAMP
Si la partie MINUTE
est manquante dans string_expression
et que le type renvoyé inclut cette partie, MINUTE
est défini sur 0
.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
MI | Correspond à 2 chiffres. Définit la partie de minutes sur le nombre correspondant. |
Entrée pour HH:MI PM : '03:30 P.M.' Sortie au format TIME : 15:30:00 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
TIME
DATETIME
TIMESTAMP
Exemples
SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI P.M.') AS string_to_date_time
+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00 |
+---------------------+
Mettre en forme la chaîne en partie seconde
CAST(string_expression AS type FORMAT format_string_expression)
Caste une seconde au format de chaîne en un type de données contenant la partie seconde. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne avec la seconde que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie seconde.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format seconde. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie "seconde" :
TIME
DATETIME
TIMESTAMP
Si la partie SECOND
est manquante dans string_expression
et que le type renvoyé inclut cette partie, SECOND
est défini sur 0
.
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
SS | Correspond à 2 chiffres. Définit la partie "seconde" sur le nombre correspondant. |
Entrée pour HH:MI:SS PM : '03:30:02 P.M.' Sortie au format TIME : 15:30:02 |
SSSSS | Correspond à 5 chiffres. Définit les parties heure, minute et seconde en interprétant le nombre correspondant comme le nombre de secondes écoulées après minuit. |
Entrée pour SSSSS : "03723" Sortie en tant que TIME : 01:02:03 |
FFn |
Correspond à n chiffres, où n est le nombre qui suit FF dans l'élément de format. Définit la partie fractionnaire de la partie "seconde" sur le nombre correspondant.
|
Entrée pour HH24:MI:SS.FF1 : '01:05:07.16' Sortie en tant que TIME : 01:05:07.2 Entrée pour HH24:MI:SS.FF2 : '01:05:07.16' Sortie en tant que TIME : 01:05:07.16 Entrée pour HH24:MI:SS.FF3 : 'FF3: 01:05:07.16' Sortie en tant que TIME : 01:05:07.160 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
TIME
DATETIME
TIMESTAMP
Exemples
SELECT CAST('01:05:07.16' AS TIME FORMAT 'HH24:MI:SS.FF1') AS string_to_date_time
+---------------------+
| string_to_date_time |
+---------------------+
| 01:05:07.2 |
+---------------------+
Mettre en forme la chaîne en partie indicateur méridien
CAST(string_expression AS type FORMAT format_string_expression)
Caste un indicateur méridien au format de chaîne en un type de données contenant la partie indicateur méridien. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne avec l'indicateur méridien que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Elle doit inclure la partie indicateur méridien.format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format indicateur méridien. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie indicateur méridien :
TIME
DATETIME
TIMESTAMP
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
AM ou PM |
Correspond à l'expression régulière '(A|P)\.M\.' .
|
Entrée pour HH:MI AM : '03:30 A.M.' Sortie au format TIME : 03:30:00 Entrée pour HH:MI PM : '03:30 P.M.' Sortie en tant que TIME : 15:30:00 Entrée pour HH:MI P.M. : '03:30 A.M.' Sortie en tant que TIME : 03:30:00 Entrée pour HH:MI A.M. : '03:30 P.M.' Sortie en tant que TIME : 15:30:00 Entrée pour HH:MI a.m. : '03:30 a.m.' Sortie en tant que TIME : 03:30:00 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
TIME
DATETIME
TIMESTAMP
Exemples
SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI A.M.') AS string_to_date_time
+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00 |
+---------------------+
Mettre en forme la chaîne en tant que partie du fuseau horaire
CAST(string_expression AS type FORMAT format_string_expression)
Caste un fuseau horaire au format de chaîne en un type de données contenant la partie de fuseau horaire. Inclut des éléments de format, qui fournissent des instructions sur la façon de procéder au casting.
string_expression
: cette expression contient la chaîne contenant le fuseau horaire que vous devez formater.type
: type de données vers lequel vous effectuez un casting. Doit inclure la partie "fuseau horaire".format_string_expression
: chaîne contenant des éléments de format, y compris l'élément de format de fuseau horaire. Les éléments de format de cette chaîne sont définis collectivement comme le modèle de format, qui doit respecter ces règles.
Ces types de données incluent une partie de fuseau horaire :
DATE
TIME
DATETIME
TIMESTAMP
Une erreur est générée si une valeur qui n'est pas un élément de format compatible apparaît dans format_string_expression
ou string_expression
ne contient pas une valeur spécifiée par un élément de format.
Élément de format | Renvoie | Exemple |
---|---|---|
TZH |
Correspond à l'expression régulière '(\+|\-| )[0-9]{2}' .
Définit le fuseau horaire et les parties d'heure sur le signe et le numéro correspondants.
Définit le signe de fuseau horaire comme étant la première lettre de la chaîne correspondante.
Le nombre 2 correspond à deux chiffres au maximum pour la correspondance non exacte et deux chiffres exactement pour la correspondance exacte.
|
Entrée pour AAAA-MM-JJ HH:MI:SSTZH : '2008-12-25 05:30:00-08' Sortie au format TIMESTAMP : 2008-12-25 05:30:00-08 |
TZM |
Correspond à 2 chiffres. Attribuez la valeur n au numéro correspondant. Si le signe de fuseau horaire est le signe moins, définit la partie de fuseau horaire sur -n . Sinon, définit la partie minute du fuseau horaire sur n .
|
Entrée pour AAAA-MM-JJ HH:MI:SSTZH : '2008-12-25 05:30:00+05.30' Sortie au format TIMESTAMP : 2008-12-25 05:30:00+05.30 |
Type renvoyé
Type de données sur lequel la chaîne a été castée. Il peut s'agir de :
DATE
TIME
DATETIME
TIMESTAMP
Exemples
SELECT CAST('2020.06.03 00:00:53+00' AS TIMESTAMP FORMAT 'YYYY.MM.DD HH:MI:SSTZH') AS string_to_date_time
+-----------------------------+
| as_timestamp |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+
Mettre en forme une chaîne en littéral
CAST(string_expression AS data_type FORMAT format_string_expression)
Élément de format | Renvoie | Exemple |
---|---|---|
- | Le résultat est identique à l'entrée. | |
. | Le résultat est identique à l'entrée. | . |
/ | Le résultat est identique à l'entrée. | / |
, | Le résultat est identique à l'entrée. | , |
' | Le résultat est identique à l'entrée. | ' |
; | Le résultat est identique à l'entrée. | ; |
: | Le résultat est identique à l'entrée. | : |
Espace blanc | Une séquence consécutive d'un ou de plusieurs espaces du modèle de format est mise en correspondance avec un ou plusieurs caractères d'espacement Unicode consécutifs dans l'entrée. L'espace signifie le caractère d'espace ASCII 32. Cela ne signifie pas les espaces généraux, tels qu'une tabulation ou une nouvelle ligne. Tout caractère d'espacement qui n'est pas le caractère ASCII 32 dans le modèle de format génère une erreur. | |
"texte" |
Résultat généré par l'élément de mise en forme en utilisant cette expression régulière et s , représentant l'entrée de chaîne : regex.escape(s) .
|
Entrée : "abc" Sortie : abc Entrée : "a\"b\\c" Sortie : a"b\c |
Mettre en forme le type numérique en tant que chaîne
CAST(numeric_expression AS STRING FORMAT format_string_expression)
Vous pouvez caster un type numérique vers une chaîne en combinant les éléments de format suivants :
À l'exception de l'élément de format d'exposant (EEEE
), tous les éléments de format génèrent un nombre fixe de caractères dans la sortie, et celle-ci est alignée par le point décimal. Le premier caractère génère un -
pour les nombres négatifs, sinon un espace. Pour supprimer des caractères vides et des zéros de fin, utilisez l'option FM
.
Type renvoyé
STRING
Exemple
SELECT input, CAST(input AS STRING FORMAT '$999,999.999') AS output
FROM UNNEST([1.2, 12.3, 123.456, 1234.56, -12345.678, 1234567.89]) AS input
+------------+---------------+
| input | output |
+------------+---------------+
| 1.2 | $1.200 |
| 12.3 | $12.300 |
| 123.456 | $123.456 |
| 1234.56 | $1,234.560 |
| -12345.678 | -$12,345.678 |
| 1234567.89 | $###,###.### |
+------------+---------------+
Mettre en forme les chiffres sous forme de chaîne
Les éléments de format suivants produisent des chiffres. Si les éléments au format numérique ne sont pas suffisants pour représenter l'entrée, tous les éléments de format numérique sont remplacés par #
dans la sortie.
Élément de format | Renvoie | Exemple |
---|---|---|
0 | Un chiffre décimal. Les zéros de début et de fin sont inclus. |
Entrée : 12 Format : '000' Sortie : ' 012'
Entrée : 12 Format : '000.000' Sortie : ' 012.000'
Entrée : -12 Format : '000.000' Sortie : '-012.000'
|
9 | Un chiffre décimal. Les zéros de début sont remplacés par des espaces. Les zéros de fin sont inclus. |
Entrée : 12 Format : '999' Sortie : ' 12'
Entrée : 12 Format : '999.999' Sortie : ' 12.000'
|
X ou x | Un chiffre hexadécimal. Ne peut pas apparaître avec d'autres éléments de format, à l'exception de 0, FM et de l'élément de format de signe. Le nombre maximal de chiffres hexadécimaux compris dans la chaîne de format est de 16. X génère des lettres majuscules et x génère des lettres minuscules. Lorsque 0 est combiné à l'élément de format hexadécimal, la lettre générée par 0 correspond à la casse de l'élément X ou x suivant. S'il n'y a pas de X ou de X ultérieur, 0 génère une lettre majuscule. |
Entrée : 43981 Format : 'XXXX' Sortie : ' ABCD'
Entrée : 43981 Format : 'xxxx' Sortie : ' abcd'
Entrée : 43981 Format : '0X0x' Sortie : ' ABcd'
Entrée : 43981 Format : '0000000X' Sortie : ' 0000ABCD'
|
Type renvoyé
STRING
Exemple
SELECT
CAST(12 AS STRING FORMAT '999') as a,
CAST(-12 AS STRING FORMAT '999') as b;
+------+------+
| a | b |
+------+------+
| 12 | -12 |
+------+------+
Mettre en forme le nombre décimal sous forme de chaîne
Les éléments de format suivants génèrent un séparateur décimal. Ces éléments de format s'excluent mutuellement. Au maximum, un seul peut apparaître dans la chaîne de format.
Élément de format | Renvoie | Exemple |
---|---|---|
. (point) | Séparateur décimal. |
Entrée : 123.58 Format : '999.999' Sortie : ' 123.580'
|
D | Séparateur décimal des paramètres régionaux |