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. Pour plus d'informations sur le langage SQL standard, consultez la page Fonctions et opérateurs du langage SQL standard.

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 clause GROUP BY qui inclut tous les champs non agrégés de la clause

SELECT

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, BigQuery 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 SQL standard, 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. Pour en savoir plus sur la migration des instructions OMIT...IF vers le langage SQL standard, consultez la page Migrer vers le langage SQL standard.

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 sans le modificateur entraîne généralement de meilleures performances. Employez 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 clause GROUP 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 clause


    GROUP 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 de GROUP 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 clauses ORDER BY, la totalité de l'ensemble de résultats doit être traitée avant que les résultats ne soient renvoyés. La clause LIMIT 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 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 de numeric_expr sur l'ensemble des lignes. Les valeurs NULL sont ignorées. Cette fonction renvoie NULL si toutes les instances de numeric_expr ont la valeur NULL.

BIT_OR(numeric_expr)

Renvoie le résultat d'une opération OR bit à bit entre chaque instance de numeric_expr sur l'ensemble des lignes. Les valeurs NULL sont ignorées. Cette fonction renvoie NULL si toutes les instances de numeric_expr ont la valeur NULL.

BIT_XOR(numeric_expr)

Renvoie le résultat d'une opération XOR bit à bit entre chaque instance de numeric_expr sur l'ensemble des lignes. Les valeurs NULL sont ignorées. Cette fonction renvoie NULL si toutes les instances de numeric_expr ont la valeur NULL.

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 fonction , 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 pour DISTINCT 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 COUNT(DISTINCT), vous pouvez spécifier un deuxième paramètre, 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 valeur n plus élevée, COUNT(DISTINCT) renverra des résultats exacts jusqu'à cette valeur de n. Sachez toutefois que la définition d'une valeur n 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 fonction COUNT(*). L'approche GROUP 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 et numeric_expr2.

COVAR_SAMP(numeric_expr1, numeric_expr2)

Calcule la covariance d'échantillon des valeurs calculées par numeric_expr1 et numeric_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ètre separator 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îne a"b renvoie le résultat "a""b". Utilisez GROUP_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])

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îne a"b renvoie le résultat a"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 valeur x distincte et contient un champ répété pour toutes les valeurs associées à x dans l'entrée de la requête. La fonction NEST nécessite une clause GROUP 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 fonction NEST 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 nième valeur séquentielle dans le champ d'application de la fonction, où n est une constante. La fonction NTH commence à compter à partir de 1. L'ordre zéro n'existe donc pas. Si le champ d'application de la fonction comprend moins de n valeurs, la fonction renvoie NULL.

QUANTILES(expr[, buckets])

Calcule approximativement la valeur minimale, la valeur maximale et les quantiles pour l'expression d'entrée. Les valeurs d'entrée NULL sont ignorées. Les entrées vides ou possédant exclusivement la valeur NULL aboutissent au résultat NULL. Le nombre de quantiles calculés est contrôlé par le paramètre facultatif buckets, qui inclut les valeurs minimale et maximale. Pour calculer approximativement N-tiles, utilisez N+1 . La valeur par défaut de buckets est 100. (Remarque : la valeur 100 par défaut n'estime pas les centiles. Pour cela, vous devez utiliser au moins 101 buckets.) Si le paramètre buckets est explicitement spécifié, sa valeur ne doit pas être inférieure à 2.

L'erreur fractionnelle par quantile est epsilon = 1/buckets, ce qui signifie que l'erreur diminue à mesure que le nombre de buckets augmente. Exemple :

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

La fonction NTH peut être utilisée pour sélectionner un quantile spécifique, mais n'oubliez pas que NTH a une base de 1 et que QUANTILES renvoie la valeur minimale (quantile "0") en première position et la valeur maximale ("100e" centile ou "Nième" N-tile) en dernière position. Par exemple, NTH(11, QUANTILES(expr, 21)) estime la médiane de expr, tandis que NTH(20, QUANTILES(expr, 21)) estime le 19e vigintile (95e centile) de expr. Les deux estimations présentent une marge d'erreur de 5 %.

Pour améliorer la précision, utilisez davantage de buckets. Par exemple, pour réduire la marge d'erreur des calculs précédents de 5 % à 0,1 %, employez 1 001 buckets au lieu de 21 et ajustez l'argument à la fonction NTH en conséquence. Pour calculer la médiane avec une marge d'erreur de 0,1 %, utilisez NTH(501, QUANTILES(expr, 1001)). Pour calculer le 95e centile avec une marge d'erreur de 0,1 %, utilisez NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Renvoie l'écart type des valeurs calculées par numeric_expr. Les lignes ayant une valeur nulle ne sont pas incluses dans le calcul. La fonction STDDEV est un alias pour STDDEV_SAMP.

STDDEV_POP(numeric_expr)

Calcule l'écart type de population de la valeur calculée par numeric_expr. Utilisez STDDEV_POP() pour calculer l'écart type d'un ensemble de données englobant la population d'intérêt dans sa totalité. Si votre ensemble de données ne comprend qu'un échantillon représentatif de la population, utilisez plutôt STDDEV_SAMP(). Pour en savoir plus sur les différences entre l'écart type de population et l'écart type d'échantillon, consultez la page Écart type de Wikipédia.

STDDEV_SAMP(numeric_expr)

Calcule l'écart type d'échantillon de la valeur calculée par numeric_expr. Utilisez STDDEV_SAMP() pour calculer l'écart-type d'une population entière en fonction d'un échantillon représentatif de la population. Si votre ensemble de données comprend la population dans sa totalité, utilisez plutôt STDDEV_POP(). Pour en savoir plus sur les différences entre l'écart type de population et l'écart type d'échantillon, consultez la page Écart type de Wikipédia.

SUM(field)

Renvoie la somme totale des valeurs comprises dans le champ d'application de la fonction. Cette fonction n'accepte que des types de données numériques.

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Renvoie les enregistrements max_records les mieux classés par fréquence. Consultez la description de la fonction TOP ci-dessous pour en savoir plus.

UNIQUE(expr)

Renvoie l'ensemble de valeurs uniques non nulles du champ d'application de la fonction dans un ordre indéterminé. S'il existe un trop grand nombre de valeurs distinctes, la requête échoue et renvoie une erreur "Resources Exceeded" (Dépassement de ressources), tout comme le ferait une vaste clause ne contenant pas le mot clé . Cependant, contrairement à , vous pouvez appliquer la fonction avec une agrégation de champ d'application, ce qui vous permet d'exécuter des opérations performantes sur des champs imbriqués avec un nombre de valeurs limité.

VARIANCE(numeric_expr)

Calcule la variance des valeurs calculées par numeric_expr. Les lignes ayant une valeur nulle ne sont pas incluses dans le calcul. La fonction VARIANCE est un alias pour VAR_SAMP.

VAR_POP(numeric_expr)

Calcule la variance de population des valeurs calculées par numeric_expr. Pour en savoir plus sur les différences entre l'écart type de population et l'écart type d'échantillon, consultez la page Écart type de Wikipédia.

VAR_SAMP(numeric_expr)

Calcule la variance d'échantillon des valeurs calculées par numeric_expr. Pour en savoir plus sur les différences entre l'écart type de population et l'écart type d'échantillon, consultez la page Écart type de Wikipédia.

Fonction TOP()

La fonction TOP constitue une alternative à la clause GROUP BY. Elle permet de bénéficier d'une syntaxe simplifiée pour GROUP BY ... ORDER BY ... LIMIT .... En règle générale, la fonction TOP s'exécute plus rapidement que la requête ... GROUP BY ... ORDER BY ... LIMIT ... complète mais elle peut ne renvoyer que des résultats approximatifs. Voici la syntaxe associée à la fonction TOP :

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Lorsque vous utilisez la fonction TOP dans une clause SELECT, vous devez inclure un champ .

Une requête qui emploie la fonction TOP() ne peut renvoyer que deux champs : le champ TOP et la valeur COUNT(*).

field|alias: Champ ou alias à renvoyer.
max_values: [Facultatif] Nombre maximal de résultats autorisé. La valeur par défaut est "20".
multiplier: Entier positif qui multiplie la ou les valeurs renvoyées par COUNT(*) par le nombre spécifié.

Exemples d'utilisation de TOP()

Exemples de requêtes de base utilisant TOP()

Les requêtes suivantes emploient TOP() pour renvoyer 10 lignes.

Exemple 1 :

#legacySQL
SELECT
  TOP(word, 10) as word, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th";

Exemple 2 :

#legacySQL
SELECT
  word, left(word, 3)
FROM
  (SELECT TOP(word, 10) AS word, COUNT(*)
     FROM [bigquery-public-data:samples.shakespeare]
     WHERE word CONTAINS "th");

Comparer TOP() à GROUP BY...ORDER BY...LIMIT

La requête renvoie dans l'ordre les 10 mots les plus fréquents contenant "th" ainsi que le nombre de documents dans lequel les mots sont employés. La requête TOP s'exécute bien plus rapidement :

Exemple sans TOP() :
```
#legacySQL
SELECT
  word, COUNT(*) AS cnt
FROM
  ds.Table
WHERE
  word CONTAINS 'th'
GROUP BY
  word
ORDER BY
  cnt DESC LIMIT 10;
```
Exemple avec TOP() :
```
#legacySQL
SELECT
  TOP(word, 10), COUNT(*)
FROM
  ds.Table
WHERE
  word contains 'th';
```

Utilisation du paramètre multiplier.

Les requêtes suivantes montrent la façon dont le paramètre multiplier affecte le résultat de la requête. La première requête renvoie le nombre de naissances par mois dans le Wyoming. La deuxième exploite le paramètre multiplier pour multiplier les valeurs cnt par 100.

Exemple sans le paramètre multiplier :

#legacySQL
SELECT
  TOP(month,3) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

Renvoie :

+-------+-------+
| month |  cnt  |
+-------+-------+
|   7   | 19594 |
|   5   | 19038 |
|   8   | 19030 |
+-------+-------+

Exemple avec le paramètre multiplier :

#legacySQL
SELECT
  TOP(month,3,100) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

Renvoie :

+-------+---------+
| month |   cnt   |
+-------+---------+
|   7   | 1959400 |
|   5   | 1903800 |
|   8   | 1903000 |
+-------+---------+

Remarque : doit être inclus dans la clause pour utiliser .

Exemples avancés

Classer l'écart type et l'écart moyen par condition

La requête suivante renvoie l'écart type et l'écart moyen du poids des nouveau-nés dans l'Ohio en 2003, classés par mère fumeuse ou non-fumeuse.

Exemple :

#legacySQL
SELECT
  cigarette_use,
  /* Finds average and standard deviation */
  AVG(weight_pounds) baby_weight,
  STDDEV(weight_pounds) baby_weight_stdev,
  AVG(mother_age) mother_age
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year=2003 AND state='OH'
/* Group the result values by those */
/* who smoked and those who didn't.  */
GROUP BY
  cigarette_use;

Filtrer les résultats de la requête à l'aide d'une valeur agrégée

Pour filtrer les résultats de la requête à l'aide d'une valeur agrégée (par exemple, filtrer les résultats en fonction de la valeur d'une somme SUM), utilisez la fonction HAVING. La fonction HAVING compare une valeur à un résultat déterminé par une fonction d'agrégation, contrairement à la clause WHERE qui opère sur chaque ligne avant l'agrégation.

Exemple :

#legacySQL
SELECT
  state,
  /* If 'is_male' is True, return 'Male', */
  /* otherwise return 'Female' */
  IF (is_male, 'Male', 'Female') AS sex,
  /* The count value is aliased as 'cnt' */
  /* and used in the HAVING clause below. */
  COUNT(*) AS cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state != ''
GROUP BY
  state, sex
HAVING
  cnt > 3000000
ORDER BY
  cnt DESC

Renvoie :

+-------+--------+---------+
| state |  sex   |   cnt   |
+-------+--------+---------+
| CA    | Male   | 7060826 |
| CA    | Female | 6733288 |
| TX    | Male   | 5107542 |
| TX    | Female | 4879247 |
| NY    | Male   | 4442246 |
| NY    | Female | 4227891 |
| IL    | Male   | 3089555 |
+-------+--------+---------+

Opérateurs arithmétiques

Les opérateurs arithmétiques exploitent des arguments numériques et renvoient un résultat numérique. Chaque argument peut être un littéral numérique ou une valeur numérique renvoyée par une requête. Si l'opération arithmétique aboutit à un résultat indéterminé, l'opération renvoie la valeur NULL.

Syntaxe

Opérateur	Description	Exemple
+	Addition	`SELECT 6 + (5 - 1);` Renvoie : 10
-	Soustraction	`SELECT 6 - (4 + 1);` Renvoie : 1
*	Multiplication	`SELECT 6 * (5 - 1);` Renvoie : 24
/	Division	`SELECT 6 / (2 + 2);` Renvoie : 1,5
%	Modulo	`SELECT 6 % (2 + 2);` Renvoie : 2

Fonctions bit à bit

Les fonctions bit à bit agissent au niveau des bits individuels et nécessitent des arguments numériques. Pour en savoir plus sur ces fonctions, consultez la page Opération bit à bit.

Trois fonctions bit à bit supplémentaires (BIT_AND, BIT_OR et BIT_XOR) sont décrites dans les fonctions d'agrégation.

Syntaxe

Opérateur	Description	Exemple
&	Opérateur AND (ET) bit à bit	`SELECT (1 + 3) & 1` Renvoie : 0
\|	Opérateur OR (OU) bit à bit	`SELECT 24 \| 12` Renvoie : 28
^	Opérateur XOR (OU exclusif) bit à bit	`SELECT 1 ^ 0` Renvoie : 1
<<	Décalage à gauche bit à bit	`SELECT 1 << (2 + 2)` Renvoie : 16
>>	Décalage à droite bit à bit	`SELECT (6 + 2) >> 2` Renvoie : 2
~	Opérateur NOT (PAS) bit à bit	`SELECT ~2` Renvoie -3
`BIT_COUNT(<numeric_expr>)`	Renvoie le nombre de bits définis dans `<numeric_expr>`.	`SELECT BIT_COUNT(29);` Renvoie : 4

Fonctions de cast

Les fonctions de cast modifient le type de données d'une expression numérique. Elles sont particulièrement utiles pour s'assurer que les arguments d'une fonction de comparaison possèdent bien le même type de données.

Syntaxe

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.

BOOLEAN(<numeric_expr>)

Renvoie true si la valeur de <numeric_expr> est différente de 0 et n'est pas nulle.
Renvoie false si <numeric_expr> est défini sur 0.
Renvoie NULL si la valeur de <numeric_expr> est nulle.

BYTES(string_expr)

Renvoie string_expr sous la forme d'une valeur de type bytes.

CAST(expr AS type)

Convertit expr en une variable de type type.

FLOAT(expr)

Renvoie expr en double. La chaîne expr peut être de type '45.78', mais la fonction renvoie NULL pour des valeurs non numériques.

HEX_STRING(numeric_expr)

Renvoie numeric_expr sous forme de chaîne hexadécimale.

INTEGER(expr)

Convertit expr en nombre entier de 64 bits.

Renvoie NULL si expr est une chaîne qui ne correspond pas à une valeur entière.
Renvoie le nombre de microsecondes écoulées depuis l'époque Unix si expr est un horodatage.

STRING(numeric_expr)

Renvoie numeric_expr sous forme de chaîne.

Fonctions de comparaison

Les fonctions de comparaison renvoient true ou false en se basant sur les types de comparaison suivants :

Comparaison de deux expressions
Comparaison d'une expression ou d'un ensemble d'expressions à l'aide d'un critère spécifique (par exemple, appartenir à une liste spécifique, posséder la valeur NULL ou disposer d'une valeur facultative autre que celle par défaut)

Certaines des fonctions répertoriées ci-dessous renvoient des valeurs autres que true ou false, mais ces valeurs sont basées sur des opérations de comparaison.

Vous pouvez utiliser des expressions numériques ou de chaîne comme arguments pour les fonctions de comparaison. (Les constantes de chaîne doivent être placées entre guillemets simples ou doubles.) Les expressions peuvent être des littéraux ou des valeurs récupérées par une requête. Les fonctions de comparaison sont généralement utilisées en tant que conditions de filtrage dans les clauses WHERE, mais elles peuvent également servir dans d'autres clauses.

Syntaxe

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

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 supérieure ou égale à expr2 ou inférieure ou égale à expr3.

expr IS NULL

Renvoie true si la valeur de expr est nulle.

expr IN(expr1, expr2, ...)

Renvoie true si la valeur de expr correspond à expr1, expr2 ou à toute valeur entre parenthèses. Le mot-clé IN est un raccourci efficace pour (expr = expr1 || expr = expr2 || ...). Les expressions utilisées avec le mot clé IN doivent être des constantes et doivent correspondre au type de données de expr. La clause IN peut également être utilisée pour créer des semi-jointures et des anti-jointures. Pour plus d'informations, reportez-vous à Semi-jointure et anti-jointure.

COALESCE(<expr1>, <expr2>, ...)

Retourne le premier argument qui n'est pas NULL.

GREATEST(numeric_expr1, numeric_expr2, ...)

Renvoie le plus grand paramètre numeric_expr. Tous les paramètres doivent être numériques et posséder le même type. Si l'un des paramètres présente la valeur NULL, la fonction renvoie NULL.

Pour ignorer les valeurs NULL, utilisez la fonction IFNULL pour remplacer les valeurs NULL par une valeur n'affectant pas la comparaison. Dans l'exemple de code suivant, la fonction IFNULL remplace les valeurs NULL par -1, ce qui n'affecte pas la comparaison entre les nombres positifs.

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);

IFNULL(expr, null_default)

Si la valeur de expr n'est pas nulle, renvoie expr, sinon renvoie null_default.

IS_INF(numeric_expr)

Renvoie true si la valeur de numeric_expr correspond à l'infini positif ou négatif.

IS_NAN(numeric_expr)

Renvoie true si numeric_expr est la valeur numérique NaN spéciale.

IS_EXPLICITLY_DEFINED(expr)

Cette fonction est obsolète. Utilisez plutôt expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Renvoie le plus petit paramètre numeric_expr. Tous les paramètres doivent être numériques et posséder le même type. Si l'un des paramètres présente la valeur NULL, la fonction renvoie NULL.

NVL(expr, null_default)

Si la valeur de expr n'est pas nulle, renvoie expr, sinon renvoie null_default. La fonction NVL est un alias pour IFNULL.

Fonctions de date et heure

Les fonctions suivantes permettent de manipuler la date et l'heure des horodatages UNIX, des chaînes de date et des types de données TIMESTAMP. Pour en savoir plus sur l'utilisation du type de données TIMESTAMP, consultez la section relative à TIMESTAMP.

Les fonctions de date et heure qui utilisent des horodatages UNIX exploitent l'heure Unix. Les fonctions de date et heure renvoient des valeurs basées sur le fuseau horaire UTC.

Syntaxe

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.

CURRENT_DATE()

Renvoie une chaîne lisible indiquant la date actuelle au format %Y-%m-%d.

Exemple :

SELECT CURRENT_DATE();

Renvoie 2013-02-01

CURRENT_TIME()

Renvoie une chaîne lisible indiquant l'heure actuelle du serveur au format %H:%M:%S.

Exemple :

SELECT CURRENT_TIME();

Renvoie 01:32:56

CURRENT_TIMESTAMP()

Renvoie un type de données TIMESTAMP indiquant l'heure actuelle du serveur au format %Y-%m-%d %H:%M:%S.

Exemple :

SELECT CURRENT_TIMESTAMP();

Renvoie 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

Renvoie une chaîne lisible d'un type de données TIMESTAMP au format %Y-%m-%d.

Exemple :

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

Renvoie 2012-10-01

DATE_ADD(<timestamp>,<interval>, <interval_units>)

Ajoute l'intervalle spécifié à un type de données TIMESTAMP. Les valeurs possibles de interval_units sont YEAR, MONTH, DAY, HOUR, MINUTE et SECOND. Si interval est un nombre négatif, l'intervalle est soustrait du type de données TIMESTAMP.

Exemple :

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

Renvoie 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

Renvoie 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

Renvoie le nombre de jours entre deux types de données TIMESTAMP. Le résultat est positif si le premier type de données TIMESTAMP indiqué est ultérieur au deuxième. Sinon, le résultat est négatif.

Exemple :

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

Renvoie : 466

Exemple :

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

Renvoie -466

DAY(<timestamp>)

Renvoie le jour du mois d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 31 inclus.

Exemple :

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 2

DAYOFWEEK(<timestamp>)

Renvoie le jour de la semaine d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 (dimanche) et 7 (samedi) inclus.

Exemple :

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

Renvoie : 2

DAYOFYEAR(<timestamp>)

Renvoie le jour de l'année d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 366 inclus. Le chiffre 1 correspond au 1er janvier.

Exemple :

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

Renvoie : 275

FORMAT_UTC_USEC(<unix_timestamp>)

Renvoie une représentation lisible sous forme de chaîne d'un horodatage UNIX au format YYYY-MM-DD HH:MM:SS.uuuuuu.

Exemple :

SELECT FORMAT_UTC_USEC(1274259481071200);

Renvoie 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

Renvoie l'heure d'un type de données TIMESTAMP sous la forme d'un entier compris entre 0 et 23 inclus.

Exemple :

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 5

MINUTE(<timestamp>)

Renvoie les minutes d'un type de données TIMESTAMP sous la forme d'un nombre entier compris entre 0 et 59 inclus.

Exemple :

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 23

MONTH(<timestamp>)

Renvoie le mois d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 12 inclus.

Exemple :

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 10

MSEC_TO_TIMESTAMP(<expr>)

Convertit un horodatage UNIX exprimé en millisecondes en type de données TIMESTAMP.

Exemple :

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Renvoie 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Renvoie 2012-10-01 01:02:04 UTC

NOW()

Renvoie l'horodatage UNIX actuel en microsecondes.

Exemple :

SELECT NOW();

Renvoie : 1359685811687920

PARSE_UTC_USEC(<date_string>)

Convertit une chaîne de date en horodatage UNIX exprimé en microsecondes. date_string doit être au format YYYY-MM-DD HH:MM:SS[.uuuuuu]. La partie fractionnaire de la seconde peut comporter jusqu'à six chiffres ou être omise.

TIMESTAMP_TO_USEC est une fonction équivalente qui convertit un argument de type de données TIMESTAMP au lieu d'une chaîne de date.

Exemple :

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

Renvoie : 1349056984000000

QUARTER(<timestamp>)

Renvoie le trimestre de l'année d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 4 inclus.

Exemple :

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

Renvoie : 4

SEC_TO_TIMESTAMP(<expr>)

Convertit un horodatage UNIX exprimé en secondes en type de données TIMESTAMP.

Exemple :

SELECT SEC_TO_TIMESTAMP(1355968987);

Renvoie 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Renvoie 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

Renvoie les secondes d'un type de données TIMESTAMP sous la forme d'un entier compris entre 0 et 59 inclus.

Pendant une seconde intercalaire, la plage de l'entier est comprise entre 0 et 60 inclus.

Exemple :

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 48

STRFTIME_UTC_USEC(<unix_timestamp>, <date_format_str>)

Renvoie une chaîne de date lisible au format date_format_str. La chaîne au format date_format_str peut inclure des signes de ponctuation liés à la date (tels que / et -) ainsi que des caractères spéciaux acceptés par la fonction strftime en C++ (tels que %d pour le jour du mois).

Si vous prévoyez de regrouper des données de requête par intervalle (pour obtenir toutes les données d'un mois particulier, par exemple), utilisez les fonctions , qui sont plus performantes.

Exemple :

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

Renvoie 2010-05-19

TIME(<timestamp>)

Renvoie une chaîne lisible d'un type de données TIMESTAMP, au format %H:%M:%S.

Exemple :

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

Renvoie 02:03:04

TIMESTAMP(<date_string>)

Convertit une chaîne de date en type de données TIMESTAMP.

Exemple :

SELECT TIMESTAMP("2012-10-01 01:02:03");

Renvoie 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en millisecondes.

Exemple :

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

Renvoie : 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en secondes.

Exemple :

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

Renvoie : 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

Convertit un type de données TIMESTAMP en horodatage UNIX exprimé en microsecondes.

PARSE_UTC_USEC est une fonction équivalente qui convertit un argument de chaîne de données au lieu d'un type de données TIMESTAMP.

Exemple :

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

Renvoie : 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Convertit un horodatage UNIX exprimé en microsecondes en type de données TIMESTAMP.

Exemple :

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Renvoie 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Renvoie 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

Déplace un horodatage UNIX exprimé en microsecondes au début de la journée à laquelle il se rapporte.

Par exemple, si unix_timestamp se produit le 19 mai à 08h58, la fonction renvoie un horodatage UNIX correspondant au 19 mai à 00h00 (minuit).

Exemple :

SELECT UTC_USEC_TO_DAY(1274259481071200);

Renvoie : 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

Déplace un horodatage UNIX exprimé en microsecondes au début de l'heure à laquelle il se rapporte.

Par exemple, si unix_timestamp se produit à 08h58, la fonction renvoie un horodatage UNIX correspondant au même jour à 08h00.

Exemple :

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Renvoie : 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Décale un horodatage UNIX exprimé en microsecondes au début du mois auquel il se rapporte.

Par exemple, si unix_timestamp se produit le 19 mars, la fonction renvoie un horodatage UNIX correspondant au 1er mars de la même année.

Exemple :

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Renvoie : 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>, <day_of_week>)

Renvoie un horodatage UNIX exprimé en microsecondes représentant un jour de la semaine de l'argument unix_timestamp. Cette fonction exploite deux arguments : un horodatage UNIX exprimé en microsecondes et un jour de la semaine compris entre 0 (dimanche) et 6 (samedi).

Par exemple, si unix_timestamp se produit le vendredi 11 avril 2008 et que vous définissez day_of_week sur 2 (mardi), la fonction renvoie un horodatage UNIX correspondant au mardi 08 avril 2008.

Exemple :

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Renvoie : 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

Renvoie un horodatage UNIX exprimé en microsecondes représentant l'année de l'argument unix_timestamp.

Par exemple, si unix_timestamp se produit en 2010, la fonction renvoie 1274259481071200, qui est la représentation en microsecondes de 2010-01-01 00:00.

Exemple :

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Renvoie : 1262304000000000

WEEK(<timestamp>)

Renvoie la semaine d'un type de données TIMESTAMP sous la forme d'un entier compris entre 1 et 53 inclus.

La semaine commence le dimanche. Par conséquent, si le 1er janvier est un jour autre que dimanche, la semaine 1 comprend moins de sept jours et le premier dimanche de l'année est le premier jour de la semaine 2.

Exemple :

SELECT WEEK(TIMESTAMP('2014-12-31'));

Renvoie : 53

YEAR(<timestamp>)

Renvoie l'année d'un type de données TIMESTAMP.

Exemple :

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

Renvoie : 2012

Exemples avancés

Convertir les résultats d'horodatage entiers dans un format lisible

La requête suivante identifie les cinq moments lors desquels les plus grands nombres de révisions d'articles Wikipedia ont été enregistrés. Pour afficher les résultats dans un format lisible, utilisez la fonction FORMAT_UTC_USEC() de BigQuery, qui exploite un horodatage exprimé en microsecondes comme entrée. Cette requête multiplie les horodatages au format POSIX de Wikipédia (en secondes) par 1 000 000 pour convertir la valeur en microsecondes.

Exemple :

#legacySQL
SELECT
  /* Multiply timestamp by 1000000 and convert */
  /* into a more human-readable format. */
  TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
    AS top_revision_time,
  COUNT (*) AS revision_count
FROM
  [bigquery-public-data:samples.wikipedia];

Renvoie :

+----------------------------+----------------+
|     top_revision_time      | revision_count |
+----------------------------+----------------+
| 2002-02-25 15:51:15.000000 |          20976 |
| 2002-02-25 15:43:11.000000 |          15974 |
| 2010-02-02 03:34:51.000000 |              3 |
| 2010-02-02 01:04:59.000000 |              3 |
| 2010-02-01 23:55:05.000000 |              3 |
+----------------------------+----------------+

Classer les résultats par horodatage

Il peut être utile d'employer des fonctions de date et heure pour regrouper les résultats de requête dans des buckets correspondant à des années, des mois ou des jours spécifiques. L'exemple suivant exploite la fonction UTC_USEC_TO_MONTH() pour afficher le nombre de caractères que chaque contributeur Wikipédia utilise chaque mois dans ses commentaires de révision.

Exemple :

#legacySQL
SELECT
  contributor_username,
  /* Return the timestamp shifted to the
   * start of the month, formatted in
   * a human-readable format. Uses the
   * 'LEFT()' string function to return only
   * the first 7 characters of the formatted timestamp.
   */
  LEFT (FORMAT_UTC_USEC(
    UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
    AS month,
  SUM(LENGTH(comment)) as total_chars_used
FROM
  [bigquery-public-data:samples.wikipedia]
WHERE
  (contributor_username != '' AND
   contributor_username IS NOT NULL)
  AND timestamp > 1133395200
  AND timestamp < 1157068800
GROUP BY
  contributor_username, month
ORDER BY
  total_chars_used DESC;

Renvoie (résultats tronqués) :

+--------------------------------+---------+-----------------------+
|      contributor_username      |  month  | total_chars_used      |
+--------------------------------+---------+-----------------------+
| Kingbotk                       | 2006-08 |              18015066 |
| SmackBot                       | 2006-03 |               7838365 |
| SmackBot                       | 2006-05 |               5148863 |
| Tawkerbot2                     | 2006-05 |               4434348 |
| Cydebot                        | 2006-06 |               3380577 |
etc ...

Fonctions d'adresse IP

Les fonctions d'adresse IP convertissent des adresses IP dans un format lisible ou à partir d'un format lisible.

Syntaxe

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.

FORMAT_IP(integer_value): Convertit les 32 bits les moins significatifs du paramètre integer_value en une chaîne d'adresse IPv4 lisible. Par exemple, FORMAT_IP(1) renverra la chaîne '0.0.0.1'.
PARSE_IP(readable_ip): Convertit une chaîne représentant une adresse IPv4 en un entier sans signature. Par exemple, PARSE_IP('0.0.0.1') renvoie 1. Si la chaîne n'est pas une adresse IPv4 valide, PARSE_IP renvoie NULL.

BigQuery autorise la rédaction d'adresses IPv4 et IPv6 dans des chaînes compactées, en tant que données binaires de 4 ou 16 octets dans l'ordre des octets sur le réseau. Les fonctions décrites ci-dessous permettent de convertir des adresses dans un format lisible ou à partir d'un format lisible. Elles n'acceptent que des champs de chaîne avec des adresses IP.

Syntaxe

FORMAT_PACKED_IP(packed_ip)

Renvoie une adresse IP lisible au format 10.1.5.23 ou 2620:0:1009:1:216:36ff:feef:3f. Exemples :

FORMAT_PACKED_IP('0123456789@ABCDE') renvoie '3031:3233:3435:3637:3839:4041:4243:4445'
FORMAT_PACKED_IP('0123') renvoie '48.49.50.51'

PARSE_PACKED_IP(readable_ip)

Renvoie une adresse IP au format BYTES. Si la chaîne d'entrée n'est pas une adresse IPv4 ou IPv6 valide, PARSE_PACKED_IP renvoie NULL. Exemples :

PARSE_PACKED_IP('48.49.50.51') renvoie 'MDEyMw=='
PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') renvoie 'MDEyMzQ1Njc4OUBBQkNERQ=='

Fonctions JSON

Les fonctions JSON de BigQuery vous permettent d'utiliser des expressions du type JSONPath pour rechercher des valeurs au sein de vos données JSON stockées.

Le stockage de données JSON peut se révéler plus flexible que la déclaration de l'ensemble des champs individuels de votre schéma de table, mais peut entraîner des coûts plus élevés. Lorsque vous sélectionnez des données d'une chaîne JSON, l'analyse de la chaîne complète vous est facturée, ce qui s'avère plus coûteux que de disposer de chaque champ dans une colonne séparée. La requête est également plus lente, car toute la chaîne doit être analysée au moment de la requête. Toutefois, dans le cas de schémas ponctuels ou évoluant rapidement, la flexibilité offerte par JSON peut valoir le coût supplémentaire engendré.

Si vous exploitez des données structurées, utilisez des fonctions JSON plutôt que les fonctions d'expression régulière de BigQuery, car elles sont plus faciles à utiliser.

Syntaxe

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.

JSON_EXTRACT(json, json_path): Sélectionne une valeur dans json en fonction de l'expression JSONPath json_path. json_path doit être une constante de chaîne. Renvoie la valeur au format de chaîne JSON.
JSON_EXTRACT_SCALAR(json, json_path): Sélectionne une valeur dans json en fonction de l'expression JSONPath json_path. json_path doit être une constante de chaîne. Renvoie une valeur JSON scalaire.

Opérateurs logiques

Les opérateurs logiques effectuent des opérations binaires ou ternaires logiques sur des expressions. Les opérations logiques binaires renvoient true ou false. Les opérations logiques ternaires prennent en compte les valeurs NULL et renvoient true, false ou NULL.

Syntaxe

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.

expr AND expr

Renvoie true si les deux expressions sont vraies.
Renvoie false si au moins une des expressions est fausse.
Renvoie NULL si les deux expressions sont nulles ou si une expression est vraie et que l'autre est nulle.

expr OR expr

Renvoie true si l'une des expressions ou les deux sont vraies.
Renvoie false si les deux expressions sont fausses.
Renvoie NULL si les deux expressions sont nulles ou si une expression est fausse et que l'autre est nulle.

NOT expr

Renvoie true si l'expression est fausse.
Renvoie false si l'expression est vraie.
Renvoie NULL si l'expression est nulle.

Vous pouvez utiliser l'opérateur NOT avec d'autres fonctions en tant qu'opérateur de négation. Par exemple, NOT IN(expr1, expr2) ou IS NOT NULL.

Fonctions mathématiques

Les fonctions mathématiques exploitent des arguments numériques et renvoient un résultat numérique. Chaque argument peut être un littéral numérique ou une valeur numérique renvoyée par une requête. Si la fonction mathématique aboutit à un résultat indéterminé, l'opération renvoie la valeur NULL.

Syntaxe

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.

ABS(numeric_expr): Renvoie la valeur absolue de l'argument.
ACOS(numeric_expr): Renvoie l'arc cosinus de l'argument.
ACOSH(numeric_expr): Renvoie l'arc cosinus hyperbolique de l'argument.
ASIN(numeric_expr): Renvoie l'arc sinus de l'argument.
ASINH(numeric_expr): Renvoie l'arc sinus hyperbolique de l'argument.
ATAN(numeric_expr): Renvoie l'arc tangente de l'argument.
ATANH(numeric_expr): Renvoie l'arc tangente hyperbolique de l'argument.
ATAN2(numeric_expr1, numeric_expr2): Renvoie l'arc tangente des deux arguments.
CEIL(numeric_expr): Arrondit l'argument à l'entier supérieur le plus proche et renvoie la valeur arrondie.
COS(numeric_expr): Renvoie le cosinus de l'argument.
COSH(numeric_expr): Renvoie le cosinus hyperbolique de l'argument.
DEGREES(numeric_expr): Renvoie numeric_expr, converti de radians en degrés.
EXP(numeric_expr): Renvoie le résultat de l'élévation de la constante "e" (la base du logarithme naturel) à la puissance de numeric_expr.
FLOOR(numeric_expr): Arrondit l'argument à l'entier inférieur le plus proche et renvoie la valeur arrondie.
LN(numeric_expr) LOG(numeric_expr): Renvoie le logarithme naturel de l'argument.
LOG2(numeric_expr): Renvoie le logarithme en base 2 de l'argument.
LOG10(numeric_expr): Renvoie le logarithme en base 10 de l'argument.
PI(): Renvoie la constante π. La fonction PI() exige des parenthèses pour indiquer qu'il s'agit d'une fonction, mais aucun argument ne doit être placé au sein de celles-ci. Vous pouvez utiliser PI() comme une constante avec des fonctions mathématiques et arithmétiques.
POW(numeric_expr1, numeric_expr2): Renvoie le résultat de l'élévation de numeric_expr1 à la puissance de numeric_expr2.
RADIANS(numeric_expr): Renvoie numeric_expr, en convertissant les degrés en radians. (Notez que π radians correspond à 180 degrés.)
RAND([int32_seed]): Renvoie une valeur flottante aléatoire comprise dans la plage 0,0 <= valeur < 1,0. Chaque valeur int32_seed génère toujours la même séquence de nombres aléatoires dans une requête donnée, tant que vous n'utilisez pas de clause LIMIT. Si int32_seed n'est pas spécifié, BigQuery utilise l'horodatage actuel comme valeur de départ.
ROUND(numeric_expr [, digits]): Arrondit l'argument à l'entier inférieur ou supérieur le plus proche (ou au nombre de chiffres spécifié, si vous en définissez un) et renvoie la valeur arrondie.
SIN(numeric_expr): Renvoie le sinus de l'argument.
SINH(numeric_expr): Renvoie le sinus hyperbolique de l'argument.
SQRT(numeric_expr): Renvoie la racine carrée de l'expression.
TAN(numeric_expr): Renvoie la tangente de l'argument.
TANH(numeric_expr): Renvoie la tangente hyperbolique de l'argument.

Exemples avancés

Requête de cadre de délimitation

La requête suivante renvoie un ensemble de points compris dans un cadre de délimitation rectangulaire centré sur San Francisco (37.46, -122.50).

Exemple :

#legacySQL
SELECT
  year, month,
  AVG(mean_temp) avg_temp,
  MIN(min_temperature) min_temp,
  MAX(max_temperature) max_temp
FROM
  [weather_geo.table]
WHERE
  /* Return values between a pair of */
  /* latitude and longitude coordinates */
  lat / 1000 > 37.46 AND
  lat / 1000 < 37.65 AND
  long / 1000 > -122.50 AND
  long / 1000 < -122.30
GROUP BY
  year, month
ORDER BY
  year, month ASC;

Requête de cercle de délimitation approximatif

Renvoie un ensemble de 100 points maximum compris dans un cercle approximatif déterminé par l'utilisation de la loi des cosinus et centré sur Denver, au Colorado (39.73, -104.98). Cette requête utilise les fonctions mathématiques et trigonométriques de BigQuery, telles que PI(), SIN() et COS().

Comme la Terre n'est pas une sphère absolue et que la longitude et la latitude convergent aux pôles, cette requête renvoie une approximation qui peut être utile pour de nombreux types de données.

Exemple :

#legacySQL
SELECT
  distance, lat, long, temp
FROM
  (SELECT
    ((ACOS(SIN(39.73756700 * PI() / 180) *
           SIN((lat/1000) * PI() / 180) +
           COS(39.73756700 * PI() / 180) *
           COS((lat/1000) * PI() / 180) *
           COS((-104.98471790 -
           (long/1000)) * PI() / 180)) *
           180 / PI()) * 60 * 1.1515)
      AS distance,
     AVG(mean_temp) AS temp,
     AVG(lat/1000) lat, AVG(long/1000) long
FROM
  [weather_geo.table]
WHERE
  month=1 GROUP BY distance)
WHERE
  distance < 100
ORDER BY
  distance ASC
LIMIT 100;

Fonctions d'expression régulière

La bibliothèque re2 permet d'utiliser des expressions régulières dans BigQuery. Consultez la documentation de cette bibliothèque pour en savoir plus sur la syntaxe d'expression régulière à utiliser.

Notez que les expressions régulières sont des correspondances globales. Pour commencer la mise en correspondance au début d'un mot, vous devez utiliser le caractère "^".

Syntaxe

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.

REGEXP_MATCH('str', 'reg_exp')

Renvoie "true" si str correspond à l'expression régulière. Pour mettre en correspondance des chaînes sans expressions régulières, utilisez CONTAINS au lieu de REGEXP_MATCH.

Exemple :

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

Renvoie :

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

REGEXP_EXTRACT('str', 'reg_exp')

Renvoie la partie de str qui correspond au groupe de capture dans l'expression régulière.

Exemple :

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

Renvoie :

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

Renvoie une chaîne dans laquelle toute sous-chaîne de orig_str correspondant à reg_exp est remplacée par replace_str. Par exemple, REGEXP_REPLACE ('Hello', 'lo', 'p') renvoie "Help".

Exemple :

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

Renvoie :

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

Exemples avancés

Filtrer l'ensemble de résultats par correspondance d'expression régulière

Les fonctions d'expression régulière de BigQuery permettent de filtrer les résultats d'une clause WHERE ainsi que d'afficher les résultats d'une clause SELECT. Les exemples suivants combinent ces deux cas d'utilisation d'expressions régulières en une seule requête.

Exemple :

#legacySQL
SELECT
  /* Replace white spaces in the title with underscores. */
  REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions
FROM
  (SELECT title, COUNT(revision_id) as revisions
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    wp_namespace=0
    /* Match titles that start with 'G', end with
     * 'e', and contain at least two 'o's.
     */
    AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
  GROUP BY
    title
  ORDER BY
    revisions DESC
  LIMIT 100);

Utiliser des expressions régulières sur des entiers ou des valeurs flottantes

Bien que les fonctions d'expression régulière de BigQuery n'acceptent que des données de chaîne, il est possible d'utiliser la fonction STRING() pour convertir des entiers ou des valeurs flottantes au format de chaîne. Dans cet exemple, STRING() permet de convertir la valeur entière corpus_date en chaîne qui est ensuite altérée par REGEXP_REPLACE.

Exemple :

#legacySQL
SELECT
  corpus_date,
  /* Cast the corpus_date to a string value  */
  REGEXP_REPLACE(STRING(corpus_date),
    '^16',
    'Written in the sixteen hundreds, in the year \''
    ) AS date_string
FROM [bigquery-public-data:samples.shakespeare]
/* Cast the corpus_date to string, */
/* match values that begin with '16' */
WHERE
  REGEXP_MATCH(STRING(corpus_date), '^16')
GROUP BY
  corpus_date, date_string
ORDER BY
  date_string DESC
LIMIT 5;

Fonctions de chaîne

Les fonctions de chaîne opèrent sur des données de chaîne. Les constantes de chaîne doivent être placées entre guillemets simples ou doubles. Les fonctions de chaîne sont sensibles à la casse par défaut. Vous pouvez ajouter IGNORE CASE à la fin d'une requête pour activer la mise en correspondance non sensible à la casse. IGNORE CASE ne fonctionne qu'avec les caractères ASCII et uniquement au premier niveau de la requête.

Les caractères génériques ne sont pas compatibles avec ces fonctions. Si vous souhaitez employer des fonctionnalités liées aux expressions régulières, utilisez des fonctions d'expression régulière.

Syntaxe

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.

CONCAT('str1', 'str2', '...') str1 + str2 + ...

Renvoie la concaténation de deux chaînes ou plus, ou NULL si l'une des valeurs est nulle. Exemple : si la valeur de str1 est Java et la valeur de str2 est Script, CONCAT renvoie JavaScript.

expr CONTAINS 'str'

Renvoie true si expr contient l'argument de chaîne spécifié. Cette comparaison est sensible à la casse.

INSTR('str1', 'str2')

Renvoie l'index de base 1 de la première occurrence de str2 dans str1, ou renvoie 0 si str2 n'apparaît pas dans str1.

LEFT('str', numeric_expr)

Renvoie les caractères numeric_expr les plus à gauche de str. Si le nombre est plus long que str, la chaîne complète est renvoyée. Exemple : LEFT('seattle', 3) renvoie sea.

LENGTH('str')

Renvoie une valeur numérique indiquant la longueur de la chaîne. Exemple : si la valeur de str est '123456', LENGTH renvoie 6.

LOWER('str')

Renvoie la chaîne d'origine avec tous les caractères en minuscules.

LPAD('str1', numeric_expr, 'str2')

Insère str1 à gauche de str2, en répétant str2 jusqu'à ce que la chaîne de résultat contienne exactement numeric_expr caractères. Exemple : LPAD('1', 7, '?') renvoie ??????1.

LTRIM('str1' [, str2])

Supprime des caractères situés à gauche de str1. Si str2 est omis, LTRIM supprime les espaces se trouvant à gauche de str1. Dans le cas contraire, LTRIM supprime tous les caractères inclus dans str2 qui sont situés à gauche de str1 (en tenant compte de la casse).

Exemples :

SELECT LTRIM("Say hello", "yaS") renvoie " hello".

SELECT LTRIM("Say hello", " ySa") renvoie "hello".

REPLACE('str1', 'str2', 'str3')

Remplace toutes les instances de str2 dans str1 par str3.

RIGHT('str', numeric_expr)

Renvoie les caractères numeric_expr les plus à droite de str. Si le nombre est plus long que la chaîne, la chaîne complète est renvoyée. Exemple : RIGHT('kirkland', 4) renvoie land.

RPAD('str1', numeric_expr, 'str2')

Insère str1 à droite de str2, en répétant str2 jusqu'à ce que la chaîne de résultat contienne exactement numeric_expr caractères. Exemple : RPAD('1', 7, '?') renvoie 1??????.

RTRIM('str1' [, str2])

Supprime les caractères de fin situés à droite de str1. Si str2 est omis, RTRIM supprime les espaces de fin de str1. Dans le cas contraire, RTRIM supprime tous les caractères inclus dans str2 qui sont situés à droite de str1 (en tenant compte de la casse).

Exemples :

SELECT RTRIM("Say hello", "leo") renvoie "Say h".

SELECT RTRIM("Say hello ", " hloe") renvoie "Say".

SPLIT('str' [, 'delimiter'])

Divise une chaîne en sous-chaînes répétées. Si le paramètre delimiter est spécifié, la fonction SPLIT divise str en sous-chaînes en utilisant delimiter comme délimiteur.

SUBSTR('str', index [, max_len])

Renvoie une sous-chaîne de str, à partir de la position index. Si le paramètre facultatif max_len est défini, la longueur maximale de la chaîne renvoyée est de max_len caractères. Sachez que le comptage commence à 1. Par conséquent, le premier caractère de la chaîne se situe à la position 1 (et non zéro). Si index correspond à 5, la sous-chaîne commence par le cinquième caractère de str en partant de la gauche. Si index correspond à -4, la sous-chaîne commence par le quatrième caractère de str en partant de la droite. Exemple : SUBSTR('awesome', -4, 4) renvoie la sous-chaîne some.

UPPER('str')

Renvoie la chaîne d'origine avec tous les caractères en majuscules.

Échapper les caractères spéciaux des chaînes

Pour échapper les caractères spéciaux, employez l'une des méthodes suivantes :

Utilisez la notation '\xDD', où '\x' est suivi de la représentation hexadécimale à deux chiffres du caractère.
Utilisez une barre oblique d'échappement devant les barres obliques, les guillemets simples et les guillemets doubles.
Utilisez des séquences d'échappement semblables à celles du langage C ('\a', '\b', '\f', '\n', '\r', '\t', et '\v') pour les autres caractères.

Voici quelques exemples d'échappement :

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

Fonctions de caractères génériques de table

Les fonctions de caractères génériques de table constituent un moyen pratique d'interroger les données d'un ensemble spécifique de tables. Une fonction de caractère générique de table équivaut à une union séparée par des virgules de toutes les tables qui correspondent à la fonction de caractère générique. Lorsque vous utilisez une fonction de caractère générique de table, BigQuery n'accède qu'aux tables correspondant au caractère générique et ne vous facture que ces dernières. Les fonctions de caractères génériques de table sont spécifiées dans la clause FROM de la requête.

Si vous utilisez des fonctions de caractères génériques de table dans une requête, les fonctions ne doivent plus nécessairement figurer entre parenthèses. Par exemple, certains des exemples suivants utilisent des parenthèses, et d'autres non.

Les résultats mis en cache ne sont pas compatibles avec les requêtes sur plusieurs tables utilisant une fonction de caractère générique, même si l'option Utiliser les résultats mis en cache est cochée. Si vous exécutez plusieurs fois la même requête générique, chaque requête est facturée sur votre compte.

Syntaxe

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

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Interroge les tables quotidiennes qui chevauchent la période comprise entre <timestamp1> et <timestamp2>.

Les noms de table doivent présenter le format suivant : <prefix><day>, où <day> possède le format YYYYMMDD.

Vous pouvez utiliser des fonctions de date et heure pour générer les paramètres d'horodatage. Exemple :

TIMESTAMP('2012-10-01 02:03:04')
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Exemple : obtenir les tables entre deux jours

Cet exemple suppose que les tables suivantes existent :

mydata.people20140325
mydata.people20140326
mydata.people20140327

#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

La requête fait correspondre les tables suivantes :

mydata.people20140325
mydata.people20140326
mydata.people20140327

Exemple : obtenir les tables issues d'une période de deux jours jusqu'à maintenant

Cet exemple suppose que les tables suivantes existent dans un projet nommé myproject-1234 :

mydata.people20140323
mydata.people20140324
mydata.people20140325

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

La requête fait correspondre les tables suivantes :

mydata.people20140323
mydata.people20140324
mydata.people20140325

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

Cette fonction est équivalente à TABLE_DATE_RANGE. La seule différence est que s'il manque une table quotidienne dans la séquence, TABLE_DATE_RANGE_STRICT échoue et renvoie une erreur Not Found: Table <table_name>.

Exemple : erreur liée à une table manquante

Cet exemple suppose que les tables suivantes existent :

people20140325
people20140327

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

L'exemple ci-dessus renvoie une erreur "Not Found" (Introuvable) pour la table "people20140326".

TABLE_QUERY(dataset, expr)

Interroge les tables dont les noms correspondent au paramètre fourni. Le paramètre expr doit être représenté sous forme de chaîne et contenir l'expression à évaluer. Par exemple, 'length(table_id) < 3'.

Exemple : faire correspondre les tables dont le nom contient "oo" et comprend au moins quatre caractères

Cet exemple suppose que les tables suivantes existent :

mydata.boo
mydata.fork
mydata.ooze
mydata.spoon

#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

La requête fait correspondre les tables suivantes :

mydata.ooze
mydata.spoon

Exemple : faire correspondre les tables dont le nom contient "boo", suivi de trois à cinq chiffres

Cet exemple suppose que les tables suivantes existent dans un projet nommé myproject-1234 :

mydata.book4
mydata.book418
mydata.boom12345
mydata.boom123456789
mydata.taboo999

#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

La requête fait correspondre les tables suivantes :

mydata.book418
mydata.boom12345

Fonctions d'URL

Syntaxe

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.

HOST('url_str'): Avec une URL fournie, renvoie le nom d'hôte sous forme de chaîne. Exemple : HOST('http://www.google.com:80/index.html') renvoie "www.google.com".
DOMAIN('url_str'): Avec une URL fournie, renvoie le domaine sous forme de chaîne. Exemple : DOMAIN('http://www.google.com:80/index.html') renvoie "google.com".
TLD('url_str'): Avec une URL fournie, renvoie le domaine de premier niveau ainsi que le domaine de pays de l'URL. Exemple : TLD('http://www.google.com:80/index.html') renvoie '.com'. TLD('http://www.google.co.uk:80/index.html') renvoie ".co.uk".

Remarques :

Ces fonctions ne permettent pas d'effectuer une résolution DNS inverse. Par conséquent, si vous les appelez à l'aide d'une adresse IP, elles renvoient des segments de l'adresse IP plutôt que des segments du nom d'hôte.
N'employez que des minuscules dans les fonctions d'analyse d'URL. Si l'URL contient des majuscules, la fonction renvoie un résultat NULL ou autrement incorrect. Envisagez de transmettre l'entrée à cette fonction via la fonction LOWER() si vos données ont une casse mixte.

Exemple avancé

Analyser des noms de domaine à partir de données d'URL

Cette requête utilise la fonction DOMAIN() pour renvoyer les domaines les plus populaires répertoriés comme pages d'accueil de dépôts sur GitHub. Elle emploie la clause HAVING pour filtrer les enregistrements à l'aide du résultat de la fonction DOMAIN(). Cette fonction est utile pour déterminer les informations de provenance à partir des données d'URL.

Exemples :

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

Renvoie :

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

Pour consulter spécifiquement les informations du domaine de premier niveau, utilisez la fonction TLD(). Cet exemple affiche les domaines de premier niveau les plus fréquents qui ne figurent pas dans une liste d'exemples courants.

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

Renvoie :

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

Fonctions de fenêtrage

Les fonctions de fenêtrage (également appelées fonctions analytiques) permettent d'effectuer des calculs sur un sous-ensemble spécifique, ou "fenêtre", d'un ensemble de résultats. Les fonctions de fenêtrage facilitent la création de rapports comprenant des analyses complexes telles que des moyennes sur une période à ce jour ou des totaux cumulés.

Chaque fonction de fenêtrage nécessite une clause OVER qui spécifie le début et la fin de la fenêtre. Les trois composants de la clause OVER (partitionnement, classement et encadrement) offrent un contrôle supplémentaire sur la fenêtre. Le partitionnement vous permet de répartir les données d'entrée en groupes logiques ayant une caractéristique commune. Le classement vous permet d'organiser les résultats au sein d'une partition. L'encadrement vous permet de créer un cadre de fenêtrage glissant dans une partition mobile par rapport à la ligne actuelle. Vous pouvez configurer la taille du cadre de fenêtrage glissant en fonction d'un certain nombre de lignes ou d'une plage de valeurs (par exemple, un intervalle de temps).

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

Définit la partition de base sur laquelle cette fonction opère. Spécifiez un ou plusieurs noms de colonne séparés par des virgules. Une partition est créée pour chaque ensemble de valeurs distinct pour ces colonnes, tout comme dans la clause GROUP BY. Si PARTITION BY est omis, la partition de base contient toutes les lignes de l'entrée de la fonction de fenêtrage.

La clause PARTITION BY permet également aux fonctions de fenêtrage de partitionner des données et d'exécuter des requêtes parallèles. Si vous souhaitez utiliser une fonction de fenêtrage avec allowLargeResults, ou si vous prévoyez d'appliquer d'autres jointures ou agrégations au résultat de votre fonction de fenêtrage, utilisez PARTITION BY pour exécuter des requêtes parallèles.

Les clauses JOIN EACH et GROUP EACH BY ne peuvent pas être utilisées sur le résultat des fonctions de fenêtrage. Pour générer un grand nombre de résultats de requêtes avec des fonctions de fenêtrage, vous devez utiliser la clause PARTITION BY.

ORDER BY

Trie la partition. Si la clause ORDER BY est absente, l'ordre de tri par défaut n'est pas garanti. Le tri s'effectue au niveau de la partition, avant l'application des clauses de cadre de fenêtrage. Si vous spécifiez une fenêtre RANGE, vous devez ajouter une clause ORDER BY. Le tri par défaut est ASC.

La clause ORDER BY est facultative dans certains cas, mais certaines fonctions de fenêtrage, telles que rank() ou dense_rank(), exigent sa présence.

Si vous utilisez ORDER BY sans spécifier ROWS ni RANGE, ORDER BY suppose que la fenêtre s'étend du début de la partition à la ligne actuelle. En l'absence d'une clause ORDER BY, la fenêtre correspond à la partition entière.

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

Sous-ensemble de la partition sur lequel opérer. Le sous-ensemble peut avoir une taille inférieure ou égale à celle de la partition. Si vous utilisez la clause ORDER BY sans window-frame-clause, le cadre de fenêtrage par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Si vous omettez à la fois ORDER BY et window-frame-clause, le cadre de fenêtrage par défaut est la partition entière.

ROWS : définit une fenêtre en fonction d'une position de ligne par rapport à la ligne actuelle. Par exemple, pour ajouter une colonne indiquant la somme des cinq lignes précédentes de valeurs salariales, vous devez exécuter la requête SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). L'ensemble de lignes comprend généralement la ligne actuelle, mais ce n'est pas obligatoire.
RANGE : définit une fenêtre en fonction d'une plage de valeurs dans une colonne donnée, par rapport à la valeur de cette colonne dans la ligne actuelle. Ne fonctionne qu'avec des nombres et des dates, où les valeurs de date sont des valeurs entières simples (microsecondes depuis l'époque). Les lignes voisines possédant la même valeur sont appelées lignes homologues. Les lignes homologues de la ligne actuelle CURRENT ROW sont incluses dans un cadre de fenêtrage qui spécifie la ligne actuelle CURRENT ROW. Par exemple, si vous indiquez que la fin de la fenêtre correspond à la ligne actuelle CURRENT ROW et que la ligne suivante de la fenêtre a la même valeur, elle sera incluse dans le calcul de la fonction.
BETWEEN <start> AND <end> : plage incluant les lignes de début et de fin. La plage ne doit pas nécessairement inclure la ligne actuelle, mais <start> doit être antérieur ou égal à <end>.
<start> : spécifie le décalage de début pour cette fenêtre par rapport à la ligne actuelle. Les options suivantes sont compatibles :
```
{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
```
où <expr> est un entier positif, PRECEDING indique un numéro de ligne précédente ou une valeur de plage précédente, et FOLLOWING indique un numéro de ligne suivante ou une valeur de plage suivante. UNBOUNDED PRECEDING se réfère à la première ligne de la partition. Si le début est antérieur à la fenêtre, la première ligne de la partition est utilisée.
<end> : spécifie le décalage de fin pour cette fenêtre par rapport à la ligne actuelle. Les options suivantes sont compatibles :
```
{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
```
où <expr> est un entier positif, PRECEDING indique un numéro de ligne précédente ou une valeur de plage précédente, et FOLLOWING indique un numéro de ligne suivante ou une valeur de plage suivante. UNBOUNDED FOLLOWING se réfère à la dernière ligne de la partition. Si la fin est ultérieure à la fin de la fenêtre, la dernière ligne de la partition est utilisée.

Contrairement aux fonctions d'agrégation, qui ne génèrent qu'une ligne de sortie à partir de plusieurs lignes d'entrée, les fonctions de fenêtrage renvoient une ligne de sortie par ligne d'entrée. Cette fonctionnalité facilite la création de requêtes permettant de calculer des totaux cumulés et des moyennes mobiles. Par exemple, la requête suivante renvoie un total cumulé pour un petit ensemble de données constitué de cinq lignes définies par des instructions SELECT :

#legacySQL
SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

Renvoie :

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

L'exemple suivant calcule une moyenne mobile des valeurs de la ligne actuelle et de celles de la ligne la précédant. Le cadre de fenêtrage comprend deux lignes qui se déplacent avec la ligne actuelle.

#legacySQL
SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

Renvoie :

+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

Syntaxe

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.

AVG(numeric_expr) COUNT(*) COUNT([DISTINCT] field) MAX(field) MIN(field) STDDEV(numeric_expr) SUM(field)

Ces fonctions de fenêtrage effectuent des opérations identiques aux fonctions d'agrégation correspondantes, mais sont calculées sur une fenêtre définie par la clause OVER.

Une autre différence majeure réside dans le fait que la fonction COUNT([DISTINCT] field) produit des résultats exacts lorsqu'elle est utilisée en tant que fonction de fenêtrage, avec un comportement semblable à la fonction d'agrégation EXACT_COUNT_DISTINCT().

Dans l'exemple de requête, la clause ORDER BY entraîne le calcul de la fenêtre depuis le début de la partition jusqu'à la ligne actuelle, ce qui génère une somme cumulée pour cette année.

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count

Renvoie :

corpus_date	corpus	word_count	annual_total
0	various	37	37
0	sonnets	157	194
1590	2kinghenryvi	18	18
1590	1kinghenryvi	24	42
1590	3kinghenryvi	40	82

CUME_DIST()

Renvoie un nombre à double précision qui indique la distribution cumulative d'une valeur d'un groupe de valeurs, calculée à l'aide de la formule <number of rows preceding or tied with the current row> / <total rows>. Les valeurs à égalité renvoient la même valeur de distribution cumulative.

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	cume_dist
handkerchief	29	0,20
satisfaction	5	0,4
displeasure	4	0,8
instruments	4	0,8
circumstance	3	1

DENSE_RANK()

Renvoie le rang (sous forme d'entier) d'une valeur d'un groupe de valeurs. Le rang est calculé en fonction des comparaisons avec les autres valeurs du groupe.

Les valeurs à égalité s'affichent comme ayant le même rang. Le rang de la valeur suivante est incrémenté d'une unité. Par exemple, si deux valeurs sont à égalité pour le rang 2, la valeur de rang suivante est 3. Si vous préférez générer un décalage dans le classement, utilisez rank().

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	dense_rank
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	3
circumstance	3	4

FIRST_VALUE(<field_name>)

Renvoie la première valeur de <field_name> dans la fenêtre.

#legacySQL
SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

Renvoie :

word	word_count	fv
imperfectly	1	imperfectly

LAG(<expr>[, <offset>[, <default_value>]])

Permet de lire les données d'une ligne précédente d'une fenêtre. Plus spécifiquement, LAG() renvoie la valeur de <expr> pour la ligne située <offset> lignes avant la ligne actuelle. Si la ligne n'existe pas, la fonction renvoie <default_value>.

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	lag
handkerchief	29	null
satisfaction	5	handkerchief
displeasure	4	satisfaction
instruments	4	displeasure
circumstance	3	instruments

LAST_VALUE(<field_name>)

Renvoie la dernière valeur de <field_name> dans la fenêtre.

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

Renvoie :

word	word_count	lv
imperfectly	1	imperfectly

LEAD(<expr>[, <offset>[, <default_value>]])

Permet de lire les données d'une ligne suivante d'une fenêtre. Plus spécifiquement, LEAD() renvoie la valeur de <expr> pour la ligne située <offset> lignes après la ligne actuelle. Si la ligne n'existe pas, la fonction renvoie <default_value>.

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	lead
handkerchief	29	satisfaction
satisfaction	5	displeasure
displeasure	4	instruments
instruments	4	circumstance
circumstance	3	null

NTH_VALUE(<expr>, <n>)

Renvoie la valeur de <expr> à la position <n> du cadre de fenêtrage, où <n> est un index de base 1.

NTILE(<num_buckets>)

Divise une séquence de lignes en <num_buckets> buckets et attribue un numéro de bucket correspondant (sous forme d'entier) à chaque ligne. La fonction ntile() attribue les numéros de bucket aussi équitablement que possible et renvoie une valeur comprise entre 1 et <num_buckets> pour chaque ligne.

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	ntile
handkerchief	29	1
satisfaction	5	1
displeasure	4	1
instruments	4	2
circumstance	3	2

PERCENT_RANK()

Renvoie le rang de la ligne actuelle par rapport aux autres lignes de la partition. Les valeurs renvoyées sont comprises entre 0 et 1 inclus. La première valeur renvoyée est 0,0.

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	p_rank
handkerchief	29	0,0
satisfaction	5	0,25
displeasure	4	0,5
instruments	4	0,5
circumstance	3	1

PERCENTILE_CONT(<percentile>)

Renvoie une valeur interpolée pouvant être mappée à l'argument de centile par rapport à la fenêtre, après le tri des valeurs par la clause ORDER BY.

<percentile> doit être compris entre 0 et 1.

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	p_cont
handkerchief	29	4
satisfaction	5	4
displeasure	4	4
instruments	4	4
circumstance	3	4

PERCENTILE_DISC(<percentile>)

Renvoie la valeur la plus proche du centile de l'argument sur la fenêtre.

<percentile> doit être compris entre 0 et 1.

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	p_disc
handkerchief	29	4
satisfaction	5	4
displeasure	4	4
instruments	4	4
circumstance	3	4

RANK()

Renvoie le rang (sous forme d'entier) d'une valeur d'un groupe de valeurs. Le rang est calculé en fonction des comparaisons avec les autres valeurs du groupe.

Les valeurs à égalité s'affichent comme ayant le même rang. Le rang de la valeur suivante est incrémenté en fonction du nombre de valeurs à égalité qui la précèdent. Par exemple, si deux valeurs sont à égalité pour le rang 2, la valeur de rang suivante est 4, et non 3. Si vous préférez ne pas générer de décalage dans le classement, utilisez dense_rank().

Cette fonction de fenêtrage requiert ORDER BY dans la clause OVER.

#legacySQL
SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	rank
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	3
circumstance	3	5

RATIO_TO_REPORT(<column>)

Renvoie le ratio de chaque valeur par rapport à la somme des valeurs sous la forme d'un nombre à double précision compris entre 0 et 1.

#legacySQL
SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	r_to_r
handkerchief	29	0.6444444444444445
satisfaction	5	0.1111111111111111
displeasure	4	0.08888888888888889
instruments	4	0.08888888888888889
circumstance	3	0.06666666666666667

ROW_NUMBER()

Renvoie le numéro de ligne actuel du résultat de requête sur la fenêtre, en commençant par 1.

#legacySQL
SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Renvoie :

word	word_count	row_num
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	4
circumstance	3	5

Autres fonctions

Syntaxe

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.

CASE WHEN when_expr1 THEN then_expr1 WHEN when_expr2 THEN then_expr2 ... ELSE else_expr END

Permet de choisir deux expressions alternatives ou plus dans votre requête. Les expressions WHEN doivent être booléennes et toutes les expressions des clauses THEN et ELSE doivent être des types compatibles.

CURRENT_USER()

Renvoie l'adresse e-mail de l'utilisateur exécutant la requête.

EVERY(<condition>)

Renvoie true si condition est vrai pour toutes ses entrées. Lorsqu'elle est employée avec la clause OMIT IF, cette fonction est utile pour les requêtes impliquant des champs répétés.

FROM_BASE64(<str>)

Convertit la chaîne d'entrée encodée en base64 str au format BYTES. Pour convertir une valeur de type BYTES en valeur de type STRING encodée en base64, utilisez TO_BASE64().

HASH(expr)

Calcule et renvoie une valeur de hachage signée de 64 bits des octets du paramètre expr, telle que définie par la bibliothèque CityHash (version 1.0.3). Toutes les expressions de chaîne et d'entier sont compatibles et la fonction respecte le paramètre IGNORE CASE pour les chaînes, renvoyant ainsi des valeurs sans casse mixte.

FARM_FINGERPRINT(expr)

Calcule et renvoie une valeur d'empreinte signée de 64 bits de l'entrée STRING ou BYTES à l'aide de la fonction Fingerprint64 de la bibliothèque Open Source FarmHash. Le résultat renvoyé par cette fonction pour une entrée spécifique ne change jamais et correspond au résultat de la fonction FARM_FINGERPRINT lorsqu'elle utilise le langage SQL standard. Cette fonction respecte le paramètre IGNORE CASE pour les chaînes, renvoyant ainsi des valeurs sans casse mixte.

IF(condition, true_return, false_return)

Renvoie true_return ou false_return, selon que condition est vrai ou faux. Les valeurs renvoyées peuvent être des valeurs littérales ou des valeurs dérivées des champs, mais elles doivent posséder le même type de données. Les valeurs dérivées des champs n'ont pas besoin d'être incluses dans la clause SELECT.

POSITION(field)

Renvoie la position séquentielle de base 1 du paramètre field dans un ensemble de champs répétés.

SHA1(<str>)

Renvoie un hachage SHA1 au format BYTES de la chaîne d'entrée . Vous pouvez convertir le résultat en base64 à l'aide de TO_BASE64(). Par exemple :

#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;

SOME(<condition>)

Renvoie true si condition est vrai pour au moins une de ses entrées. Lorsqu'elle est employée avec la clause OMIT IF, cette fonction est utile pour les requêtes impliquant des champs répétés.

TO_BASE64(<bin_data>)

Convertit l'entrée BYTES bin_data en chaîne encodée en base64. Exemple :

#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;

Pour convertir une chaîne encodée en base64 au format BYTES, utilisez FROM_BASE64().

Exemples avancés

Classer les résultats par catégorie à l'aide d'expressions conditionnelles

La requête suivante utilise un bloc CASE/WHEN pour classer les résultats dans plusieurs catégories "région" en fonction d'une liste d'États. Si l'État n'apparaît pas en tant qu'option dans l'une des instructions WHEN, la valeur d'État par défaut est "None".

Exemple :

#legacySQL
SELECT
  CASE
    WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                   'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
      THEN 'West'
    WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                   'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                   'MD', 'DC', 'DE')
      THEN 'South'
    WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                   'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
      THEN 'Midwest'
    WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                   'RI', 'MA', 'VT', 'NH', 'ME')
      THEN 'Northeast'
    ELSE 'None'
  END as region,
  average_mother_age,
  average_father_age,
  state, year
FROM
  (SELECT
     year, state,
     SUM(mother_age)/COUNT(mother_age) as average_mother_age,
     SUM(father_age)/COUNT(father_age) as average_father_age
   FROM
     [bigquery-public-data:samples.natality]
   WHERE
     father_age < 99
   GROUP BY
     year, state)
ORDER BY
  year
LIMIT 5;

Renvoie :

+--------+--------------------+--------------------+-------+------+
| region | average_mother_age | average_father_age | state | year |
+--------+--------------------+--------------------+-------+------+
| South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
| West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
| West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
| West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
| South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
+--------+--------------------+--------------------+-------+------+

Simuler un tableau croisé dynamique

Utilisez des instructions conditionnelles pour organiser les résultats d'une requête "subselect" en lignes et en colonnes. Dans l'exemple ci-dessous, les résultats d'une recherche visant à identifier les articles Wikipedia les plus révisés commençant par "Google" sont organisés en plusieurs colonnes. Celles-ci comprennent le nombre de révisions si elles remplissent certains critères.

Exemple :

#legacySQL
SELECT
  page_title,
  /* Populate these columns as True or False, */
  /*  depending on the condition */
  IF (page_title CONTAINS 'search',
      INTEGER(total), 0) AS search,
  IF (page_title CONTAINS 'Earth' OR
      page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
FROM
  /* Subselect to return top revised Wikipedia articles */
  /* containing 'Google', followed by additional text. */
  (SELECT
    TOP (title, 5) as page_title,
    COUNT (*) as total
   FROM
     [bigquery-public-data:samples.wikipedia]
   WHERE
     REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
  );

Renvoie :

+---------------+--------+------+
|  page_title   | search | geo  |
+---------------+--------+------+
| Google search |   4261 |    0 |
| Google Earth  |      0 | 3874 |
| Google Chrome |      0 |    0 |
| Google Maps   |      0 | 2617 |
| Google bomb   |      0 |    0 |
+---------------+--------+------+

Sélectionner un échantillon de données aléatoire à l'aide de la fonction HASH

Certaines requêtes peuvent fournir un résultat utile en exploitant un sous-échantillon aléatoire de l'ensemble de résultats. Pour obtenir un échantillonnage aléatoire des valeurs, utilisez la fonction HASH. Celle-ci renvoie les résultats pour lesquels le modulo "n" du hachage est égal à zéro.

Par exemple, la requête suivante identifie le hachage HASH() de la valeur "title" (titre), puis vérifie si le modulo "2" de cette valeur est égal à zéro. Environ 50 % des valeurs devraient alors être marquées comme échantillonnées. Pour échantillonner moins de valeurs, augmentez la valeur de l'opération modulo de "2" à une valeur plus grande. La requête emploie la fonction ABS conjointement à HASH, car la fonction HASH peut renvoyer des valeurs négatives, et l'utilisation de l'opérateur modulo sur une valeur négative produit une valeur négative.

Exemple :
```
#legacySQL
SELECT
  title,
  HASH(title) AS hash_value,
  IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
    AS included_in_sample
FROM
  [bigquery-public-data:samples.wikipedia]
WHERE
  wp_namespace = 0
LIMIT 5;
```

Fonctions et opérateurs de l'ancien SQL

Fonctions et opérateurs compatibles

Syntaxe des requêtes

Clause SELECT

Exemple

Modificateur WITHIN pour les fonctions d'agrégation

Exemple

Clause FROM

Référencer les tables

Exemple

Exemples

Utiliser des sous-requêtes

Virgule en tant qu'opérateur UNION ALL

Fonctions de caractères génériques de table

Opérateur FLATTEN

Opérateur de jointure

Types de jointures

Modificateur EACH

Semi-jointure et anti-jointure

Exemples

Clause WHERE

Exemple

Clause OMIT RECORD IF

Exemple

Clause GROUP BY

Exemple

Modificateur EACH

Fonction ROLLUP

Exemple

Exemple

Clause HAVING

Exemple

Clause ORDER BY

Clause LIMIT

Grammaire des requêtes

Fonctions d'agrégation

Syntaxe

Fonction TOP()

Exemples d'utilisation de TOP()

Exemples avancés

Opérateurs arithmétiques

Syntaxe

Fonctions bit à bit

Syntaxe

Fonctions de cast

Syntaxe

Fonctions de comparaison

Syntaxe

Fonctions de date et heure

Syntaxe

Exemples avancés

Fonctions d'adresse IP

Syntaxe

Syntaxe

Fonctions JSON

Syntaxe

Opérateurs logiques

Syntaxe

Fonctions mathématiques

Syntaxe

Exemples avancés

Fonctions d'expression régulière

Syntaxe

Exemples avancés

Fonctions de chaîne

Syntaxe

Fonctions de caractères génériques de table

Syntaxe

Fonctions d'URL

Syntaxe

Exemple avancé

Fonctions de fenêtrage

Syntaxe

Autres fonctions

Syntaxe

Exemples avancés

Virgule en tant qu'opérateur `UNION ALL`

Modificateur `EACH`

Fonction `ROLLUP`