Diese Seite wurde von der Cloud Translation API übersetzt.

Daten mit Pipe-Syntax analysieren

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

Die Pipe-Syntax ist eine Erweiterung von GoogleSQL, die eine lineare Abfragestruktur unterstützt. Damit lassen sich Abfragen leichter lesen, schreiben und verwalten. Die Pipe-Syntax besteht aus dem Pipe-Symbol |>, dem Namen eines Pipe-Operators und allen Argumenten. Weitere Informationen finden Sie in den folgenden Ressourcen:

Eine Einführung in die Pipe-Syntax finden Sie unter Mit der Pipe-Abfragesyntax arbeiten.
Ausführliche Informationen zur Syntax 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.

Lernziele

Wenn Sie Tabellendaten ansehen möchten, beginnen Sie eine Abfrage mit einer FROM-Klausel.
Fügen Sie Spalten mit dem EXTEND-Pipe-Operator hinzu.
Fassen Sie Daten nach Tag und Woche mithilfe des Pipe-Operators AGGREGATE zusammen.
Daten über ein gleitendes Fenster aggregieren, indem Sie den CROSS JOIN-Pipe-Operator verwenden.
Daten mit dem WHERE-Pipe-Operator filtern
Vergleichen Sie die lineare Abfragestruktur der Pipe-Syntax mit der verschachtelten Abfragestruktur der Standardsyntax, wenn Sie Aggregationen auf mehreren Ebenen ausführen.

Hinweise

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.

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 ist in neuen Projekten automatisch aktiviert. So aktivieren Sie BigQuery in einem vorhandenen Projekt:
Enable the BigQuery API.
Enable the API

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 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 Fahrt angezeigt wird. Um eine Spalte in der Pipe-Syntax hinzuzufügen, 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 zusammenfassen

Sie können nach Datum gruppieren, um die Gesamtzahl der Fahrten und die verwendeten Fahrräder pro Tag 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 Pipe-Operator ORDER BY zu verwenden. Das Hinzufügen des Suffixes zur GROUP BY-Klausel ist eine von mehreren optionalen Kürzel-Sortierfunktionen mit AGGREGATE, die von der Pipe-Syntax unterstützt werden. In der Standardsyntax ist das nicht möglich. Sie müssen die ORDER BY-Klausel zum Sortieren verwenden.

Wochendaten aggregieren

Nachdem Sie nun Daten zur Anzahl der Fahrräder haben, die jeden Tag verwendet werden, können Sie Ihre Abfrage erweitern, um die Anzahl der verschiedenen Fahrräder zu ermitteln, die in jedem 7‑Tages-Zeitraum verwendet werden.

Wenn Sie die Zeilen in Ihrer Tabelle so aktualisieren möchten, dass anstelle von Tagen Wochen angezeigt werden, verwenden Sie die Funktion DATE_TRUNC in der GROUP BY-Klausel und legen Sie den Detaillierungsgrad 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 aggregieren

Die Ergebnisse im vorherigen Abschnitt zeigen Fahrten in einem festen Zeitraum 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 nach vorn verschiebt. Mit anderen Worten: Für ein bestimmtes Datum möchten Sie möglicherweise die Anzahl der Fahrten und der verwendeten Fahrräder in der folgenden Woche ermitteln.

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

Verwenden Sie die Funktion GENERATE_ARRAY und einen CROSS JOIN, um Ihre Daten 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. Beim Vorgang CROSS JOIN UNNEST werden sieben Kopien jeder Zeile erstellt. Außerdem wird eine neue Spalte diff_days erstellt, die für jede Zeile einen der Arrayelementwerte aus 0 bis 6 enthält. Sie können die diff_days-Werte als Anpassung für das ursprüngliche Datum verwenden, um das Zeitfenster um so viele Tage 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 Reise, die am 2024-05-20 beginnt, gilt beispielsweise auch an jedem Tag bis zum 2024-05-26 als aktiv.

Aggregieren Sie schließlich die IDs für Fahrten und Fahrräder und gruppieren Sie die Daten 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 Daten 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 der Pipe-Abfragesyntax arbeiten.
Weitere technische Informationen finden Sie in der Referenzdokumentation zur Pipe-Abfragesyntax.