Google verwendet KI-Technologie, um Inhalte in Ihre bevorzugte Sprache zu übersetzen. KI-Übersetzungen können Fehler enthalten.

Daten mit Pipe-Syntax analysieren

Mit der Pipe-Syntax in GoogleSQL können Sie Abfragen linear strukturieren, sodass komplexe Datenanalysen einfacher zu lesen, zu schreiben und zu verwalten sind. In dieser Anleitung erstellen Sie eine Abfrage mit der öffentlich verfügbaren Tabelle bigquery-public-data.austin_bikeshare.bikeshare_trips, um Daten zu Fahrradfahrten zu filtern, zu aggregieren und zu analysieren. Konzeptionelle Hintergrundinformationen und Operatorspezifikationen finden Sie unter Mit Pipe-Abfragesyntax arbeiten und Pipe-Abfragesyntax.

Ziele

Tabellendaten ansehen, indem Sie eine Abfrage mit einer FROM-Klausel starten.
Spalten mit dem Pipe-Operator EXTEND hinzufügen
Daten mit dem Pipe-Operator AGGREGATE nach Tag und Woche aggregieren
Daten über ein gleitendes Fenster mit dem Pipe-Operator CROSS JOIN aggregieren.
Daten mit dem Pipe-Operator WHERE filtern
Vergleichen Sie die lineare Abfragestruktur der Pipe-Syntax mit der verschachtelten Abfragestruktur der Standardsyntax, wenn Sie mehrstufige Aggregationen ausführen.

Hinweis

Für den Einstieg in die Verwendung eines öffentlichen BigQuery-Datasets müssen Sie zuerst ein Projekt erstellen oder auswählen. Das erste Terabyte an verarbeiteten Daten pro Monat ist kostenlos. Sie können also öffentliche Datasets abfragen, 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 mit Google Cloudnoch nicht vertraut sind, erstellen Sie einfach ein Konto, um die Leistungsfähigkeit 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.

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.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

BigQuery ist in neuen Projekten automatisch aktiviert. So aktivieren Sie BigQuery in einem bereits vorhandenen Projekt:
Aktivieren Sie die BigQuery API, falls sie noch nicht aktiviert ist.
Erforderliche Rollen zum Aktivieren von APIs
Zum Aktivieren von APIs benötigen Sie die Berechtigung serviceusage.services.enable. Wenn Sie das Projekt erstellt haben, haben Sie diese Berechtigung wahrscheinlich bereits über die Inhaberrolle (roles/owner). Andernfalls können Sie diese Berechtigung über die Rolle „Service Usage-Administrator“ (roles/serviceusage.serviceUsageAdmin) erhalten. Informationen zum Zuweisen von Rollen
API aktivieren

Weitere Informationen zu den verschiedenen Möglichkeiten, Abfragen auszuführen, 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 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. Sie möchten aber vielleicht eine Spalte hinzufügen, die nur das Datum der Fahrt enthält. Wenn Sie eine Spalte in der Pipe-Syntax hinzufügen möchten, verwenden Sie den Pipe-Operator EXTEND:

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

Tagesdaten aggregieren

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

Verwenden Sie den Pipe-Operator AGGREGATE mit der Funktion COUNT, um die Gesamtzahl der Fahrten und der verwendeten Fahrräder zu ermitteln.
Verwenden Sie die GROUP BY-Klausel, 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 GROUP BY-Anweisung 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 ORDER BY-Klausel für die Sortierung verwenden.

Wöchentliche Daten aggregieren

Nachdem Sie nun Daten zur Anzahl der täglich genutzten Fahrräder haben, können Sie Ihre Abfrage erweitern, um die Anzahl der eindeutigen Fahrräder zu ermitteln, die in jedem siebentägigen Zeitraum genutzt wurden.

Wenn Sie die Zeilen in Ihrer Tabelle so aktualisieren möchten, dass Wochen statt Tage angezeigt werden, verwenden Sie die Funktion DATE_TRUNC 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 Gleitfenster aggregieren

Die Ergebnisse im vorherigen Abschnitt zeigen Fahrten in einem festen Zeitfenster zwischen Start- und Enddatum, z. B. vom 2024-06-23 bis zum 2024-06-29. Stattdessen möchten Sie vielleicht Fahrten in einem gleitenden Fenster über einen Zeitraum von sieben Tagen sehen, der sich mit jedem neuen Tag weiterbewegt. Mit anderen Worten: Für ein bestimmtes Datum möchten Sie vielleicht wissen, wie viele Fahrten in der folgenden Woche unternommen und wie viele Fahrräder genutzt wurden.

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

Wenn Sie Ihre Daten vorwärts kopieren möchten, verwenden Sie die Funktion GENERATE_ARRAY und einen Cross Join:
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 CROSS JOIN UNNEST-Vorgang erstellt sieben Kopien jeder Zeile mit einer neuen diff_days-Spalte, die für jede Zeile einen der Array-Elementwerte von 0 bis 6 enthält. Sie können die diff_days-Werte als Anpassung des ursprünglichen Datums verwenden, um das Fenster um diese Anzahl von Tagen nach vorn zu verschieben, bis zu sieben Tage nach dem ursprünglichen Datum.

Wenn Sie die berechneten aktiven Daten für Fahrten sehen möchten, verwenden Sie den Pipe-Operator EXTEND mit der Funktion DATE_ADD, um eine Spalte namens active_date zu erstellen, 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 Fahrt, die am 2024-05-20 beginnt, gilt beispielsweise auch an jedem Tag bis zum 2024-05-26 als aktiv.

Aggregieren Sie schließlich die Fahrt-IDs und Fahrrad-IDs 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 Datumsangaben filtern

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

Fügen Sie einen weiteren EXTEND-Pipe-Operator hinzu, der eine Fensterfunktion mit einer OVER-Klausel verwendet, um das maximale Datum in der Tabelle zu berechnen.
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 Pipe-Abfragesyntax arbeiten.
Weitere technische Informationen finden Sie in der Referenzdokumentation zur Pipe-Abfragesyntax.