Fonctions et opérateurs de l'ancien SQL
Ce document décrit les fonctions et les opérateurs de l'ancien SQL. La syntaxe de requête privilégiée pour BigQuery est le langage SQL standard de Google. Pour en savoir plus sur le langage SQL standard de Google, consultez la page Fonctions et opérateurs du langage SQL standard de Google.
Fonctions et opérateurs compatibles
La plupart des clauses de l'instruction SELECT
sont compatibles avec les fonctions. Les champs référencés dans une fonction n'ont pas besoin d'être répertoriés dans une clause SELECT
. Par conséquent, la requête suivante est valide, même si le champ clicks
n'est pas directement affiché :
#legacySQL SELECT country, SUM(clicks) FROM table GROUP BY country;
Fonctions d'agrégation | |
---|---|
AVG() |
Renvoie la moyenne des valeurs pour un groupe de lignes. |
BIT_AND() |
Renvoie le résultat d'une opération AND (ET) bit à bit. |
BIT_OR() |
Renvoie le résultat d'une opération OR (OU) bit à bit. |
BIT_XOR() |
Renvoie le résultat d'une opération XOR (OU exclusif) bit à bit. |
CORR() |
Renvoie le coefficient de corrélation de Pearson d'un ensemble de paires de nombres. |
COUNT() |
Renvoie le nombre total de valeurs. |
COUNT([DISTINCT]) |
Renvoie le nombre total de valeurs non nulles. |
COVAR_POP() |
Calcule la covariance de population des valeurs. |
COVAR_SAMP() |
Calcule la covariance d'échantillon des valeurs. |
EXACT_COUNT_DISTINCT() |
Renvoie le nombre exact de valeurs non nulles distinctes pour le champ spécifié. |
FIRST() |
Renvoie la première valeur séquentielle comprise dans le champ d'application de la fonction. |
GROUP_CONCAT() |
Concatène plusieurs chaînes en une seule chaîne. |
GROUP_CONCAT_UNQUOTED() |
Concatène plusieurs chaînes en une seule chaîne. N'ajoute pas de guillemets doubles. |
LAST() |
Renvoie la dernière valeur séquentielle. |
MAX() |
Renvoie la valeur maximale. |
MIN() |
Renvoie la valeur minimale. |
NEST() |
Agrège toutes les valeurs comprises dans le champ d'application actuel de l'agrégation dans un champ répété. |
NTH() |
Renvoie la nième valeur séquentielle. |
QUANTILES() |
Calcule approximativement la valeur minimale, la valeur maximale et les quantiles. |
STDDEV() |
Renvoie l'écart type. |
STDDEV_POP() |
Calcule l'écart type de la population. |
STDDEV_SAMP() |
Calcule l'écart type de l'échantillon. |
SUM() |
Renvoie la somme totale des valeurs. |
TOP() ... COUNT(*) |
Renvoie les enregistrements "max_records" les mieux classés par fréquence. |
UNIQUE() |
Renvoie l'ensemble de valeurs uniques et non nulles. |
VARIANCE() |
Calcule la variance des valeurs. |
VAR_POP() |
Calcule la variance de population des valeurs. |
VAR_SAMP() |
Calcule la variance d'échantillon des valeurs. |
Opérateurs arithmétiques | |
---|---|
+ |
Addition |
- |
Soustraction |
* |
Multiplication |
/ |
Division |
% |
Modulo |
Fonctions bit à bit | |
---|---|
& |
Opérateur AND (ET) bit à bit |
| |
Opérateur OR (OU) bit à bit |
^ |
Opérateur XOR (OU exclusif) bit à bit |
<< |
Décalage à gauche bit à bit |
>> |
Décalage à droite bit à bit |
~ |
Opérateur NOT (PAS) bit à bit |
BIT_COUNT() |
Renvoie le nombre de bits. |
Fonctions de cast | |
---|---|
BOOLEAN() |
Convertit une expression au format booléen. |
BYTES() |
Convertit une expression en octets. |
CAST(expr AS type) |
Convertit expr en une variable de type type . |
FLOAT() |
Convertit une expression en nombre à double précision. |
HEX_STRING() |
Convertit une expression en chaîne hexadécimale. |
INTEGER() |
Convertit une expression en nombre entier. |
STRING() |
Convertit une expression en chaîne. |
Fonctions de comparaison | |
---|---|
expr1 = expr2 |
Renvoie true si la valeur des expressions est égale. |
expr1 != expr2 expr1 <> expr2
|
Renvoie true si la valeur des expressions n'est pas égale. |
expr1 > expr2 |
Renvoie true si la valeur de expr1 est supérieure à expr2 . |
expr1 < expr2 |
Renvoie true si la valeur de expr1 est inférieure à expr2 . |
expr1 >= expr2 |
Renvoie true si la valeur de expr1 est supérieure ou égale à expr2 . |
expr1 <= expr2 |
Renvoie true si la valeur de expr1 est inférieure ou égale à expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Renvoie true si la valeur de expr1 est comprise entre expr2 et expr3 , inclus. |
expr IS NULL |
Renvoie true si la valeur de expr est nulle. |
expr IN() |
Renvoie true si expr correspond à expr1 , expr2 ou à toute valeur entre parenthèses. |
COALESCE() |
Renvoie le premier argument qui n'est pas NULL. |
GREATEST() |
Renvoie le plus grand paramètre numeric_expr . |
IFNULL() |
Renvoie l'argument si celui-ci n'est pas nul. |
IS_INF() |
Renvoie true si la valeur correspond à l'infini positif ou négatif. |
IS_NAN() |
Renvoie true si l'argument est NaN . |
IS_EXPLICITLY_DEFINED() |
obsolète : utilisez plutôt expr IS NOT NULL . |
LEAST() |
Renvoie le plus petit paramètre numeric_expr de l'argument. |
NVL() |
Si expr n'est pas null, renvoie expr , sinon renvoie null_default . |
Fonctions de date et heure | |
---|---|
CURRENT_DATE() |
Renvoie la date actuelle au format %Y-%m-%d . |
CURRENT_TIME() |
Renvoie l'heure actuelle du serveur au format %H:%M:%S . |
CURRENT_TIMESTAMP() |
Renvoie l'heure actuelle du serveur au format %Y-%m-%d %H:%M:%S . |
DATE() |
Renvoie la date au format %Y-%m-%d . |
DATE_ADD() |
Ajoute l'intervalle spécifié à un type de données TIMESTAMP. |
DATEDIFF() |
Renvoie le nombre de jours entre deux types de données TIMESTAMP. |
DAY() |
Renvoie le jour du mois sous la forme d'un entier compris entre 1 et 31. |
DAYOFWEEK() |
Renvoie le jour de la semaine sous la forme d'un entier compris entre 1 (dimanche) et 7 (samedi). |
DAYOFYEAR() |
Renvoie le jour de l'année sous la forme d'un entier compris entre 1 et 366. |
FORMAT_UTC_USEC() |
Renvoie un horodatage UNIX au format YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Renvoie l'heure d'un type de données TIMESTAMP sous la forme d'un entier compris entre 0 et 23. |
MINUTE() |
Renvoie les minutes d'un type de données TIMESTAMP sous la forme d'un entier compris entre 0 et 59. |
MONTH() |
Renvoie le mois d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 12. |
MSEC_TO_TIMESTAMP() |
Convertit un horodatage UNIX exprimé en millisecondes en type de données TIMESTAMP. |
NOW() |
Renvoie l'horodatage UNIX actuel en microsecondes. |
PARSE_UTC_USEC() |
Convertit une chaîne de date en horodatage UNIX exprimé en microsecondes. |
QUARTER() |
Renvoie le trimestre d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 4. |
SEC_TO_TIMESTAMP() |
Convertit un horodatage UNIX exprimé en secondes en type de données TIMESTAMP. |
SECOND() |
Renvoie les secondes d'un TIMESTAMP sous forme d'entier compris entre 0 et 59. |
STRFTIME_UTC_USEC() |
Renvoie une chaîne de date au format date_format_str. |
TIME() |
Renvoie un type de données TIMESTAMP au format %H:%M:%S . |
TIMESTAMP() |
Convertit une chaîne de date en TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en millisecondes. |
TIMESTAMP_TO_SEC() |
Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en secondes. |
TIMESTAMP_TO_USEC() |
Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en microsecondes. |
USEC_TO_TIMESTAMP() |
Convertit un horodatage UNIX exprimé en microsecondes en type de données TIMESTAMP. |
UTC_USEC_TO_DAY() |
Déplace un horodatage UNIX exprimé en microsecondes au début de la journée à laquelle il se rapporte. |
UTC_USEC_TO_HOUR() |
Déplace un horodatage UNIX exprimé en microsecondes au début de l'heure à laquelle il se rapporte. |
UTC_USEC_TO_MONTH() |
Décale un horodatage UNIX exprimé en microsecondes au début du mois auquel il se rapporte. |
UTC_USEC_TO_WEEK() |
Renvoie un horodatage UNIX exprimé en microsecondes représentant un jour de la semaine. |
UTC_USEC_TO_YEAR() |
Renvoie un horodatage UNIX exprimé en microsecondes représentant l'année. |
WEEK() |
Renvoie la semaine d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 53. |
YEAR() |
Renvoie l'année d'un type de données TIMESTAMP. |
Fonctions d'adresse IP | |
---|---|
FORMAT_IP() |
Convertit les 32 bits les moins significatifs du paramètre integer_value en une chaîne d'adresse IPv4 lisible. |
PARSE_IP() |
Convertit une chaîne représentant une adresse IPv4 en un entier sans signature. |
FORMAT_PACKED_IP() |
Renvoie une adresse IP lisible au format 10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Renvoie une adresse IP au format BYTES. |
Fonctions JSON | |
---|---|
JSON_EXTRACT() |
Sélectionne une valeur en fonction de l'expression JSONPath et renvoie une chaîne JSON. |
JSON_EXTRACT_SCALAR() |
Sélectionne une valeur en fonction de l'expression JSONPath et renvoie un scalaire JSON. |
Opérateurs logiques | |
---|---|
expr AND expr |
Renvoie true si les deux expressions sont vraies. |
expr OR expr |
Renvoie true si l'une des expressions ou les deux sont vraies. |
NOT expr |
Renvoie true si l'expression est fausse. |
Fonctions mathématiques | |
---|---|
ABS() |
Renvoie la valeur absolue de l'argument. |
ACOS() |
Renvoie l'arc cosinus de l'argument. |
ACOSH() |
Renvoie l'arc cosinus hyperbolique de l'argument. |
ASIN() |
Renvoie l'arc sinus de l'argument. |
ASINH() |
Renvoie l'arc sinus hyperbolique de l'argument. |
ATAN() |
Renvoie l'arc tangente de l'argument. |
ATANH() |
Renvoie l'arc tangente hyperbolique de l'argument. |
ATAN2() |
Renvoie l'arc tangente des deux arguments. |
CEIL() |
Arrondit l'argument à l'entier supérieur le plus proche et renvoie la valeur arrondie. |
COS() |
Renvoie le cosinus de l'argument. |
COSH() |
Renvoie le cosinus hyperbolique de l'argument. |
DEGREES() |
Convertit des radians en degrés. |
EXP() |
Renvoie e élevé à la puissance de l'argument. |
FLOOR() |
Arrondit l'argument à l'entier inférieur le plus proche. |
LN() LOG()
|
Renvoie le logarithme naturel de l'argument. |
LOG2() |
Renvoie le logarithme en base 2 de l'argument. |
LOG10() |
Renvoie le logarithme en base 10 de l'argument. |
PI() |
Renvoie la constante π. |
POW() |
Renvoie le premier argument élevé à la puissance du deuxième argument. |
RADIANS() |
Convertit des degrés en radians. |
RAND() |
Renvoie une valeur flottante aléatoire comprise dans la plage 0,0 <= valeur < 1,0. |
ROUND() |
Arrondit l'argument à l'entier inférieur ou supérieur le plus proche. |
SIN() |
Renvoie le sinus de l'argument. |
SINH() |
Renvoie le sinus hyperbolique de l'argument. |
SQRT() |
Renvoie la racine carrée de l'expression. |
TAN() |
Renvoie la tangente de l'argument. |
TANH() |
Renvoie la tangente hyperbolique de l'argument. |
Fonctions d'expression régulière | |
---|---|
REGEXP_MATCH() |
Renvoie "true" si l'argument correspond à l'expression régulière. |
REGEXP_EXTRACT() |
Renvoie la partie de l'argument qui correspond au groupe de capture dans l'expression régulière. |
REGEXP_REPLACE() |
Remplace une sous-chaîne qui correspond à une expression régulière. |
Fonctions de chaîne | |
---|---|
CONCAT() |
Renvoie la concaténation de deux ou davantage de chaînes, ou NULL si l'une des valeurs est nulle. |
expr CONTAINS 'str' |
Renvoie true si expr contient l'argument de chaîne spécifié. |
INSTR() |
Renvoie l'index de base 1 de la première occurrence d'une chaîne. |
LEFT() |
Renvoie les caractères les plus à gauche d'une chaîne. |
LENGTH() |
Renvoie la longueur de la chaîne. |
LOWER() |
Renvoie la chaîne d'origine avec tous les caractères en minuscules. |
LPAD() |
Insère des caractères à gauche d'une chaîne. |
LTRIM() |
Supprime des caractères situés à gauche d'une chaîne. |
REPLACE() |
Remplace toutes les occurrences d'une sous-chaîne. |
RIGHT() |
Renvoie les caractères les plus à droite d'une chaîne. |
RPAD() |
Insère des caractères à droite d'une chaîne. |
RTRIM() |
Supprime les caractères de fin situés à droite d'une chaîne. |
SPLIT() |
Divise une chaîne en sous-chaînes répétées. |
SUBSTR() |
Renvoie une sous-chaîne. |
UPPER() |
Renvoie la chaîne d'origine avec tous les caractères en majuscules. |
Fonctions de caractères génériques de table | |
---|---|
TABLE_DATE_RANGE() |
Interroge plusieurs tables quotidiennes couvrant une plage de dates. |
TABLE_DATE_RANGE_STRICT() |
Interroge plusieurs tables quotidiennes couvrant une plage de dates, sans dates manquantes. |
TABLE_QUERY() |
Interroge les tables dont les noms correspondent à un prédicat spécifié. |
Fonctions d'URL | |
---|---|
HOST() |
Avec une URL fournie, renvoie le nom d'hôte sous forme de chaîne. |
DOMAIN() |
Avec une URL fournie, renvoie le domaine sous forme de chaîne. |
TLD() |
Avec une URL fournie, renvoie le domaine de premier niveau ainsi que le domaine de pays de l'URL. |
Fonctions de fenêtrage | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
Opérations identiques aux fonctions d'agrégation correspondantes, mais calculées sur une fenêtre définie par la clause OVER. |
CUME_DIST() |
Renvoie un nombre à double précision qui indique la distribution cumulative d'une valeur d'un groupe de valeurs. |
DENSE_RANK() |
Renvoie le rang (sous forme d'entier) d'une valeur d'un groupe de valeurs. |
FIRST_VALUE() |
Renvoie la première valeur du champ spécifié dans la fenêtre. |
LAG() |
Permet de lire les données d'une ligne précédente d'une fenêtre. |
LAST_VALUE() |
Renvoie la dernière valeur du champ spécifié dans la fenêtre. |
LEAD() |
Permet de lire les données d'une ligne suivante d'une fenêtre. |
NTH_VALUE() |
Renvoie la valeur de <expr> à la position <n> du cadre de fenêtrage... |
NTILE() |
Divise la fenêtre en un nombre de buckets spécifié. |
PERCENT_RANK() |
Renvoie le rang de la ligne actuelle par rapport aux autres lignes de la partition. |
PERCENTILE_CONT() |
Renvoie une valeur interpolée pouvant être mappée à l'argument de centile par rapport à la fenêtre. |
PERCENTILE_DISC() |
Renvoie la valeur la plus proche du centile de l'argument sur la fenêtre. |
RANK() |
Renvoie le rang (sous forme d'entier) d'une valeur d'un groupe de valeurs. |
RATIO_TO_REPORT() |
Renvoie le ratio de chaque valeur par rapport à la somme des valeurs. |
ROW_NUMBER() |
Renvoie le numéro de ligne actuel du résultat de requête sur la fenêtre. |
Autres fonctions | |
---|---|
CASE WHEN ... THEN |
Permet de choisir deux expressions alternatives ou plus dans votre requête. |
CURRENT_USER() |
Renvoie l'adresse e-mail de l'utilisateur exécutant la requête. |
EVERY() |
Renvoie "true" si l'argument est vrai pour toutes ses entrées. |
FROM_BASE64() |
Convertit la chaîne d'entrée encodée en base64 au format BYTES. |
HASH() |
Calcule et renvoie une valeur de hachage signée de 64 bits. |
FARM_FINGERPRINT() |
Calcule et renvoie une valeur d'empreinte signée de 64 bits. |
IF() |
Si le premier argument est vrai, renvoie le deuxième argument. Sinon, renvoie le troisième. |
POSITION() |
Renvoie la position séquentielle de base 1 de l'argument. |
SHA1() |
Renvoie un hachage SHA1 au format BYTES. |
SOME() |
Renvoie "true" si l'argument est vrai pour au moins une de ses entrées. |
TO_BASE64() |
Convertit l'argument BYTES en chaîne encodée en base64. |
Syntaxe des requêtes
Remarque : Les mots clés ne sont pas sensibles à la casse. Dans ce document, les mots clés tels que SELECT
ne sont mis en majuscule qu'à titre illustratif.
Clause SELECT
La clause SELECT
spécifie une liste d'expressions à calculer. Les expressions de la clause SELECT
peuvent contenir des noms de champs, des littéraux, des appels de fonctions (y compris des fonctions d'agrégation et des fonctions de fenêtrage) ainsi que des combinaisons des trois. Les expressions de la liste doivent être séparées par des virgules.
Vous pouvez attribuer un alias à chaque expression en ajoutant une espace suivie d'un identifiant après l'expression. Vous avez également la possibilité d'ajouter le mot clé AS
entre l'expression et l'alias pour améliorer la lisibilité. Les alias définis dans une clause SELECT
peuvent être référencés dans les clauses GROUP BY
, HAVING
et ORDER BY
de la requête, mais pas dans les clauses FROM
, WHERE
ou OMIT RECORD IF
ni dans d'autres expressions de la même clause SELECT
.
Remarques :
-
Si vous utilisez une fonction d'agrégation dans votre clause
SELECT
, vous devez soit employer une fonction d'agrégation dans toutes les expressions, soit ajouter dans votre requête une clauseGROUP BY
qui inclut tous les champs non agrégés de la clauseSELECT
sous forme de clés de groupement. Exemple :#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Vous pouvez utiliser des crochets afin d'échapper les mots réservés et de les employer comme nom de champ et alias. Par exemple, si vous avez une colonne nommée "partition", qui est un mot réservé dans la syntaxe BigQuery, les requêtes faisant référence à ce champ échouent avec des messages d'erreur obscurs, sauf si vous l'échappez entre crochets :
SELECT [partition] FROM ...
Exemple
Cet exemple définit des alias dans la clause SELECT
, puis référence l'un d'entre eux dans la clause ORDER BY
. Notez que la colonne word ne peut pas être référencée par l'alias word_alias dans la clause WHERE
. Elle doit être référencée par son nom. L'alias len n'apparaît pas non plus dans la clause WHERE
. Celui-ci serait toutefois visible dans une clause HAVING
.
#legacySQL SELECT word AS word_alias, LENGTH(word) AS len FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS 'th' ORDER BY len;
Modificateur WITHIN pour les fonctions d'agrégation
aggregate_function WITHIN RECORD [ [ AS ] alias ]
Le mot clé WITHIN
indique à la fonction d'agrégation qu'elle doit agréger les valeurs répétées de chaque enregistrement. Un seul résultat agrégé est produit pour chaque enregistrement d'entrée. Ce type d'agrégation est appelé agrégation ciblée. Comme l'agrégation ciblée génère un résultat pour chaque enregistrement, vous pouvez sélectionner des expressions non agrégées en plus des expressions d'agrégation ciblée sans avoir à employer la clause GROUP BY
.
Vous utiliserez généralement le champ d'application RECORD
avec l'agrégation ciblée. Si vous disposez d'un schéma imbriqué et répété très complexe, vous devrez peut-être effectuer des agrégations dans les champs d'application des sous-enregistrements. Pour cela, remplacez le mot clé RECORD
dans la syntaxe ci-dessus par le nom du nœud du schéma où vous souhaitez effectuer l'agrégation.
Pour en savoir plus sur ce comportement avancé, consultez la section Traiter les données.
Exemple
Cet exemple effectue une agrégation COUNT
ciblée, puis filtre et trie les enregistrements en fonction de la valeur agrégée.
#legacySQL SELECT repository.url, COUNT(payload.pages.page_name) WITHIN RECORD AS page_count FROM [bigquery-public-data:samples.github_nested] HAVING page_count > 80 ORDER BY page_count DESC;
Clause FROM
FROM [project_name:]datasetId.tableId [ [ AS ] alias ] | (subquery) [ [ AS ] alias ] |JOIN
clause |FLATTEN
clause | table wildcard function
La clause FROM
spécifie les données sources à interroger. Les requêtes BigQuery peuvent s'exécuter directement sur des tables, des sous-requêtes, des tables jointes et des tables modifiées par les opérateurs spécifiques décrits ci-dessous. Les combinaisons de ces sources de données peuvent être interrogées à l'aide de la virgule, qui est l'opérateur UNION ALL
de BigQuery.
Référencer les tables
Lorsque vous référencez une table, vous devez spécifier l'ID de l'ensemble de données (datasetId) et l'ID de la table (tableId). Le nom du projet (project_name) est facultatif. Si project_name n'est pas spécifié, BigQuery utilise par défaut le projet actuel. Si le nom de votre projet comprend un tiret, vous devez placer l'ensemble de la référence à la table entre crochets.
Exemple
[my-dashed-project:dataset1.tableName]
Vous pouvez attribuer un alias à une table en ajoutant une espace suivie d'un identifiant après son nom. Vous avez également la possibilité d'ajouter le mot clé AS
entre l'ID de la table (tableId) et l'alias pour améliorer la lisibilité.
Lorsque vous référencez une colonne d'une table, vous pouvez utiliser le nom simple de la colonne, ou préfixer son nom avec l'alias (le cas échéant) ou encore avec l'ID de l'ensemble de données (datasetId) et l'ID de la table (tableId) tant qu'aucun nom de projet (project_name) n'a été spécifié. Le nom project_name ne peut pas être inclus dans le préfixe de la colonne, car le caractère deux-points n'est pas autorisé dans les noms de champs.
Exemples
Cet exemple référence une colonne sans préfixe de table.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
Dans l'exemple ci-dessous, le nom de la colonne est préfixé par l'ID de l'ensemble de données (datasetID) et l'ID de la table (tableID). Notez que le nom du projet (project_name) ne peut pas être inclus dans cet exemple. Cette méthode ne fonctionne que si l'ensemble de données se trouve dans votre projet par défaut actuel.
#legacySQL SELECT samples.shakespeare.word FROM samples.shakespeare;
Dans l'exemple suivant, le nom de la colonne est préfixé par un alias de table.
#legacySQL SELECT t.word FROM [bigquery-public-data:samples.shakespeare] AS t;
Utiliser des sous-requêtes
Une sous-requête est une instruction SELECT
imbriquée placée entre parenthèses. Les expressions calculées dans la clause SELECT
de la sous-requête sont disponibles pour la requête externe, tout comme les colonnes d'une table.
Les sous-requêtes servent à calculer des agrégations ainsi que d'autres expressions. Tous les opérateurs SQL sont utilisables dans les sous-requêtes. Cela signifie qu'une sous-requête peut elle-même contenir d'autres sous-requêtes, ou encore que des sous-requêtes peuvent effectuer des jointures et des regroupements d'agrégations, parmi d'autres opérations.
Virgule en tant qu'opérateur UNION ALL
Contrairement au langage SQL standard de Google, l'ancien SQL utilise la virgule en tant qu'opérateur UNION ALL
plutôt qu'en tant qu'opérateur CROSS JOIN
. Il s'agit d'un ancien comportement qui a évolué, car BigQuery n'était pas compatible avec les requêtes CROSS JOIN
et car les utilisateurs avaient régulièrement besoin d'écrire des requêtes UNION ALL
. En langage SQL standard de Google, les requêtes qui effectuent des unions sont particulièrement longues. L'utilisation de la virgule comme opérateur d'union permet d'écrire ces requêtes beaucoup plus efficacement. Par exemple, la requête suivante permet d'exécuter une seule requête sur des journaux issus de plusieurs jours.
#legacySQL SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503] WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
Les requêtes qui unissent un grand nombre de tables s'exécutent généralement plus lentement que celles qui traitent la même quantité de données à partir d'une seule table. L'exécution peut être ralentie d'un maximum de 50 ms par table supplémentaire. Chaque requête peut unir jusqu'à 1 000 tables.
Fonctions de caractères génériques de table
L'expression fonction de caractère générique de table désigne un type spécial de fonction propre à BigQuery.
Ces fonctions sont employées dans la clause FROM
pour faire correspondre un ensemble de noms de table à l'aide d'un ou de plusieurs types de filtres. Par exemple, la fonction TABLE_DATE_RANGE
peut être utilisée pour n'interroger qu'un ensemble spécifique de tables quotidiennes. Pour en savoir plus sur ces fonctions, reportez-vous à la section Fonctions de caractères génériques de table.
Opérateur FLATTEN
(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened)) (FLATTEN((subquery), field_to_be_flattened))
Contrairement aux systèmes de traitement SQL classiques, BigQuery est conçu pour traiter des données répétées. De ce fait, les utilisateurs de BigQuery doivent parfois écrire des requêtes qui manipulent la structure des enregistrements répétés. L'une des méthodes pour y parvenir consiste à utiliser l'opérateur FLATTEN
.
FLATTEN
convertit un nœud répété du schéma en nœud facultatif. Lorsqu'un enregistrement avec un champ répété associé à une ou plusieurs valeurs est fourni, FLATTEN
crée plusieurs enregistrements (un pour chaque valeur du champ répété). Tous les autres champs sélectionnés dans l'enregistrement sont dupliqués dans chaque nouvel enregistrement de sortie. FLATTEN
peut être appliqué à plusieurs reprises afin de supprimer plusieurs niveaux de répétition.
Pour en savoir plus et obtenir des exemples, consultez la section Traiter les données.
Opérateur de jointure
BigQuery accepte plusieurs opérateurs JOIN
dans chaque clause FROM
.
Les opérations JOIN
ultérieures utilisent les résultats de l'opération JOIN
précédente comme entrée JOIN
de gauche. Les champs de n'importe quelle entrée JOIN
précédente peuvent être employés en tant que clés dans les clauses ON
des opérateurs JOIN
suivants.
Types de jointures
BigQuery accepte les opérations INNER
, [FULL|RIGHT|LEFT] OUTER
et CROSS JOIN
. Si aucune opération n'est spécifiée, la valeur par défaut est INNER
.
Les opérations CROSS JOIN
ne sont pas compatibles avec les clauses ON
. Une opération CROSS JOIN
peut renvoyer une grande quantité de données et donner lieu à une requête lente et inefficace, ou à une requête dépassant le quota de ressources maximal autorisé. Ces requêtes échouent et renvoient une erreur. Dans la mesure du possible, privilégiez les requêtes qui ne contiennent pas d'opération CROSS JOIN
. Par exemple, CROSS JOIN
est souvent utilisé là où une fonction de fenêtrage s'avérerait plus performante.
Modificateur EACH
Le modificateur EACH
indique à BigQuery d'exécuter l'opération JOIN
à l'aide de plusieurs partitions. Il est particulièrement utile lorsque vous savez que les deux côtés de l'opération JOIN
sont volumineux. Le modificateur EACH
ne peut pas être employé dans les clauses CROSS JOIN
.
Le modificateur EACH
était privilégié dans de nombreuses situations, mais cette tendance a évolué. Si possible, utilisez l'opérateur JOIN
sans le modificateur EACH
pour améliorer les performances.
Employez JOIN EACH
lorsque votre requête échoue et renvoie un message d'erreur lié à un dépassement de ressources.
Semi-jointure et anti-jointure
En plus d'accepter l'utilisation de JOIN
dans la clause FROM
, BigQuery est également compatible avec deux types de jointures dans la clause WHERE
: la semi-jointure et l'anti-jointure. Une semi-jointure est spécifiée à l'aide du mot clé IN
dans une sous-requête, tandis qu'une anti-jointure est spécifiée à l'aide des mots clés NOT IN
.
Exemples
La requête suivante utilise une semi-jointure qui permet de rechercher les n-grammes dont le premier mot correspond au deuxième mot d'un autre n-gramme ayant "AND" pour troisième mot.
#legacySQL SELECT ngram FROM [bigquery-public-data:samples.trigrams] WHERE first IN (SELECT second FROM [bigquery-public-data:samples.trigrams] WHERE third = "AND") LIMIT 10;
La requête suivante utilise une semi-jointure qui permet d'identifier le nombre de femmes de plus de 50 ans ayant eu un enfant dans les 10 États avec le plus grand nombre de naissances.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Pour obtenir les chiffres des 40 autres États, vous pouvez employer une anti-jointure. La requête ci-dessous est presque identique à la précédente, mais utilise NOT IN
au lieu de IN
pour identifier le nombre de femmes de plus de 50 ans ayant eu un enfant dans les 40 États avec le moins de naissances.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state NOT IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Notes :
- BigQuery n'accepte pas les semi-jointures ni les anti-jointures corrélées. La sous-requête ne peut référencer aucun champ de la requête externe.
- La sous-requête utilisée dans une semi-jointure ou une anti-jointure ne doit sélectionner qu'un champ.
-
Les types du champ sélectionné et le champ utilisé à partir de la requête externe dans la clause
WHERE
doivent correspondre exactement. BigQuery n'effectue aucune coercition de type pour les semi-jointures ou les anti-jointures.
Clause WHERE
La clause WHERE
, parfois appelée prédicat, filtre les enregistrements produits par la clause FROM
à l'aide d'une expression booléenne. Les clauses booléennes AND
et OR
permettent d'associer plusieurs conditions, qui peuvent être placées entre parenthèses "()" afin d'être regroupées. Les champs répertoriés dans une clause WHERE
n'ont pas besoin d'être sélectionnés dans la clause SELECT
correspondante. En outre, l'expression de la clause WHERE
ne peut pas référencer les expressions calculées dans la clause SELECT
de la requête à laquelle la clause WHERE
appartient.
Remarque : Les fonctions d'agrégation ne peuvent pas être utilisées dans la clause WHERE
. Utilisez une clause HAVING
et une requête externe pour filtrer la sortie d'une fonction d'agrégation.
Exemple
L'exemple suivant utilise une disjonction d'expressions booléennes dans la clause WHERE
(les deux expressions étant associées par un opérateur OR
). Un enregistrement d'entrée sera soumis au filtre WHERE
si l'une des expressions renvoie la valeur true
.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare] WHERE (word CONTAINS 'prais' AND word CONTAINS 'ing') OR (word CONTAINS 'laugh' AND word CONTAINS 'ed');
Clause OMIT RECORD IF
La clause OMIT RECORD IF
est un élément propre à BigQuery. Elle est particulièrement utile pour le traitement des schémas imbriqués et répétés. Elle ressemble à la clause WHERE
, à deux grandes différences près. Tout d'abord, elle utilise une condition d'exclusion, ce qui signifie que les enregistrements sont omis si l'expression renvoie la valeur true
, mais sont conservés si l'expression renvoie false
ou null
. De plus, la clause OMIT RECORD IF
peut employer des fonctions d'agrégation ciblées dans sa condition (et le fait généralement).
En plus de filtrer les enregistrements complets, OMIT...IF
peut spécifier un champ d'application plus restreint pour ne filtrer que certaines parties d'un enregistrement. Pour ce faire, utilisez le nom d'un nœud non-feuille dans votre schéma plutôt que RECORD
dans votre clause OMIT...IF
. Cette fonctionnalité est rarement exploitée par les utilisateurs de BigQuery. Pour en savoir plus sur ce comportement avancé, consultez la documentation ci-dessus relative au modificateur WITHIN
.
Si vous utilisez OMIT...IF
pour exclure une partie d'un enregistrement dans un champ répété et que la requête sélectionne également d'autres champs indépendamment répétés, BigQuery omet une partie des autres enregistrements répétés de la requête. Si vous obtenez l'erreur Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,
, nous vous recommandons de passer au langage SQL standard de Google. Pour en savoir plus sur la migration des instructions OMIT...IF
vers le langage SQL standard de Google, consultez la page Migrer vers le langage SQL standard de Google.
Exemple
Pour reprendre l'exemple utilisé pour le modificateur WITHIN
, la clause OMIT RECORD IF
permet d'atteindre le même résultat que WITHIN
et HAVING
.
#legacySQL SELECT repository.url FROM [bigquery-public-data:samples.github_nested] OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;
Clause GROUP BY
La clause GROUP BY
vous permet de regrouper des lignes ayant les mêmes valeurs pour un champ ou un ensemble de champs donné afin de calculer des agrégations de champs connexes. Le regroupement se produit après le filtrage effectué dans la clause WHERE
, mais avant le calcul des expressions de la clause SELECT
. Les résultats de l'expression ne peuvent pas être utilisés en tant que clés de groupe dans la clause GROUP BY
.
Exemple
Cette requête recherche les 10 premiers mots les plus fréquents dans l'exemple d'ensemble de données "trigrams".
En plus de démontrer l'utilisation de la clause GROUP BY
, la requête explique comment utiliser des index de position à la place de noms de champs dans les clauses GROUP BY
et ORDER BY
.
#legacySQL SELECT first, COUNT(ngram) FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
Une agrégation effectuée à l'aide d'une clause GROUP BY
est appelée agrégation groupée. Contrairement à l'agrégation ciblée, l'utilisation de l'agrégation groupée est courante dans la plupart des systèmes de traitement SQL.
Modificateur EACH
Le modificateur EACH
indique à BigQuery d'exécuter l'opération GROUP BY
à l'aide de plusieurs partitions. Il est particulièrement utile lorsque vous savez que votre ensemble de données contient un grand nombre de valeurs distinctes pour les clés de groupe.
Le modificateur EACH
était privilégié dans de nombreuses situations, mais cette tendance a évolué.
L'utilisation de GROUP BY
sans le modificateur EACH
entraîne généralement de meilleures performances.
Employez GROUP EACH BY
lorsque votre requête échoue et renvoie un message d'erreur lié à un dépassement de ressources.
Fonction ROLLUP
Lorsque la fonction ROLLUP
est utilisée, BigQuery ajoute des lignes supplémentaires au résultat de la requête, qui représentent les agrégations cumulées. Tous les champs répertoriés après ROLLUP
doivent être entourés d'une seule paire de parenthèses. Dans les lignes ajoutées par la fonction ROLLUP
, la valeur NULL
indique les colonnes pour lesquelles l'agrégation est cumulée.
Exemple
Cette requête génère les chiffres annuels relatifs aux naissances d'enfants de sexes masculin et féminin à partir de l'exemple d'ensemble de données "natality".
#legacySQL SELECT year, is_male, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Voici les résultats de la requête. Notez qu'il existe des lignes où l'une ou les deux clés de groupe possèdent la valeur NULL
. Ces lignes sont les lignes de cumul.
+------+---------+----------+ | year | is_male | count | +------+---------+----------+ | NULL | NULL | 12122730 | | 2000 | NULL | 4063823 | | 2000 | false | 1984255 | | 2000 | true | 2079568 | | 2001 | NULL | 4031531 | | 2001 | false | 1970770 | | 2001 | true | 2060761 | | 2002 | NULL | 4027376 | | 2002 | false | 1966519 | | 2002 | true | 2060857 | +------+---------+----------+
Lorsque vous utilisez la fonction ROLLUP
, vous pouvez employer la fonction GROUPING
pour différencier les lignes ajoutées par la fonction ROLLUP
et celles ayant une valeur NULL
pour la clé de groupe.
Exemple
La requête ci-dessous ajoute la fonction GROUPING
à l'exemple précédent afin de mieux identifier les lignes ajoutées par la fonction ROLLUP
.
#legacySQL SELECT year, GROUPING(year) as rollup_year, is_male, GROUPING(is_male) as rollup_gender, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Voici les résultats renvoyés par la nouvelle requête :
+------+-------------+---------+---------------+----------+ | year | rollup_year | is_male | rollup_gender | count | +------+-------------+---------+---------------+----------+ | NULL | 1 | NULL | 1 | 12122730 | | 2000 | 0 | NULL | 1 | 4063823 | | 2000 | 0 | false | 0 | 1984255 | | 2000 | 0 | true | 0 | 2079568 | | 2001 | 0 | NULL | 1 | 4031531 | | 2001 | 0 | false | 0 | 1970770 | | 2001 | 0 | true | 0 | 2060761 | | 2002 | 0 | NULL | 1 | 4027376 | | 2002 | 0 | false | 0 | 1966519 | | 2002 | 0 | true | 0 | 2060857 | +------+-------------+---------+---------------+----------+
Remarques :
- Les champs non agrégés de la clause
SELECT
doivent être répertoriés dans la clauseGROUP BY
.#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
- Les expressions calculées dans la clause
SELECT
ne peuvent pas être utilisées dans la clauseGROUP BY
correspondante.#legacySQL SELECT word, corpus, COUNT(word) word_count FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus, word_count; /* Fails because word_count is not visible to this
GROUP BY
clause. */ - Le regroupement par valeur flottante et par valeur à double précision n'est pas accepté, car la fonction d'égalité pour ces types n'est pas bien définie.
-
Comme il s'agit d'un système interactif, les requêtes qui renvoient un grand nombre de groupes peuvent échouer. L'utilisation de la fonction
TOP
au lieu deGROUP BY
peut résoudre certains problèmes de scaling.
Clause HAVING
La clause HAVING
se comporte exactement comme la clause WHERE
, sauf qu'elle est évaluée après la clause SELECT
, de sorte que les résultats de toutes les expressions calculées sont visibles par la clause HAVING
. Cette clause ne peut faire référence qu'aux résultats de la clause SELECT
correspondante.
Exemple
La requête suivante calcule les premiers mots les plus fréquents dans l'exemple d'ensemble de données sur les n-grammes qui contiennent la lettre "a" et apparaissent moins de 10 000 fois.
#legacySQL SELECT first, COUNT(ngram) ngram_count FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 HAVING first contains "a" AND ngram_count < 10000 ORDER BY 2 DESC LIMIT 10;
Clause ORDER BY
La clause ORDER BY
trie les résultats d'une requête par ordre croissant ou décroissant à l'aide d'un ou de plusieurs champs de clé. Pour trier les résultats à l'aide de plusieurs champs ou alias, saisissez-les sous la forme d'une liste d'entrées séparées par des virgules. Les résultats sont triés en fonction des champs dans l'ordre dans lequel ils sont répertoriés.
Utilisez DESC
(décroissant) ou ASC
(croissant) pour définir le sens de tri.
ASC
est la valeur par défaut. Vous pouvez définir un sens de tri différent pour chaque clé de tri.
La clause ORDER BY
est évaluée après la clause SELECT
. Ainsi, elle peut référencer le résultat de toute expression calculée dans la clause SELECT
. Si un alias est attribué à un champ de la clause SELECT
, il doit être utilisé dans la clause ORDER BY
.
Clause LIMIT
La clause LIMIT
limite le nombre de lignes dans l'ensemble de résultats renvoyé. Comme les requêtes BigQuery opèrent régulièrement sur un très grand nombre de lignes, LIMIT
permet d'éviter les requêtes de longue durée en ne traitant qu'un sous-ensemble de lignes.
Remarques :
-
La clause
LIMIT
arrête le traitement et renvoie les résultats obtenus une fois vos conditions remplies. Cela peut réduire le temps de traitement de certaines requêtes, mais lorsque vous spécifiez des fonctions d'agrégation telles que COUNT ou des clausesORDER BY
, la totalité de l'ensemble de résultats doit être traitée avant que les résultats ne soient renvoyés. La clauseLIMIT
est la dernière à être évaluée. -
Une requête avec une clause
LIMIT
peut rester non déterministe si elle ne contient aucun opérateur garantissant le tri de l'ensemble de résultats généré. En effet, BigQuery exécute un grand nombre de nœuds de calcul en parallèle. L'ordre dans lequel les tâches parallèles sont renvoyées n'est pas garanti. - La clause
LIMIT
ne peut contenir aucune fonction. Elle n'accepte qu'une constante numérique.
Grammaire des requêtes
Les clauses individuelles des instructions SELECT
BigQuery sont décrites en détail ci-dessus. Cette section présente la grammaire complète des instructions SELECT
dans un format compact et inclut des liens vous redirigeant vers les sections individuelles.
query: SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ] [ FROM from_body [ WHERE bool_expression ] [ OMIT RECORD IF bool_expression] [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ] [ HAVING bool_expression ] [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ] [ LIMIT n ] ]; from_body: { from_item [, ...] | # Warning: Comma means UNION ALL here from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] | (FLATTEN({ table_name | (query) }, field_name_or_alias)) | table_wildcard_function } from_item: { table_name | (query) } [ [ AS ] alias ] join_type: { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS } join_predicate: field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...] expression: { literal_value | field_name_or_alias | function_call } bool_expression: { expression_which_results_in_a_boolean_value | bool_expression AND bool_expression | bool_expression OR bool_expression | NOT bool_expression }
Notation :
- Les crochets "[]" indiquent des clauses facultatives.
- Les accolades "{}" renferment un ensemble d'options.
- La barre verticale "|" indique un opérateur logique "OR".
- Une virgule suivie de points de suspension entre crochets "[, …]" indique que l'élément précédent peut être répété dans une liste séparée par des virgules.
- Les parenthèses "()" indiquent des parenthèses littérales.
Fonctions d'agrégation
Les fonctions d'agrégation renvoient des valeurs qui représentent des résumés d'ensembles de données plus volumineux, ce qui les rend particulièrement utiles pour l'analyse de journaux. Une fonction d'agrégation exploite un ensemble de valeurs et renvoie une valeur unique par table, groupe ou champ d'application :
- Agrégation de table
Utilise une fonction d'agrégation pour résumer toutes les lignes éligibles de la table. Exemple :
SELECT COUNT(f1) FROM ds.Table;
- Agrégation de groupe
Utilise une fonction d'agrégation et une clause
GROUP BY
qui spécifie un champ non agrégé pour résumer les lignes par groupe. Exemple :SELECT COUNT(f1) FROM ds.Table GROUP BY b1;
La fonction TOP constitue un cas spécial d'agrégation de groupe.
- Agrégation de champ d'application
Cette fonctionnalité ne s'applique qu'aux tables contenant des champs imbriqués.
Utilise une fonction d'agrégation et le mot cléWITHIN
pour agréger les valeurs répétées comprises dans un champ d'application défini. Exemple :SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;
Le champ d'application peut être
RECORD
, ce qui correspond à une ligne entière, ou bien un nœud (champ répété dans une ligne). Les fonctions d'agrégation exploitent les valeurs du champ d'application et renvoient les résultats agrégés pour chaque enregistrement ou nœud.
Vous pouvez appliquer une restriction à une fonction d'agrégation en utilisant l'une des options suivantes :
-
Un alias dans une requête "subselect". La restriction est spécifiée dans la clause
WHERE
externe.#legacySQL SELECT corpus, count_corpus_words FROM (SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus) AS sub_shakespeare WHERE count_corpus_words > 4000
-
Un alias dans une clause HAVING.
#legacySQL SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus HAVING count_corpus_words > 4000;
Vous pouvez également faire référence à un alias dans les clauses GROUP BY
ou ORDER BY
.
Syntaxe
Fonctions d'agrégation | |
---|---|
AVG() |
Renvoie la moyenne des valeurs pour un groupe de lignes. |
BIT_AND() |
Renvoie le résultat d'une opération AND (ET) bit à bit. |
BIT_OR() |
Renvoie le résultat d'une opération OR (OU) bit à bit. |
BIT_XOR() |
Renvoie le résultat d'une opération XOR (OU exclusif) bit à bit. |
CORR() |
Renvoie le coefficient de corrélation de Pearson d'un ensemble de paires de nombres. |
COUNT() |
Renvoie le nombre total de valeurs. |
COUNT([DISTINCT]) |
Renvoie le nombre total de valeurs non nulles. |
COVAR_POP() |
Calcule la covariance de population des valeurs. |
COVAR_SAMP() |
Calcule la covariance d'échantillon des valeurs. |
EXACT_COUNT_DISTINCT() |
Renvoie le nombre exact de valeurs non nulles distinctes pour le champ spécifié. |
FIRST() |
Renvoie la première valeur séquentielle comprise dans le champ d'application de la fonction. |
GROUP_CONCAT() |
Concatène plusieurs chaînes en une seule chaîne. |
GROUP_CONCAT_UNQUOTED() |
Concatène plusieurs chaînes en une seule chaîne. N'ajoute pas de guillemets doubles. |
LAST() |
Renvoie la dernière valeur séquentielle. |
MAX() |
Renvoie la valeur maximale. |
MIN() |
Renvoie la valeur minimale. |
NEST() |
Agrège toutes les valeurs comprises dans le champ d'application actuel de l'agrégation dans un champ répété. |
NTH() |
Renvoie la nième valeur séquentielle. |
QUANTILES() |
Calcule approximativement la valeur minimale, la valeur maximale et les quantiles. |
STDDEV() |
Renvoie l'écart type. |
STDDEV_POP() |
Calcule l'écart type de la population. |
STDDEV_SAMP() |
Calcule l'écart type de l'échantillon. |
SUM() |
Renvoie la somme totale des valeurs. |
TOP() ... COUNT(*) |
Renvoie les enregistrements "max_records" les mieux classés par fréquence. |
UNIQUE() |
Renvoie l'ensemble de valeurs uniques et non nulles. |
VARIANCE() |
Calcule la variance des valeurs. |
VAR_POP() |
Calcule la variance de population des valeurs. |
VAR_SAMP() |
Calcule la variance d'échantillon des valeurs. |
AVG(numeric_expr)
- Renvoie la moyenne des valeurs pour un groupe de lignes calculé par
numeric_expr
. Les lignes ayant une valeur NULL ne sont pas incluses dans le calcul. BIT_AND(numeric_expr)
- Renvoie le résultat d'une opération
AND
bit à bit entre chaque instance denumeric_expr
sur l'ensemble des lignes. Les valeursNULL
sont ignorées. Cette fonction renvoieNULL
si toutes les instances denumeric_expr
ont la valeurNULL
. BIT_OR(numeric_expr)
- Renvoie le résultat d'une opération
OR
bit à bit entre chaque instance denumeric_expr
sur l'ensemble des lignes. Les valeursNULL
sont ignorées. Cette fonction renvoieNULL
si toutes les instances denumeric_expr
ont la valeurNULL
. BIT_XOR(numeric_expr)
- Renvoie le résultat d'une opération
XOR
bit à bit entre chaque instance denumeric_expr
sur l'ensemble des lignes. Les valeursNULL
sont ignorées. Cette fonction renvoieNULL
si toutes les instances denumeric_expr
ont la valeurNULL
. CORR(numeric_expr, numeric_expr)
- Renvoie le coefficient de corrélation de Pearson d'un ensemble de paires de nombres.
COUNT(*)
- Renvoie le nombre total de valeurs (nulles et non nulles) comprises dans le champ d'application de la fonction. À moins que vous n'utilisiez
COUNT(*)
avec la fonctionTOP
, il est recommandé de spécifier explicitement le champ qui vous intéresse. COUNT([DISTINCT] field [, n])
- Renvoie le nombre total de valeurs non nulles comprises dans le champ d'application de la fonction.
Si vous utilisez le mot clé
DISTINCT
, la fonction renvoie le nombre de valeurs distinctes pour le champ spécifié. Notez que la valeur renvoyée pourDISTINCT
est une approximation statistique et que son exactitude n'est pas garantie.Utilisez
EXACT_COUNT_DISTINCT()
pour obtenir une réponse exacte.Si vous avez besoin d'une plus grande précision de
, vous pouvez spécifier un deuxième paramètre,COUNT(DISTINCT)
n
, qui indique le seuil en dessous duquel les résultats exacts sont garantis. Par défaut,n
est égal à 1 000, mais si vous définissez une valeurn
plus élevée,COUNT(DISTINCT)
renverra des résultats exacts jusqu'à cette valeur den
. Sachez toutefois que la définition d'une valeurn
plus élevée réduit l'évolutivité de cet opérateur et peut augmenter considérablement la durée d'exécution de la requête ou la faire échouer.Pour calculer le nombre exact de valeurs distinctes, utilisez EXACT_COUNT_DISTINCT. Si vous recherchez une approche plus évolutive, envisagez d'utiliser
GROUP EACH BY
sur les champs concernés et d'appliquer la fonctionCOUNT(*)
. L'approcheGROUP EACH BY
est plus évolutive, mais peut entraîner une légère baisse initiale des performances. COVAR_POP(numeric_expr1, numeric_expr2)
- Calcule la covariance de population des valeurs calculées par
numeric_expr1
etnumeric_expr2
. COVAR_SAMP(numeric_expr1, numeric_expr2)
- Calcule la covariance d'échantillon des valeurs calculées par
numeric_expr1
etnumeric_expr2
. EXACT_COUNT_DISTINCT(field)
- Renvoie le nombre exact de valeurs non nulles distinctes pour le champ spécifié. Pour une meilleure évolutivité et de meilleures performances, utilisez COUNT(DISTINCT field).
FIRST(expr)
- Renvoie la première valeur séquentielle comprise dans le champ d'application de la fonction.
GROUP_CONCAT('str' [, separator])
-
Concatène plusieurs chaînes en une seule où chaque valeur est séparée par le paramètre
separator
facultatif. Si le paramètreseparator
est omis, BigQuery renvoie une chaîne de valeurs séparées par des virgules.Si une chaîne contient un guillemet double dans les données sources,
GROUP_CONCAT
renvoie la chaîne en ajoutant des guillemets doubles. Par exemple, la chaînea"b
renvoie le résultat"a""b"
. UtilisezGROUP_CONCAT_UNQUOTED
si vous préférez que les chaînes ne soient pas renvoyées avec des guillemets doubles.Exemple :
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])
-
Concatène plusieurs chaînes en une seule où chaque valeur est séparée par le paramètre
separator
facultatif. Si le paramètreseparator
est omis, BigQuery renvoie une chaîne de valeurs séparées par des virgules.Contrairement à
GROUP_CONCAT
, cette fonction n'ajoute pas de guillemets doubles aux valeurs renvoyées qui comprennent un caractère "guillemet double". Par exemple, la chaînea"b
renvoie le résultata"b
.Exemple :
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)
- Renvoie la dernière valeur séquentielle comprise dans le champ d'application de la fonction.
MAX(field)
- Renvoie la valeur maximale comprise dans le champ d'application de la fonction.
MIN(field)
- Renvoie la valeur minimale comprise dans le champ d'application de la fonction.
NEST(expr)
-
Agrège toutes les valeurs comprises dans le champ d'application actuel de l'agrégation dans un champ répété. Par exemple, la requête
"SELECT x, NEST(y) FROM ... GROUP BY x"
renvoie un enregistrement de sortie pour chaque valeurx
distincte et contient un champ répété pour toutes les valeursy
associées àx
dans l'entrée de la requête. La fonctionNEST
nécessite une clauseGROUP BY
.BigQuery aplatit automatiquement les résultats de la requête. Ainsi, si vous utilisez la fonction
NEST
sur la requête de premier niveau, les résultats ne contiendront pas de champs répétés. Employez la fonctionNEST
lorsque vous utilisez une instruction "subselect" qui génère des résultats intermédiaires pouvant être utilisés immédiatement par la même requête. NTH(n, field)
- Renvoie la
n
ième valeur séquentielle dans le champ d'application de la fonction, oùn