La syntaxe de requête pipe est une extension de GoogleSQL qui accepte une structure de requête linéaire conçue pour faciliter la lecture, l'écriture et la maintenance de vos requêtes. Vous pouvez utiliser la syntaxe de pipe partout où vous écrivez du code GoogleSQL.
La syntaxe de pipe est compatible avec les mêmes opérations que la syntaxe de requête GoogleSQL existante ou la syntaxe standard (par exemple, la sélection, l'agrégation et le regroupement, la jointure et le filtrage), mais les opérations peuvent être appliquées dans n'importe quel ordre, autant de fois que nécessaire. La structure linéaire de la syntaxe pipe vous permet d'écrire des requêtes de sorte que l'ordre de la syntaxe de la requête corresponde à l'ordre des étapes logiques suivies pour créer la table de résultats.
Les requêtes qui utilisent la syntaxe pipe sont facturées, exécutées et optimisées de la même manière que les requêtes équivalentes en syntaxe standard. Lorsque vous écrivez des requêtes avec la syntaxe de pipe, suivez les consignes pour estimer les coûts et optimiser le calcul des requêtes.
La syntaxe standard présente des problèmes qui peuvent rendre la lecture, l'écriture et la maintenance difficiles. Le tableau suivant montre comment la syntaxe de pipe résout ces problèmes :
| Syntaxe standard | Syntaxe Pipe |
|---|---|
| Les clauses doivent apparaître dans un ordre particulier. | Les opérateurs pipe peuvent être appliqués dans n'importe quel ordre. |
| Les requêtes plus complexes, telles que les requêtes avec agrégation multiniveau, nécessitent généralement des CTE ou des sous-requêtes imbriquées. | Les requêtes plus complexes sont généralement exprimées en ajoutant des opérateurs pipe à la fin de la requête. |
Lors de l'agrégation, les colonnes sont répétées dans les clauses SELECT, GROUP BY et ORDER BY. |
Les colonnes ne peuvent être listées qu'une seule fois par agrégation. |
Pour créer une requête complexe étape par étape dans la syntaxe pipe, consultez Analyser des données à l'aide de la syntaxe pipe. Pour en savoir plus sur la syntaxe, consultez la documentation de référence sur la syntaxe des requêtes avec pipe.
Syntaxe de base
Dans la syntaxe de pipe, les requêtes commencent par une requête SQL standard ou une clause FROM. Par exemple, une clause FROM autonome, telle que FROM MyTable, est une syntaxe de pipe valide. Le résultat de la requête SQL standard ou de la table de la clause FROM peut ensuite être transmis en entrée à un symbole de barre verticale, |>, suivi d'un nom d'opérateur de barre verticale et de tous les arguments de cet opérateur. L'opérateur pipe transforme la table d'une manière ou d'une autre, et le résultat de cette transformation peut être transmis à un autre opérateur pipe.
Vous pouvez utiliser autant d'opérateurs pipe que vous le souhaitez dans votre requête pour sélectionner, ordonner, filtrer, joindre ou agréger des colonnes. Les noms des opérateurs de pipe correspondent à leurs équivalents de syntaxe standards et ont généralement le même comportement. La principale différence entre la syntaxe standard et la syntaxe avec pipe réside dans la manière dont vous structurez votre requête. À mesure que la logique exprimée par votre requête devient plus complexe, la requête peut toujours être exprimée sous la forme d'une séquence linéaire d'opérateurs pipe, sans utiliser de sous-requêtes profondément imbriquées, ce qui la rend plus facile à lire et à comprendre.
La syntaxe de pipe présente les caractéristiques clés suivantes :
- Chaque opérateur pipe dans la syntaxe pipe se compose du symbole pipe,
|>, d'un nom d'opérateur et d'arguments :
|> operator_name argument_list - Vous pouvez ajouter des opérateurs pipe à la fin de n'importe quelle requête valide.
- Les opérateurs de pipe peuvent être appliqués dans n'importe quel ordre, autant de fois que nécessaire.
- La syntaxe pipe fonctionne partout où la syntaxe standard est acceptée : dans les requêtes, les vues, les fonctions de valeur de table et d'autres contextes.
- La syntaxe pipe peut être mélangée avec la syntaxe standard dans la même requête. Par exemple, les sous-requêtes peuvent utiliser une syntaxe différente de celle de la requête parente.
- Un opérateur pipe peut voir tous les alias qui existent dans la table précédant le pipe.
- Une requête peut commencer par une clause
FROM, et des opérateurs de barre verticale peuvent éventuellement être ajoutés après la clauseFROM.
Prenons l'exemple de la table suivante :
CREATE OR REPLACE TABLE mydataset.Produce AS (
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
UNION ALL
SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
UNION ALL
SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
UNION ALL
SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);
Les requêtes suivantes contiennent chacune une syntaxe de pipe valide qui montre comment créer une requête de manière séquentielle.
Les requêtes peuvent commencer par une clause FROM et n'ont pas besoin de contenir un symbole de barre verticale :
-- View the table.
FROM mydataset.Produce;
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 7 | fruit |
| apples | 2 | fruit |
| carrots | 0 | vegetable |
| bananas | 15 | fruit |
+---------+-------+-----------*/
Vous pouvez filtrer les résultats à l'aide d'un opérateur pipe WHERE :
-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 7 | fruit |
| apples | 2 | fruit |
| bananas | 15 | fruit |
+---------+-------+-----------*/
Pour effectuer une agrégation, utilisez l'opérateur pipe AGGREGATE, suivi d'un nombre quelconque de fonctions d'agrégation, puis d'une clause GROUP BY. La clause GROUP BY fait partie de l'opérateur pipe AGGREGATE et n'est pas séparée par un symbole pipe (|>).
-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
GROUP BY item;
/*---------+-------------+-----------+
| item | total_sales | num_sales |
+---------+-------------+-----------+
| apples | 9 | 2 |
| bananas | 15 | 1 |
+---------+-------------+-----------*/
Supposons maintenant que vous disposiez de la table suivante, qui contient un ID pour chaque article :
CREATE OR REPLACE TABLE mydataset.ItemData AS (
SELECT 'apples' AS item, '123' AS id
UNION ALL
SELECT 'bananas' AS item, '456' AS id
UNION ALL
SELECT 'carrots' AS item, '789' AS id
);
Vous pouvez utiliser l'opérateur pipe JOIN pour joindre les résultats de la requête précédente à cette table afin d'inclure l'ID de chaque élément :
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
GROUP BY item
|> JOIN mydataset.ItemData USING(item);
/*---------+-------------+-----------+-----+
| item | total_sales | num_sales | id |
+---------+-------------+-----------+-----+
| apples | 9 | 2 | 123 |
| bananas | 15 | 1 | 456 |
+---------+-------------+-----------+-----*/
Principales différences par rapport à la syntaxe standard
La syntaxe Pipe diffère de la syntaxe standard de la manière suivante :
- Les requêtes peuvent commencer par une clause
FROM. - L'opérateur pipe
SELECTn'effectue pas d'agrégation. Vous devez utiliser l'opérateur pipeAGGREGATEà la place. - Le filtrage est toujours effectué avec l'opérateur pipe
WHERE, qui peut être appliqué n'importe où. L'opérateur pipeWHERE, qui remplaceHAVINGetQUALIFY, peut filtrer les résultats des fonctions d'agrégation ou de fenêtrage.
Pour en savoir plus, consultez la liste complète des opérateurs de pipe.
Cas d'utilisation
Voici quelques cas d'utilisation courants de la syntaxe de pipe :
- Analyse ad hoc et création de requêtes incrémentielles : l'ordre logique des opérations facilite l'écriture et le débogage des requêtes. Le préfixe de toute requête jusqu'au symbole de barre verticale
|>est une requête valide, ce qui vous permet d'afficher les résultats intermédiaires d'une requête longue. Les gains de productivité peuvent accélérer le processus de développement dans l'ensemble de votre organisation. - Analyse des journaux : il existe d'autres types de syntaxe de pipe qui sont populaires parmi les utilisateurs de l'analyse des journaux. La syntaxe pipe fournit une structure familière qui simplifie l'intégration de ces utilisateurs à l'analyse de l'observabilité et à BigQuery.
Fonctionnalités supplémentaires de la syntaxe Pipe
À quelques exceptions près, la syntaxe de pipe accepte tous les opérateurs de la syntaxe standard avec la même syntaxe. De plus, la syntaxe de pipe introduit des opérateurs de pipe supplémentaires et utilise une syntaxe modifiée pour les agrégations et les jointures. Les sections suivantes expliquent certains de ces opérateurs. Pour connaître tous les opérateurs compatibles, consultez la liste complète des opérateurs de pipe.
Opérateur pipe EXTEND
L'opérateur pipe EXTEND vous permet d'ajouter des colonnes calculées à la table actuelle. L'opérateur de pipe EXTEND est semblable à l'instruction SELECT *, new_column, mais il vous offre plus de flexibilité pour référencer les alias de colonne.
Prenons l'exemple du tableau suivant, qui contient deux résultats de test pour chaque personne :
CREATE OR REPLACE TABLE mydataset.Scores AS (
SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
UNION ALL
SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);
/*---------+--------+--------+-----------------+
| student | score1 | score2 | points_possible |
+---------+--------+--------+-----------------+
| Alex | 9 | 10 | 10 |
| Dana | 5 | 7 | 10 |
+---------+--------+--------+-----------------*/
Supposons que vous souhaitiez calculer le score brut moyen et le pourcentage moyen obtenus par chaque élève au test. Dans la syntaxe standard, les colonnes ultérieures d'une instruction SELECT n'ont pas de visibilité sur les alias précédents. Pour éviter une sous-requête, vous devez répéter l'expression pour la moyenne :
SELECT student,
(score1 + score2) / 2 AS average_score,
(score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;
L'opérateur pipe EXTEND peut référencer des alias précédemment utilisés, ce qui rend la requête plus facile à lire et moins sujette aux erreurs :
FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;
/*---------+---------------+-----------------+
| student | average_score | average_percent |
+---------+---------------+-----------------+
| Alex | 9.5 | .95 |
| Dana | 6.0 | 0.6 |
+---------+---------------+-----------------*/
Opérateur pipe SET
L'opérateur pipe SET vous permet de remplacer la valeur des colonnes de la table actuelle. L'opérateur pipe SET est semblable à l'instruction SELECT
* REPLACE (expression AS column). Vous pouvez référencer la valeur d'origine en qualifiant le nom de la colonne avec un alias de table.
FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;
/*---+---+
| x | y |
+---+---+
| 6 | 5 |
+---+---*/
Opérateur pipe DROP
L'opérateur pipe DROP vous permet de supprimer des colonnes de la table actuelle. L'opérateur pipe DROP est semblable à l'instruction SELECT *
EXCEPT(column). Une fois qu'une colonne est supprimée, vous pouvez toujours référencer la valeur d'origine en qualifiant le nom de la colonne avec un alias de table.
FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;
/*---+
| y |
+---+
| 2 |
+---*/
Opérateur pipe RENAME
L'opérateur pipe RENAME vous permet de renommer les colonnes de la table actuelle. L'opérateur pipe RENAME est semblable à l'instruction SELECT *
EXCEPT(old_column), old_column AS new_column.
FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;
/*---+---+---+
| x | w | z |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---*/
Opérateur pipe AGGREGATE
Pour effectuer une agrégation dans la syntaxe de pipe, utilisez l'opérateur de pipe AGGREGATE, suivi d'un nombre quelconque de fonctions d'agrégation, puis d'une clause GROUP BY. Vous n'avez pas besoin de répéter les colonnes dans une clause SELECT.
Les exemples de cette section utilisent la table Produce :
CREATE OR REPLACE TABLE mydataset.Produce AS (
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
UNION ALL
SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
UNION ALL
SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
UNION ALL
SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);
/*---------+-------+-----------+
| item | sales | category |
+---------+-------+-----------+
| apples | 7 | fruit |
| apples | 2 | fruit |
| carrots | 0 | vegetable |
| bananas | 15 | fruit |
+---------+-------+-----------*/
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
GROUP BY item, category;
/*---------+-----------+-------+-------------+
| item | category | total | num_records |
+---------+-----------+-------+-------------+
| apples | fruit | 9 | 2 |
| carrots | vegetable | 0 | 1 |
| bananas | fruit | 15 | 1 |
+---------+-----------+-------+-------------*/
Si vous êtes prêt à trier vos résultats immédiatement après l'agrégation, vous pouvez marquer les colonnes de la clause GROUP BY que vous souhaitez trier avec ASC ou DESC. Les colonnes non marquées ne sont pas triées.
Si vous souhaitez trier toutes les colonnes, vous pouvez remplacer la clause GROUP BY par une clause GROUP AND ORDER BY, qui trie toutes les colonnes par ordre croissant par défaut. Vous pouvez spécifier DESC après les colonnes que vous souhaitez trier par ordre décroissant. Par exemple, les trois requêtes suivantes sont équivalentes :
-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
GROUP BY category, item
|> ORDER BY category DESC, item;
-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
GROUP BY category DESC, item ASC;
-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
GROUP AND ORDER BY category DESC, item;
L'avantage d'utiliser une clause GROUP AND ORDER BY est que vous n'avez pas à répéter les noms de colonnes à deux endroits.
Pour effectuer une agrégation de table complète, utilisez GROUP BY() ou omettez complètement la clause GROUP BY :
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;
/*-------+-------------+
| total | num_records |
+-------+-------------+
| 24 | 4 |
+-------+-------------*/
Opérateur pipe JOIN
L'opérateur de jointure JOIN vous permet de joindre la table actuelle à une autre table et accepte les opérations de jointure standards, y compris CROSS, INNER, LEFT, RIGHT et FULL.
Les exemples suivants font référence aux tables Produce et ItemData :
CREATE OR REPLACE TABLE mydataset.Produce AS (
SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
UNION ALL
SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
UNION ALL
SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
UNION ALL
SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);
CREATE OR REPLACE TABLE mydataset.ItemData AS (
SELECT 'apples' AS item, '123' AS id
UNION ALL
SELECT 'bananas' AS item, '456' AS id
UNION ALL
SELECT 'carrots' AS item, '789' AS id
);
L'exemple suivant utilise une clause USING et évite toute ambiguïté de colonne :
FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';
/*--------+-------+----------+-----+
| item | sales | category | id |
+--------+-------+----------+-----+
| apples | 2 | fruit | 123 |
| apples | 7 | fruit | 123 |
+--------+-------+----------+-----*/
Pour référencer des colonnes dans la table actuelle, par exemple pour lever l'ambiguïté des colonnes dans une clause ON, vous devez attribuer un alias à la table actuelle à l'aide de l'opérateur pipe AS. Vous pouvez éventuellement attribuer un alias à la table jointe. Vous pouvez référencer les deux alias après les opérateurs pipe suivants :
FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;
/*---------+-------+-----+
| item | sales | id |
+---------+-------+-----+
| bananas | 15 | 123 |
+---------+-------+-----*/
Le côté droit de la jointure n'a pas de visibilité sur le côté gauche de la jointure, ce qui signifie que vous ne pouvez pas joindre la table actuelle à elle-même. Par exemple, la requête suivante échoue :
-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);
Pour effectuer une autojointure avec une table modifiée, vous pouvez utiliser une expression de table courante (CTE) dans une clause WITH.
WITH cte_table AS (
FROM mydataset.Produce
|> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);
Exemple
Prenons l'exemple de la table suivante, qui contient des informations sur les commandes des clients :
CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
UNION ALL
SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
UNION ALL
SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
UNION ALL
SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
UNION ALL
SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
UNION ALL
SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
UNION ALL
SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);
Supposons que vous souhaitiez connaître, pour chaque État et chaque type d'article, le montant moyen dépensé par les clients réguliers. Vous pouvez écrire la requête de la manière suivante :
SELECT state, item_type, AVG(total_cost) AS average
FROM
(
SELECT
SUM(cost) AS total_cost,
customer_id,
state,
item_type,
COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
FROM mydataset.CustomerOrders
GROUP BY customer_id, state, item_type
QUALIFY num_orders > 1
)
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;
Si vous lisez la requête de haut en bas, vous rencontrez la colonne total_cost avant qu'elle ne soit définie. Même dans la sous-requête, vous lisez les noms des colonnes avant de voir de quelle table elles proviennent.
Pour comprendre cette requête, il faut la lire de l'intérieur vers l'extérieur. Les colonnes state et item_type sont répétées plusieurs fois dans les clauses SELECT et GROUP BY, puis à nouveau dans la clause ORDER BY.
La requête équivalente suivante est écrite à l'aide de la syntaxe de pipe :
FROM mydataset.CustomerOrders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;
/*-------+------------+---------+
| state | item_type | average |
+-------+------------+---------+
| WA | clothing | 35.0 |
| WA | food | 3.0 |
| NY | clothing | 16.0 |
| NY | housewares | 22.0 |
+-------+------------+---------*/
Avec la syntaxe de pipe, vous pouvez écrire la requête de manière à suivre les étapes logiques que vous pourriez envisager pour résoudre le problème d'origine. Les lignes de syntaxe de la requête correspondent aux étapes logiques suivantes :
- Commençons par la table des commandes client.
- Découvrez combien chaque client a dépensé pour chaque type d'article par État.
- Comptez le nombre de commandes pour chaque client.
- Limitez les résultats aux clients réguliers.
- Trouvez le montant moyen dépensé par les clients réguliers pour chaque État et type d'article.
Limites
- Vous ne pouvez pas inclure de clause de confidentialité différentielle dans une instruction
SELECTaprès un opérateur pipe. Utilisez plutôt une clause de confidentialité différentielle dans la syntaxe standard et appliquez des opérateurs pipe après la requête.