Daten mit der Pipe-Syntax analysieren

In dieser Anleitung erfahren Sie, wie Sie mit der Pipe-Syntax Abfragen schreiben, um Daten zu analysieren.

Die Pipe-Syntax ist eine Erweiterung von GoogleSQL, die eine lineare Abfragestruktur unterstützt, mit der Sie Abfragen einfacher lesen, schreiben und verwalten können. Die Pipe-Syntax besteht aus dem Pipe-Symbol |>, einem Pipe-Operator namen und Argumenten. Weitere Informationen finden Sie in den folgenden Ressourcen:

• Eine Einführung in die Pipe-Syntax finden Sie unter Mit der Pipe-Abfragesyntax arbeiten.
• Vollständige Syntaxdetails finden Sie in der Referenzdokumentation zur Pipe-Abfragesyntax.

In dieser Anleitung erstellen Sie eine komplexe Abfrage in der Pipe-Syntax mit der öffentlich verfügbaren bigquery-public-data.austin_bikeshare.bikeshare_trips Tabelle, die Daten zu Fahrradtouren enthält.

Ziele

• Tabellendaten ansehen, indem Sie eine Abfrage mit einer FROM Klausel starten.
• Spalten mit dem EXTEND Pipe-Operator hinzufügen.
• Daten nach Tag und Woche mit dem AGGREGATE Pipe-Operator zusammenfassen.
• Daten über ein gleitendes Fenster mit dem CROSS JOIN Pipe-Operator zusammenfassen.
• Daten mit dem WHERE Pipe-Operator filtern.
• Die lineare Abfragestruktur der Pipe-Syntax mit der verschachtelten Abfragestruktur der Standardsyntax bei mehrstufigen Aggregationen vergleichen.

Hinweis

Für den Einstieg in die Verwendung eines öffentlichen BigQuery-Datasets müssen Sie zuerst ein Projekt erstellen oder auswählen. Das erste pro Monat verarbeitete Terabyte Daten ist kostenlos, sodass Sie mit dem Abfragen von öffentlichen Datasets beginnen können, ohne die Abrechnung zu aktivieren. Wenn Sie jedoch beabsichtigen, das kostenlose Kontingent zu überschreiten, müssen Sie die Abrechnung aktivieren.

Melden Sie sich in Ihrem Google Cloud Konto an. Wenn Sie noch kein Konto haben Google Cloud, erstellen Sie ein Konto, um die Leistung unserer Produkte in der Praxis sehen und bewerten zu können. Neukunden erhalten außerdem ein Guthaben von 300 $, um Arbeitslasten auszuführen, zu testen und bereitzustellen.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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 Google Cloud project.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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 Google Cloud project.

1. BigQuery ist in neuen Projekten automatisch aktiviert. So aktivieren Sie BigQuery in einem vorhandenen Projekt:

   Aktivieren Sie die BigQuery API.

   Rollen, die zum Aktivieren von APIs erforderlich sind

   Zum Aktivieren von APIs benötigen Sie die IAM-Rolle „Service Usage-Administrator“ (roles/serviceusage.serviceUsageAdmin), die die Berechtigung serviceusage.services.enable enthält. Weitere Informationen zum Zuweisen von Rollen.

   API aktivieren

Weitere Informationen zu den verschiedenen Möglichkeiten zum Ausführen von Abfragen finden Sie unter Abfrage ausführen.

Tabellendaten ansehen

Führen Sie die folgende Abfrage aus, um alle Daten aus der Tabelle bikeshare_trips abzurufen:

Pipe-Syntax

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

Standard syntax

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

In der Pipe-Syntax kann die Abfrage mit einer FROM Klausel ohne eine SELECT Klausel beginnen, um Tabellenergebnisse zurückzugeben.

Das Ergebnis sieht etwa so aus:

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

Spalten hinzufügen

In der Tabelle bikeshare_trips ist die Spalte start_time ein Zeitstempel. Möglicherweise möchten Sie aber eine Spalte hinzufügen, in der nur das Datum der Tour angezeigt wird. Verwenden Sie den EXTEND Pipe-Operator, um in der Pipe-Syntax eine Spalte hinzuzufügen:

Pipe-Syntax

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

Standard syntax

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

Das Ergebnis sieht etwa so aus:

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

Tägliche Daten zusammenfassen

Sie können nach Datum gruppieren, um die Gesamtzahl der Touren und die verwendeten Fahrräder pro Tag zu ermitteln.

• Verwenden Sie den AGGREGATE Pipe-Operator mit der COUNT Funktion, um die Gesamtzahl der Touren und der verwendeten Fahrräder zu ermitteln.

• Verwenden Sie die Klausel GROUP BY, um die Ergebnisse nach Datum zu gruppieren.

Pipe-Syntax

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;

Standard syntax

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;

Das Ergebnis sieht etwa so aus:

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

Ergebnisse sortieren

Wenn Sie die Ergebnisse in absteigender Reihenfolge nach der Spalte date sortieren möchten, fügen Sie der Klausel GROUP BY das Suffix DESC hinzu:

Pipe-Syntax

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;

Standard syntax

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;

Das Ergebnis sieht etwa so aus:

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

In der Pipe-Syntax können Sie das Sortiersuffix direkt der GROUP BY Klausel hinzufügen, ohne den ORDER BY Pipe-Operator zu verwenden. Das Hinzufügen des Suffixes zur GROUP BY Klausel ist eine von mehreren optionalen Kurzschreibweisen für die Sortierung mit AGGREGATE die von der Pipe-Syntax unterstützt werden. In der Standardsyntax ist dies nicht möglich und Sie müssen die Klausel ORDER BY zum Sortieren verwenden.

Wöchentliche Daten zusammenfassen

Nachdem Sie nun Daten zur Anzahl der täglich verwendeten Fahrräder haben, können Sie Ihre Abfrage erweitern, um die Anzahl der verschiedenen Fahrräder zu ermitteln, die in jedem Siebentagefenster verwendet wurden.

Wenn Sie die Zeilen in Ihrer Tabelle so aktualisieren möchten, dass Wochen anstelle von Tagen angezeigt werden, verwenden Sie die DATE_TRUNC Funktion in der GROUP BY Klausel und legen Sie die Granularität auf WEEK fest:

Pipe-Syntax

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;

Standard syntax

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;

Das Ergebnis sieht etwa so aus:

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

Über ein gleitendes Fenster zusammenfassen

Die Ergebnisse im vorherigen Abschnitt zeigen Touren in einem festen Fenster zwischen Start- und Enddatum, z. B. vom 2024-06-23 bis zum 2024-06-29. Stattdessen möchten Sie möglicherweise Touren in einem gleitenden Fenster sehen, also in einem Zeitraum von sieben Tagen, der sich mit jedem neuen Tag nach vorne verschiebt. Mit anderen Worten: Für ein bestimmtes Datum möchten Sie möglicherweise die Anzahl der Touren und der verwendeten Fahrräder in der folgenden Woche ermitteln.

Wenn Sie ein gleitendes Fenster auf Ihre Daten anwenden möchten, kopieren Sie zuerst jede Tour um sechs zusätzliche aktive Tage ab dem Startdatum nach vorne. Berechnen Sie dann die Daten der aktiven Tage mit der Funktion DATE_ADD. Fassen Sie schließlich die Touren und Fahrrad-IDs für jeden aktiven Tag zusammen.

1. Verwenden Sie die Funktion GENERATE_ARRAY und eine Kreuzverknüpfung, um Ihre Daten nach vorne zu kopieren:

   Pipe-Syntax

   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;
   

   Standard syntax

   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;
   

   Die Funktion GENERATE_ARRAY erstellt ein Array mit sieben Elementen, 0 bis 6. Der Vorgang CROSS JOIN UNNEST erstellt sieben Kopien jeder Zeile mit einer neuen Spalte diff_days, die für jede Zeile einen der Array-Elementwerte von 0 bis 6 enthält. Sie können die Werte von diff_days als Anpassung an das ursprüngliche Datum verwenden, um das Fenster um so viele Tage nach vorne zu verschieben, bis zu sieben Tage nach dem ursprünglichen Datum.

2. Verwenden Sie den Pipe-Operator EXTEND mit der Funktion DATE_ADD, um die berechneten aktiven Daten für Touren zu sehen. Erstellen Sie dazu eine Spalte namens active_date, die das Startdatum plus den Wert in der Spalte diff_days enthält:

   Pipe-Syntax

   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;
   

   Standard syntax

   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)
   

   Eine Tour, die am 2024-05-20 beginnt, gilt beispielsweise auch an jedem Tag bis zum 2024-05-26 als aktiv.

3. Fassen Sie schließlich die Tour-IDs und Fahrrad-IDs zusammen und gruppieren Sie sie nach active_date:

   Pipe-Syntax

   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;
   

   Standard syntax

   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;
   

   Das Ergebnis sieht etwa so aus:

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

Zukünftige Daten filtern

In der vorherigen Abfrage reichen die Daten bis zu sechs Tage über das letzte Datum in Ihren Daten hinaus. Wenn Sie Daten herausfiltern möchten, die über das Ende Ihrer Daten hinausgehen, legen Sie in Ihrer Abfrage ein maximales Datum fest:

1. Fügen Sie einen weiteren Pipe-Operator EXTEND hinzu, der eine Fensterfunktion mit einer OVER-Klausel verwendet, um das maximale Datum in der Tabelle zu berechnen.
2. Verwenden Sie den Pipe-Operator WHERE, um die generierten Zeilen herauszufiltern, die nach dem maximalen Datum liegen.

Pipe-Syntax

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;

Standard syntax

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;

Das Ergebnis sieht etwa so aus:

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

Nächste Schritte

• Weitere Informationen zur Funktionsweise der Pipe-Syntax finden Sie unter Mit der Pipe-Abfragesyntax arbeiten.
• Weitere technische Informationen finden Sie in der Pipe-Abfragesyntax Referenzdokumentation.