Utiliser la syntaxe de requête avec pipe

Pour inscrire un projet à la version preview de la syntaxe des pipes, remplissez le formulaire d'inscription à la syntaxe des pipes.

La syntaxe des requêtes avec pipe est une extension de GoogleSQL qui prend en charge 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 des pipes partout où vous écrivez GoogleSQL.

La syntaxe des pipes accepte les mêmes opérations que la syntaxe de requête GoogleSQL 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 et un nombre illimité de fois. La structure linéaire de la syntaxe des pipes 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 de pipe sont tarifées, exécutées et optimisées de la même manière que leurs requêtes équivalentes en syntaxe standard. Lorsque vous écrivez des requêtes avec la syntaxe du pipe, suivez les consignes pour estimer les coûts et optimiser le calcul des requêtes.

La syntaxe standard souffre de 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 du pipe
Les clauses doivent apparaître dans un ordre particulier. Les opérateurs de 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 en savoir plus sur la syntaxe, consultez la documentation de référence sur la syntaxe des requêtes avec barre oblique.

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 pipe, |>, suivi d'un nom d'opérateur de pipe et d'éventuels arguments pour cet opérateur. L'opérateur de barre verticale 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 de barre verticale.

Vous pouvez utiliser autant d'opérateurs pipe que vous le souhaitez dans votre requête pour effectuer des opérations telles que sélectionner, trier, filtrer, joindre ou agréger des colonnes. Les noms des opérateurs de pipe correspondent à leurs homologues de syntaxe standard 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 façon dont vous structurez votre requête. À mesure que la logique exprimée par votre requête devient plus complexe, elle peut toujours être exprimée sous la forme d'une séquence linéaire d'opérateurs de 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 de pipe se compose du symbole de pipe, |>, d'un nom d'opérateur et d'éventuels 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 et autant de fois que nécessaire.
  • La syntaxe des pipes fonctionne partout où la syntaxe standard est prise en charge: dans les requêtes, les vues, les fonctions de table et d'autres contextes.
  • La syntaxe des pipes peut être mélangée à 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 de pipe peut voir tous les alias qui existent dans le tableau précédant le pipe.
  • Une requête peut commencer par une clause FROM, et des opérateurs de pipe peuvent éventuellement être ajoutés après la clause FROM.

Prenons l'exemple du tableau suivant:

CREATE OR REPLACE TABLE 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 ne doivent pas contenir de symbole de pipe:

-- View the table.
FROM Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Vous pouvez filtrer à l'aide d'un opérateur de pipe WHERE:

-- Filter items with no sales.
FROM 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 de 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 de barre verticale AGGREGATE et n'est pas séparée par un symbole de barre verticale (|>).

-- Compute total sales by item.
FROM 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 que vous disposiez du tableau suivant, qui contient un ID pour chaque élément:

CREATE OR REPLACE TABLE 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 de 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 Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN 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 sur les points suivants:

  • Les requêtes peuvent commencer par une clause FROM.
  • L'opérateur pipe SELECT n'effectue pas d'agrégation. Vous devez utiliser l'opérateur de pipe AGGREGATE à la place.
  • Le filtrage est toujours effectué avec l'opérateur de pipe WHERE, qui peut être appliqué n'importe où. L'opérateur de pipe WHERE, qui remplace HAVING et QUALIFY, 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 du pipe:

  • Analyse ponctuelle et création incrémentielle de requêtes : l'ordre logique des opérations facilite l'écriture et le débogage des requêtes. Le préfixe de n'importe quelle requête jusqu'à un symbole de pipe |> est une requête valide, ce qui vous permet d'afficher les résultats intermédiaires dans une requête longue. Les gains de productivité peuvent accélérer le processus de développement au sein de votre organisation.
  • Analyse des journaux: il existe d'autres types de syntaxe semblables à des pipes qui sont populaires auprès des utilisateurs de l'analyse des journaux. La syntaxe des pipes fournit une structure familière qui simplifie l'intégration de ces utilisateurs à et .

Fonctionnalités supplémentaires de la syntaxe Pipe

À quelques exceptions près, la syntaxe du pipe accepte tous les opérateurs que la syntaxe standard accepte avec la même syntaxe. De plus, la syntaxe de pipe introduit les opérateurs de pipe suivants.

Opérateur pipe EXTEND

L'opérateur de barre verticale 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 des alias de colonne.

Prenons l'exemple du tableau suivant, qui contient deux notes de test pour chaque personne:

CREATE OR REPLACE TABLE 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 la note brute moyenne et le pourcentage moyen obtenu par chaque élève à l'examen. 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 Scores;

L'opérateur de pipe EXTEND peut faire référence à des alias précédemment utilisés, ce qui rend la requête plus facile à lire et moins sujette aux erreurs:

FROM 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 de pipe SET vous permet de remplacer la valeur des colonnes dans le tableau actuel. L'opérateur pipe SET est semblable à l'instruction SELECT * REPLACE (expression AS column). Vous pouvez faire référence à 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 une colonne supprimée, vous pouvez toujours faire référence à 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 de 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 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 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 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 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 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 besoin de répéter les noms de colonnes à deux endroits.

Pour effectuer une agrégation complète de la table, utilisez GROUP BY() ou omettez complètement la clause GROUP BY:

FROM Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

Opérateur pipe JOIN

L'opérateur de pipe JOIN vous permet de joindre la table actuelle à une autre table et est compatible avec 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 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 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 l'ambiguïté des colonnes:

FROM Produce
|> JOIN `ItemData` USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

Pour faire référence aux colonnes de la table actuelle, par exemple pour distinguer les colonnes d'une clause ON, vous devez attribuer un alias à la table actuelle à l'aide de l'opérateur de pipe AS. Vous pouvez éventuellement attribuer un alias à la table jointe. Vous pouvez faire référence aux deux alias après les opérateurs de pipe suivants:

FROM Produce
|> AS produce_table
|> JOIN `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'est pas visible sur le côté gauche de la jointure, ce qui signifie que vous ne pouvez pas joindre le tableau actuel à lui-même. Par exemple, la requête suivante échoue:

-- This query doesn't work.
FROM Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Pour effectuer une jointure automatique avec une table modifiée, vous pouvez utiliser une expression de table courante (CTE) dans une clause WITH.

WITH cte_table AS (
  FROM Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Exemple

Prenons l'exemple du tableau suivant contenant des informations sur les commandes des clients:

CREATE OR REPLACE TABLE 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 comme suit:

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 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 n'ait été 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, vous devez la lire de l'intérieur vers l'extérieur. Les colonnes state et item_type sont répétées de nombreuses 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 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 pour suivre les étapes logiques que vous pourriez envisager de suivre pour résoudre le problème d'origine. Les lignes de syntaxe de la requête correspondent aux étapes logiques suivantes:

  • Commencez par la table des commandes des clients.
  • 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.
  • Déterminez le montant moyen que les clients réguliers dépensent pour chaque État et chaque type d'article.

Limites

  • Vous ne pouvez pas inclure de clause de confidentialité différentielle dans une instruction SELECT après un opérateur pipe. Utilisez plutôt une clause de confidentialité différentielle dans la syntaxe standard et appliquez des opérateurs de pipe après la requête.
  • Vous ne pouvez pas utiliser une fenêtre nommée dans la syntaxe de pipe.