GoogleSQL : requêtes
GoogleSQL est le dialecte SQL de BigQuery. Les noms de table s'écrivent `PROJECT_ID.DATASET.TABLE` entre accents graves.
Sélection
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Toutes les colonnes sauf certaines | SELECT * EXCEPT (col_a, col_b) FROM DATASET.TABLE |
Évite de lister cinquante colonnes pour en retirer deux |
| Toutes les colonnes, une transformée | SELECT * REPLACE (UPPER(pays) AS pays) FROM DATASET.TABLE |
Même ordre de colonnes, pays en majuscules |
| Filtrer sur une fonction de fenêtre | SELECT ... FROM t QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY maj DESC) = 1 |
Filtre appliqué après le calcul des fenêtres, sans sous-requête ; cf. fonctions de fenêtre |
| Découper une requête en étapes | WITH a AS (SELECT ...), b AS (SELECT ... FROM a) SELECT * FROM b |
CTE (common table expression) : sous-requête nommée, visible dans la suite de la requête |
| Parcourir une hiérarchie | WITH RECURSIVE h AS (SELECT id, parent, 0 AS niv FROM t WHERE parent IS NULL UNION ALL SELECT t.id, t.parent, h.niv + 1 FROM t JOIN h ON t.parent = h.id) SELECT * FROM h |
Terme de base, puis terme récursif en UNION ALL ; échec au-delà de 500 itérations |
| Échantillonner une grosse table | SELECT * FROM DATASET.TABLE TABLESAMPLE SYSTEM (1 PERCENT) |
Environ 1 % des blocs de données lus et facturés ; une table de moins d'environ 1 Go tient souvent en un bloc et est lue entière ; résultat différent à chaque exécution, jamais mis en cache |
Agrégation
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Grouper sans répéter les colonnes | SELECT pays, ville, SUM(ca) FROM t GROUP BY ALL |
Clés de groupement déduites des colonnes non agrégées du SELECT |
| Sous-totaux hiérarchiques | SELECT pays, ville, SUM(ca) FROM t GROUP BY ROLLUP (pays, ville) |
Lignes (pays, ville), (pays) et total général ; NULL dans les colonnes agrégées |
| Combinaisons choisies de regroupements | GROUP BY GROUPING SETS ((pays, canal), (pays), ()) |
Une série de lignes par ensemble ; () donne le total ; CUBE (a, b) produit toutes les combinaisons |
Distinguer un NULL de sous-total |
SELECT GROUPING(ville) AS est_sous_total, ... |
1 si la ligne agrège toutes les villes, 0 sinon |
| Compter sous condition | COUNTIF(statut = 'KO') |
Nombre de lignes où la condition est vraie |
| Somme conditionnelle | SUM(IF(canal = 'web', ca, 0)) |
Plusieurs indicateurs filtrés en un seul passage |
| Tous / au moins un | LOGICAL_AND(paye), LOGICAL_OR(en_retard) |
Booléen par groupe |
| Valeur associée au max | MAX_BY(produit, ca) |
Produit du meilleur chiffre d'affaires ; MIN_BY pour le min |
| N'importe quelle valeur du groupe | ANY_VALUE(libelle) |
Évite de grouper sur une colonne fonctionnellement dépendante |
| Distincts approchés | APPROX_COUNT_DISTINCT(user_id) |
Estimation HyperLogLog++, bien moins coûteuse que COUNT(DISTINCT ...) sur des milliards de lignes |
| Quantiles approchés | APPROX_QUANTILES(latence, 100)[OFFSET(95)] |
95e centile approché |
| Valeurs les plus fréquentes | APPROX_TOP_COUNT(page, 10) |
Tableau de STRUCT {value, count} |
| Lignes en colonnes | SELECT * FROM t PIVOT (SUM(ca) FOR trimestre IN ('T1', 'T2', 'T3', 'T4')) |
Une colonne par valeur listée ; les colonnes non citées servent de clés |
| Colonnes en lignes | SELECT * FROM t UNPIVOT (ca FOR trimestre IN (T1, T2, T3, T4)) |
Une ligne par (clé, trimestre) ; lignes NULL écartées sauf INCLUDE NULLS |
Jointures
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Jointure sur colonnes homonymes | FROM commandes JOIN clients USING (client_id) |
Une seule colonne client_id en sortie |
| Garder les lignes sans correspondance | FROM a LEFT JOIN b ON a.id = b.id |
Colonnes de b à NULL quand rien ne correspond |
| Semi-jointure | SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) |
Lignes de a ayant au moins un b, sans doublon |
| Anti-jointure | SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id) |
Lignes de a sans b ; plus sûr que NOT IN quand b.id contient des NULL |
NULL et erreurs
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Première valeur non nulle | COALESCE(tel_mobile, tel_fixe, 'inconnu') |
Évaluée de gauche à droite |
Remplacer un NULL |
IFNULL(remise, 0) |
Équivaut à COALESCE(remise, 0) |
Transformer une valeur en NULL |
NULLIF(quantite, 0) |
NULL si quantite = 0 : évite la division par zéro |
| Diviser sans erreur | SAFE_DIVIDE(ca, nb) |
NULL si nb = 0 |
| Convertir sans erreur | SAFE_CAST(code AS INT64) |
NULL au lieu d'une erreur si la chaîne n'est pas un entier |
| Rendre une fonction tolérante | SAFE.PARSE_DATE('%d/%m/%Y', d) |
Préfixe SAFE. : NULL au lieu d'une erreur due à la valeur des arguments |
Dates et heures
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Tronquer au mois | DATE_TRUNC(d, MONTH) |
Premier jour du mois ; WEEK(MONDAY), QUARTER, YEAR aussi |
| Écart entre deux dates | DATE_DIFF(fin, debut, DAY) |
Nombre de frontières d'unité franchies (fin - debut) |
| Ajouter une durée | DATE_ADD(d, INTERVAL 7 DAY), TIMESTAMP_SUB(ts, INTERVAL 1 HOUR) |
Date ou horodatage décalé |
| Extraire un élément | EXTRACT(DAYOFWEEK FROM d) |
1 = dimanche … 7 = samedi |
| Lire une date texte | PARSE_DATE('%d/%m/%Y', '31/12/2025') |
DATE '2025-12-31' ; PARSE_TIMESTAMP pour un horodatage |
| Formater un horodatage | FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', ts, 'Europe/Paris') |
Texte à l'heure de Paris ; sans fuseau, UTC |
| Heure locale d'un horodatage | DATETIME(ts, 'Europe/Paris') |
DATETIME sans fuseau, à l'heure locale |
| Horodatage depuis une heure locale | TIMESTAMP(dt, 'Europe/Paris') |
Instant absolu, stocké en UTC |
| Jour civil local | DATE(ts, 'Europe/Paris') |
Date du jour à Paris, qui peut différer de la date UTC |
| Tronquer dans un fuseau | TIMESTAMP_TRUNC(ts, DAY, 'Europe/Paris') |
Minuit heure de Paris |
| Calendrier continu | SELECT jour FROM UNNEST(GENERATE_DATE_ARRAY('2025-01-01', '2025-12-31', INTERVAL 1 DAY)) AS jour |
Une ligne par jour, bornes incluses ; à joindre en LEFT JOIN pour faire apparaître les jours sans données |
TIMESTAMPest un instant absolu (stocké en UTC),DATETIMEune heure murale sans fuseau. Regrouper desTIMESTAMPpar jour sans préciser'Europe/Paris'coupe les journées à minuit UTC.
Chaînes
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Tester une expression régulière | REGEXP_CONTAINS(email, r'@exemple\.fr$') |
Booléen ; préfixe r pour une chaîne brute |
| Extraire un groupe | REGEXP_EXTRACT(url, r'utm_source=([^&]+)') |
Premier groupe capturant, NULL si absent ; REGEXP_EXTRACT_ALL rend un tableau |
| Remplacer par motif | REGEXP_REPLACE(tel, r'[^0-9]', '') |
Chiffres seuls ; \\1 dans le remplacement pour un groupe |
| Découper | SPLIT(tags, ',') |
ARRAY<STRING> ; SPLIT(...)[SAFE_OFFSET(1)] pour un élément |
| Concaténer un groupe | STRING_AGG(nom, ', ' ORDER BY nom) |
Une chaîne par groupe, dans l'ordre donné |
| Recherche insensible à la casse | CONTAINS_SUBSTR(description, 'bigquery') |
Recherche normalisée, sans regex |
Tables génériques et paramètres
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Interroger une famille de tables | SELECT * FROM `PROJECT_ID.DATASET.events_*` WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250131' |
Seules les tables dont le suffixe correspond sont lues ; filtre constant obligatoire pour limiter le coût |
| Paramètre nommé | WHERE pays = @pays AND jour >= @debut |
Valeurs passées par le client, la console ou bq query --parameter=pays::FR --parameter=debut:DATE:2025-01-01 |
| Paramètre tableau | WHERE id IN UNNEST(@ids) |
Liste de valeurs en un seul paramètre (pas depuis la console, qui n'accepte que les types simples) |
Un paramètre remplace une valeur, jamais un identifiant : pas de nom de table ou de colonne en
@param. Une requête utilise soit des paramètres nommés@x, soit des paramètres positionnels?, pas les deux.
Syntaxe pipe
Une requête peut commencer par FROM et enchaîner des opérateurs |>, chacun appliqué au résultat du précédent. Elle se mélange librement avec la syntaxe classique (vues, sous-requêtes, fonctions de table).
FROM `PROJECT_ID.DATASET.ventes`
|> WHERE jour >= '2025-01-01'
|> EXTEND prix * quantite AS ca
|> AGGREGATE SUM(ca) AS ca_total, COUNT(*) AS nb
GROUP BY pays
|> ORDER BY ca_total DESC
|> LIMIT 10
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Ajouter une colonne calculée | |> EXTEND prix * quantite AS ca |
Colonnes existantes plus ca |
| Agréger | |> AGGREGATE SUM(ca) AS total GROUP BY pays |
Clés puis agrégats ; pas de GROUP BY isolé |
| Agréger et trier d'un coup | |> AGGREGATE SUM(ca) AS total GROUP AND ORDER BY pays |
Résultat trié par pays |
| Modifier, retirer, renommer | |> SET ca = ROUND(ca, 2), |> DROP col_tmp, |> RENAME ca AS chiffre |
Les autres colonnes sont conservées |
| Filtrer après agrégation | |> AGGREGATE ... GROUP BY pays |> WHERE total > 1000 |
Remplace HAVING |
| Joindre | |> JOIN DATASET.clients USING (client_id) |
Jointure de la table courante avec une autre |
Maîtriser le coût
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Lire moins d'octets | SELECT col_a, col_b FROM ... plutôt que SELECT * |
Stockage en colonnes : seules les colonnes citées sont facturées ; LIMIT ne réduit pas les octets lus |
| Élaguer les partitions | WHERE jour BETWEEN '2025-01-01' AND '2025-01-31' sur la colonne de partitionnement |
Seules les partitions concernées sont lues ; le filtre doit porter directement sur la colonne, avec des constantes |
| Filtrer sur l'ingestion | WHERE _PARTITIONTIME >= TIMESTAMP('2025-01-01') |
Table partitionnée par date d'ingestion |
| Estimer avant d'exécuter | bq query --dry_run --use_legacy_sql=false 'SELECT ...' |
Octets qui seraient lus, sans rien facturer |
Le validateur de l'éditeur BigQuery Studio affiche aussi le volume estimé avant exécution. Un filtre sur une colonne de clustering réduit les octets lus, mais l'estimation préalable n'en tient pas compte.
Contexte : BigQuery, Organiser BigQuery, bq : requêtes et jobs, fonctions de fenêtre, ARRAY, STRUCT et JSON.