Cette page a été traduite par l'API Cloud Translation.

Analyser des données à l'aide de la syntaxe pipe

Ce tutoriel vous explique comment écrire des requêtes à l'aide de la syntaxe de canalisation pour analyser des données.

La syntaxe de canalisation 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. La syntaxe de canalisation se compose du symbole pipe |>, du nom d'un opérateur de canalisation et de tous les arguments. Pour en savoir plus, consultez les ressources suivantes :

Pour une présentation de la syntaxe pipe, consultez Utiliser la syntaxe pipe pour les requêtes.
Pour en savoir plus sur la syntaxe, consultez la documentation de référence sur la syntaxe des requêtes avec des pipes.

Dans ce tutoriel, vous allez créer une requête complexe en syntaxe de canalisation à l'aide de la table bigquery-public-data.austin_bikeshare.bikeshare_trips accessible au public, qui contient des données sur les trajets à vélo.

Objectifs

Affichez les données d'une table en lançant une requête avec une clause FROM.
Ajoutez des colonnes à l'aide de l'opérateur pipe EXTEND.
Agrégez les données par jour et par semaine à l'aide de l'opérateur pipe AGGREGATE.
Agrégez les données sur une fenêtre glissante à l'aide de l'opérateur de canal CROSS JOIN.
Filtrez les données à l'aide de l'opérateur pipe WHERE.
Comparez la structure de requête linéaire de la syntaxe de canalisation à la structure de requête imbriquée de la syntaxe standard lorsque vous effectuez des agrégations à plusieurs niveaux.

Avant de commencer

Pour commencer à utiliser un ensemble de données public BigQuery, vous devez créer ou sélectionner un projet. Le premier téraoctet de données traitées par mois est gratuit. Par conséquent, vous pouvez commencer à interroger des ensembles de données publics sans activer la facturation. Si vous avez l'intention de dépasser le quota offert par la version gratuite, vous devez également activer la facturation.

In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector
Verify that billing is enabled for your Trusted Cloud project.
BigQuery est automatiquement activé dans les nouveaux projets. Pour activer BigQuery dans un projet préexistant,
Enable the BigQuery API.
Enable the API

Pour en savoir plus sur les différentes façons d'exécuter des requêtes, consultez Exécuter une requête.

Afficher les données d'un tableau

Pour récupérer toutes les données de la table bikeshare_trips, exécutez la requête suivante :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Syntaxe standard

SELECT *
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Dans la syntaxe du canal, la requête peut commencer par une clause FROM sans clause SELECT pour renvoyer les résultats du tableau.

Le résultat ressemble à ce qui suit :

+----------+-----------------+---------+-----------+-------------------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Ajouter des colonnes

Dans le tableau bikeshare_trips, la colonne start_time est un code temporel, mais vous pouvez ajouter une colonne qui n'affiche que la date du trajet. Pour ajouter une colonne dans la syntaxe du canal, utilisez l'opérateur de canal EXTEND :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date;

Syntaxe standard

SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Le résultat ressemble à ce qui suit :

+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | date       | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | 2023-02-12 | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | 2023-01-13 | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | 2023-04-20 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Agréger les données quotidiennes

Vous pouvez regrouper les données par date pour connaître le nombre total de trajets effectués et les vélos utilisés chaque jour.

Utilisez l'opérateur pipe AGGREGATE avec la fonction COUNT pour trouver le nombre total de trajets effectués et de vélos utilisés.
Utilisez la clause GROUP BY pour regrouper les résultats par date.

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date;

Syntaxe standard

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date;

Le résultat ressemble à ce qui suit :

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2023-04-20 | 841   | 197            |
| 2023-01-27 | 763   | 148            |
| 2023-06-12 | 562   | 202            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Classer les résultats

Pour trier les résultats par ordre décroissant selon la colonne date, ajoutez le suffixe DESC à la clause GROUP BY :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date DESC;

Syntaxe standard

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Le résultat ressemble à ce qui suit :

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-29 | 395   | 123            |
| 2024-06-28 | 437   | 137            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Dans la syntaxe de canalisation, vous pouvez ajouter le suffixe de tri directement à la clause GROUP BY sans utiliser l'opérateur de canalisation ORDER BY. L'ajout du suffixe à la clause GROUP BY est l'une des nombreuses fonctionnalités de tri abrégées facultatives avec AGGREGATE que la syntaxe du canal prend en charge. Dans la syntaxe standard, cela n'est pas possible et vous devez utiliser la clause ORDER BY pour le tri.

Agréger les données hebdomadaires

Maintenant que vous disposez de données sur le nombre de vélos utilisés chaque jour, vous pouvez développer votre requête pour trouver le nombre de vélos distincts utilisés au cours de chaque période de sept jours.

Pour mettre à jour les lignes de votre tableau afin qu'elles affichent des semaines au lieu de jours, utilisez la fonction DATE_TRUNC dans la clause GROUP BY et définissez la précision sur WEEK :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
    COUNT(*) AS trips,
    COUNT(DISTINCT bike_id) AS distinct_bikes,
GROUP BY DATE_TRUNC(date, WEEK) AS date DESC;

Syntaxe standard

SELECT
  DATE_TRUNC(CAST(start_time AS DATE), WEEK) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes,
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Le résultat ressemble à ce qui suit :

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-23 | 3206  | 213            |
| 2024-06-16 | 3441  | 212            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Agréger sur une fenêtre glissante

Les résultats de la section précédente affichent les trajets dans une période fixe entre les dates de début et de fin, par exemple du 2024-06-23 au 2024-06-29. Vous préférerez peut-être voir les trajets dans une période glissante, sur une période de sept jours qui avance dans le temps à chaque nouveau jour. En d'autres termes, pour une date donnée, vous pouvez souhaiter connaître le nombre de trajets effectués et de vélos utilisés au cours de la semaine suivante.

Pour appliquer une fenêtre glissante à vos données, commencez par copier chaque voyage six jours actifs supplémentaires à partir de sa date de début. Ensuite, calculez les dates des jours actifs à l'aide de la fonction DATE_ADD. Enfin, regroupez les trajets et les ID de vélos pour chaque jour actif.

Pour copier vos données, utilisez la fonction GENERATE_ARRAY et une jointure croisée :
Syntaxe pipe
```
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
```
Syntaxe standard
```
SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
```
La fonction GENERATE_ARRAY crée un tableau avec sept éléments, 0 à 6. L'opération CROSS JOIN UNNEST crée sept copies de chaque ligne, avec une nouvelle colonne diff_days qui contient l'une des valeurs d'élément de tableau de 0 à 6 pour chaque ligne. Vous pouvez utiliser les valeurs diff_days comme ajustement de la date d'origine pour décaler la période du nombre de jours indiqué, jusqu'à sept jours après la date d'origine.

Pour afficher les dates d'activité calculées pour les voyages, utilisez l'opérateur de canalisation EXTEND avec la fonction DATE_ADD pour créer une colonne appelée active_date qui contient la date de début plus la valeur de la colonne diff_days :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date;

Syntaxe standard

SELECT *, DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)

Par exemple, un trajet qui commence le 2024-05-20 est également considéré comme actif chaque jour jusqu'au 2024-05-26.

Enfin, agrégons les ID de trajets et les ID de vélos, puis regroupons-les par active_date :

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
            COUNT(trip_id) AS active_7d_trips
GROUP BY active_date DESC;

Syntaxe standard

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
GROUP BY active_date
ORDER BY active_date DESC;

Le résultat ressemble à ce qui suit :

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-07-06  | 90              | 331             |
| 2024-07-05  | 142             | 726             |
| 2024-07-04  | 186             | 1163            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Filtrer les dates ultérieures

Dans la requête précédente, les dates s'étendent jusqu'à six jours après la dernière date de vos données. Pour exclure les dates qui dépassent la fin de vos données, définissez une date maximale dans votre requête :

Ajoutez un autre opérateur de canalisation EXTEND qui utilise une fenêtrage avec une clause OVER pour calculer la date maximale dans le tableau.
Utilisez l'opérateur pipe WHERE pour filtrer les lignes générées qui dépassent la date maximale.

Syntaxe pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> EXTEND MAX(date) OVER () AS max_date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> WHERE active_date <= max_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
             COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;

Syntaxe standard

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM(
  SELECT *
  FROM (
    SELECT *,
      DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
      MAX(date) OVER () AS max_date
    FROM(
      SELECT *, CAST(start_time AS DATE) AS date,
      FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
      CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days))
  WHERE active_date <= max_date)
GROUP BY active_date
ORDER BY active_date DESC;

Le résultat ressemble à ce qui suit :

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-06-30  | 212             | 3031            |
| 2024-06-29  | 213             | 3206            |
| 2024-06-28  | 219             | 3476            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Étapes suivantes

Pour en savoir plus sur le fonctionnement de la syntaxe des requêtes avec des pipes, consultez Utiliser la syntaxe des requêtes avec des pipes.
Pour en savoir plus sur le plan technique, consultez la documentation de référence sur la syntaxe des requêtes de canalisation.