Mit der Pipe-Abfragesyntax arbeiten

Wenn Sie ein Projekt für die Vorabversion der Pipe-Syntax registrieren möchten, füllen Sie das Registrierungsformular für die Pipe-Syntax aus.

Die Pipe-Abfragesyntax ist eine Erweiterung von GoogleSQL, die eine lineare Abfragestruktur unterstützt, mit der Abfragen leichter zu lesen, zu schreiben und zu verwalten sind. Sie können die Pipe-Syntax überall dort verwenden, wo Sie GoogleSQL schreiben.

Die Pipe-Syntax unterstützt dieselben Vorgänge wie die vorhandene GoogleSQL-Abfragesyntax oder Standardsyntax, z. B. Auswahl, Aggregation und Gruppierung, Zusammenführen und Filtern. Die Vorgänge können jedoch in beliebiger Reihenfolge und beliebig oft angewendet werden. Dank der linearen Struktur der Pipe-Syntax können Sie Abfragen so schreiben, dass die Reihenfolge der Abfragesyntax der Reihenfolge der logischen Schritte entspricht, die zum Erstellen der Ergebnistabelle ausgeführt werden.

Abfragen mit Pipe-Syntax werden auf die gleiche Weise berechnet, ausgeführt und optimiert wie entsprechende Abfragen mit Standardsyntax. Wenn Sie Abfragen mit Pipe-Syntax schreiben, folgen Sie den Richtlinien, um Kosten zu schätzen und die Abfrageberechnung zu optimieren.

Die Standardsyntax weist Probleme auf, die das Lesen, Schreiben und Verwalten erschweren können. In der folgenden Tabelle wird gezeigt, wie diese Probleme mithilfe der Pipe-Syntax behoben werden:

Standard syntax Pipe-Syntax
Die Klauseln müssen in einer bestimmten Reihenfolge erscheinen. Pipe-Operatoren können in beliebiger Reihenfolge angewendet werden.
Komplexere Abfragen, z. B. Abfragen mit mehrstufiger Aggregation, erfordern in der Regel CTEs oder verschachtelte Unterabfragen. Komplexere Abfragen werden in der Regel durch das Hinzufügen von Pipe-Operatoren am Ende der Abfrage ausgedrückt.
Bei der Aggregation werden Spalten in den Klauseln SELECT, GROUP BY und ORDER BY wiederholt. Spalten können pro Aggregation nur einmal aufgeführt werden.

Ausführliche Informationen zur Syntax finden Sie in der Referenzdokumentation zur Pipe-Abfragesyntax.

Grundlegende Syntax

Bei der Pipe-Syntax beginnen Abfragen mit einer Standard-SQL-Abfrage oder einer FROM-Klausel. Eine eigenständige FROM-Klausel wie FROM MyTable ist beispielsweise eine gültige Pipe-Syntax. Das Ergebnis der Standard-SQL-Abfrage oder die Tabelle aus der FROM-Klausel kann dann als Eingabe an ein Pipesymbol |> gesendet werden, gefolgt vom Namen des Pipe-Operators und allen Argumenten für diesen Operator. Der Pipe-Operator transformiert die Tabelle auf eine bestimmte Weise und das Ergebnis dieser Transformation kann an einen anderen Pipe-Operator übergeben werden.

Sie können beliebig viele Pipe-Operatoren in Ihrer Abfrage verwenden, um beispielsweise Spalten auszuwählen, zu sortieren, zu filtern, zusammenzuführen oder zu aggregieren. Die Namen der Pipe-Operatoren stimmen mit ihren Standardsyntax-Entsprechungen überein und haben im Allgemeinen dasselbe Verhalten. Der Hauptunterschied zwischen Standardsyntax und Pipe-Syntax besteht in der Strukturierung der Abfrage. Auch wenn die Logik Ihrer Abfrage komplexer wird, kann sie weiterhin als lineare Abfolge von Pipe-Operatoren ausgedrückt werden, ohne dass tief verschachtelte Unterabfragen verwendet werden müssen. Das erleichtert das Lesen und Verstehen.

Die Pipe-Syntax hat die folgenden Hauptmerkmale:

  • Jeder Pipe-Operator in der Pipe-Syntax besteht aus dem Pipe-Symbol |>, einem Operatornamen und beliebigen Argumenten:
    |> operator_name argument_list
  • Pipe-Operatoren können am Ende jeder gültigen Abfrage eingefügt werden.
  • Pipe-Operatoren können beliebig oft und in beliebiger Reihenfolge angewendet werden.
  • Die Pipe-Syntax funktioniert überall dort, wo die Standardsyntax unterstützt wird: in Abfragen, Ansichten, tabellenwertigen Funktionen und anderen Kontexten.
  • Die Pipe-Syntax kann in derselben Abfrage mit der Standardsyntax kombiniert werden. Unterabfragen können beispielsweise eine andere Syntax als die übergeordnete Abfrage verwenden.
  • Ein Pipe-Operator kann jeden Alias sehen, der in der Tabelle vor dem Pipe-Operator vorhanden ist.
  • Eine Abfrage kann mit einer FROM-Klausel beginnen. Nach der FROM-Klausel können optional Pipe-Operatoren hinzugefügt werden.

Betrachten Sie die folgende Tabelle:

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);

Die folgenden Abfragen enthalten jeweils einen gültigen Pipe-Syntax, der zeigt, wie Sie eine Abfrage sequenziell erstellen können.

Abfragen können mit einer FROM-Klausel beginnen und müssen kein Pipe-Symbol enthalten:

-- View the table.
FROM Produce;

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

Sie können mit dem WHERE-Pipe-Operator filtern:

-- Filter items with no sales.
FROM Produce
|> WHERE sales > 0;

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

Verwenden Sie zum Ausführen einer Aggregation den AGGREGATE-Pipe-Operator, gefolgt von einer beliebigen Anzahl von Aggregatfunktionen und einer GROUP BY-Klausel. Die GROUP BY-Klausel ist Teil des AGGREGATE-Pipe-Operators und wird nicht durch ein Pipe-Symbol (|>) getrennt.

-- 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         |
 +---------+-------------+-----------*/

Angenommen, Sie haben die folgende Tabelle mit einer ID für jeden Artikel:

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
);

Mit dem JOIN-Pipe-Operator können Sie die Ergebnisse der vorherigen Abfrage mit dieser Tabelle zusammenführen, um die ID jedes Artikels einzubeziehen:

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 |
 +---------+-------------+-----------+-----*/

Wichtige Unterschiede zur Standardsyntax

Die Pipe-Syntax unterscheidet sich in folgenden Punkten von der Standardsyntax:

  • Abfragen können mit einer FROM-Klausel beginnen.
  • Der Pipe-Operator SELECT führt keine Aggregation durch. Verwenden Sie stattdessen den AGGREGATE-Pipe-Operator.
  • Die Filterung erfolgt immer mit dem WHERE-Pipe-Operator, der überall angewendet werden kann. Mit dem Pipe-Operator WHERE, der HAVING und QUALIFY ersetzt, können die Ergebnisse von Aggregations- oder Fensterfunktionen gefiltert werden.

Weitere Informationen finden Sie in der vollständigen Liste der Pipe-Operatoren.

Anwendungsfälle

Gängige Anwendungsfälle für die Pipe-Syntax:

  • Ad-hoc-Analyse und inkrementelle Abfrageerstellung: Die logische Reihenfolge der Vorgänge erleichtert das Schreiben und Entfernen von Fehlern in Abfragen. Das Präfix einer Abfrage bis zu einem Pipesymbol |> ist eine gültige Abfrage, mit der Sie Zwischenergebnisse in einer langen Abfrage aufrufen können. Die Produktivitätssteigerungen können den Entwicklungsprozess in Ihrer gesamten Organisation beschleunigen.
  • Loganalyse: Es gibt andere Arten von Pipe-ähnlicher Syntax, die bei Nutzern von Loganalysen beliebt sind. Die Pipe-Syntax bietet eine vertraute Struktur, die das Onboarding für diese Nutzer für und vereinfacht.

Zusätzliche Funktionen in der Pipe-Syntax

Mit wenigen Ausnahmen werden in der Pipe-Syntax alle Operatoren unterstützt, die in der Standardsyntax mit derselben Syntax unterstützt werden. Außerdem werden in der Pipe-Syntax die folgenden Pipe-Operatoren eingeführt.

EXTEND-Pipe-Operator

Mit dem Pipe-Operator EXTEND können Sie berechnete Spalten an die aktuelle Tabelle anhängen. Der Pipe-Operator EXTEND ähnelt der SELECT *, new_column-Anweisung, bietet aber mehr Flexibilität beim Verweisen auf Spaltenaliasse.

Betrachten Sie die folgende Tabelle mit zwei Testergebnissen für jede Person:

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              |
 +---------+--------+--------+-----------------*/

Angenommen, Sie möchten den durchschnittlichen Roh- und Prozentsatzwert berechnen, den die einzelnen Schüler im Test erhalten haben. Bei der Standardsyntax sind frühere Aliase für spätere Spalten in einer SELECT-Anweisung nicht sichtbar. Um eine untergeordnete Abfrage zu vermeiden, müssen Sie den Ausdruck für den Durchschnitt wiederholen:

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM Scores;

Der Pipe-Operator EXTEND kann auf zuvor verwendete Aliase verweisen, wodurch die Abfrage leichter zu lesen und weniger fehleranfällig ist:

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             |
 +---------+---------------+-----------------*/

SET-Pipe-Operator

Mit dem SET-Pipe-Operator können Sie den Wert von Spalten in der aktuellen Tabelle ersetzen. Der Pipe-Operator SET ähnelt der Anweisung SELECT * REPLACE (expression AS column). Sie können auf den ursprünglichen Wert verweisen, indem Sie den Spaltennamen mit einem Tabellenalias qualifizieren.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

DROP-Pipe-Operator

Mit dem DROP-Pipe-Operator können Sie Spalten aus der aktuellen Tabelle entfernen. Der Pipe-Operator DROP ähnelt der Anweisung SELECT * EXCEPT(column). Nachdem eine Spalte gelöscht wurde, können Sie weiterhin auf den ursprünglichen Wert verweisen, indem Sie den Spaltennamen mit einem Tabellenalias qualifizieren.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

RENAME-Pipe-Operator

Mit dem RENAME-Pipe-Operator können Sie Spalten in der aktuellen Tabelle umbenennen. Der Pipe-Operator RENAME ähnelt der Anweisung 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 |
 +---+---+---*/

AGGREGATE-Pipe-Operator

Wenn Sie eine Aggregation in der Pipe-Syntax ausführen möchten, verwenden Sie den AGGREGATE-Pipe-Operator, gefolgt von einer beliebigen Anzahl von Aggregationsfunktionen und einer GROUP BY-Klausel. In einer SELECT-Klausel müssen Spalten nicht wiederholt werden.

In den Beispielen in diesem Abschnitt wird die Tabelle Produce verwendet:

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           |
 +---------+-----------+-------+-------------*/

Wenn Sie die Ergebnisse direkt nach der Aggregation sortieren möchten, können Sie die Spalten in der GROUP BY-Klausel, die Sie sortieren möchten, mit ASC oder DESC kennzeichnen. Nicht markierte Spalten werden nicht sortiert.

Wenn Sie alle Spalten sortieren möchten, können Sie die GROUP BY-Klausel durch eine GROUP AND ORDER BY-Klausel ersetzen, die standardmäßig alle Spalten in aufsteigender Reihenfolge sortiert. Sie können DESC nach den Spalten angeben, die in absteigender Reihenfolge sortiert werden sollen. Die folgenden drei Abfragen sind beispielsweise gleichwertig:

-- 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;

Der Vorteil einer GROUP AND ORDER BY-Klausel besteht darin, dass Sie Spaltennamen nicht an zwei Stellen wiederholen müssen.

Wenn Sie eine vollständige Tabellenaggregation ausführen möchten, verwenden Sie GROUP BY() oder lassen Sie die GROUP BY-Klausel vollständig aus:

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

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

JOIN-Pipe-Operator

Mit dem JOIN-Pipe-Operator können Sie die aktuelle Tabelle mit einer anderen Tabelle zusammenführen. Er unterstützt die standardmäßigen Join-Vorgänge, einschließlich CROSS, INNER, LEFT, RIGHT und FULL.

In den folgenden Beispielen wird auf die Tabellen Produce und ItemData verwiesen:

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
);

Im folgenden Beispiel wird eine USING-Klausel verwendet, um Mehrdeutigkeiten bei Spalten zu vermeiden:

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

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

Wenn Sie auf Spalten in der aktuellen Tabelle verweisen möchten, z. B. um Spalten in einer ON-Klausel eindeutig zu identifizieren, müssen Sie der aktuellen Tabelle einen Alias mit dem AS-Pipe-Operator zuweisen. Optional können Sie der zusammengeführten Tabelle einen Alias zuweisen. Sie können auf beide Aliasse nach nachfolgenden Pipe-Operatoren verweisen:

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 |
 +---------+-------+-----*/

Die rechte Seite des Joins kann die linke Seite des Joins nicht sehen. Das bedeutet, dass Sie die aktuelle Tabelle nicht mit sich selbst verknüpfen können. Die folgende Abfrage schlägt beispielsweise fehl:

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

Wenn Sie einen Selbstjoin mit einer geänderten Tabelle ausführen möchten, können Sie einen allgemeinen Tabellenausdruck (Common Table Expression, CTE) in einer WITH-Klausel verwenden.

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

Beispiel

Sehen wir uns die folgende Tabelle mit Informationen zu Kundenbestellungen an:

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);

Angenommen, Sie möchten für jeden Bundesstaat und Artikeltyp den durchschnittlichen Betrag wissen, den wiederkehrende Kunden ausgeben. Sie könnten die Abfrage so schreiben:

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;

Wenn Sie die Abfrage von oben nach unten lesen, sehen Sie die Spalte total_cost, bevor sie definiert wurde. Selbst in der untergeordneten Abfrage sehen Sie zuerst die Namen der Spalten, bevor Sie sehen, aus welcher Tabelle sie stammen.

Um diese Abfrage zu verstehen, muss sie von innen nach außen gelesen werden. Die Spalten state und item_type werden in den Klauseln SELECT und GROUP BY mehrmals wiederholt und dann noch einmal in der Klausel ORDER BY.

Die folgende äquivalente Abfrage ist in der Pipe-Syntax geschrieben:

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    |
 +-------+------------+---------*/

Mithilfe der Pipe-Syntax können Sie die Abfrage so schreiben, dass sie den logischen Schritten folgt, die Sie zur Lösung des ursprünglichen Problems durchgehen würden. Die Syntaxzeilen in der Abfrage entsprechen den folgenden logischen Schritten:

  • Beginnen Sie mit der Tabelle der Kundenbestellungen.
  • Sehen Sie sich an, wie viel jeder Kunde für die einzelnen Artikeltypen ausgegeben hat, aufgeschlüsselt nach Bundesland.
  • Zählen Sie die Anzahl der Bestellungen für jeden Kunden.
  • Beschränken Sie die Ergebnisse auf wiederkehrende Kunden.
  • Ermitteln Sie den durchschnittlichen Betrag, den wiederkehrende Kunden in den einzelnen Bundesstaaten und für die einzelnen Artikeltypen ausgeben.

Beschränkungen

  • Sie können eine differenzielle Datenschutzklausel nicht in eine SELECT-Anweisung einfügen, die auf einen Pipe-Operator folgt. Verwenden Sie stattdessen eine Klausel für den differenziellen Datenschutz in der Standardsyntax und wenden Sie Pipe-Operatoren nach der Abfrage an.
  • In der Pipe-Syntax können Sie kein benanntes Fenster verwenden.