Mit der Pipe-Abfragesyntax arbeiten

Die Pipe-Abfragesyntax ist eine Erweiterung von GoogleSQL, die eine lineare Abfragestruktur unterstützt, mit der Sie Abfragen einfacher lesen, schreiben und verwalten können. 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-Abfrage syntax oder die Standardsyntax – z. B. Auswahl, Aggregation und Gruppierung, Verknüpfung und Filterung. Die Vorgänge können jedoch in beliebiger Reihenfolge und beliebig oft angewendet werden. Mit 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, die die Pipe-Syntax verwenden, werden genauso abgerechnet, ausgeführt und optimiert wie entsprechende Abfragen mit Standardsyntax. Wenn Sie Abfragen mit der Pipe Syntax schreiben, folgen Sie der Anleitung zum Schätzen von Kosten und Optimieren der Abfrageberechnung.

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

Standard syntax Pipe-Syntax
Klauseln müssen in einer bestimmten Reihenfolge stehen. 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 Hinzufügen von Pipe-Operatoren am Ende der Abfrage ausgedrückt.
Bei der Aggregation werden Spalten in den SELECT, GROUP BY, und ORDER BY Klauseln wiederholt. Spalten können nur einmal pro Aggregation aufgeführt werden.

Informationen zum schrittweisen Erstellen einer komplexen Abfrage in der Pipe-Syntax finden Sie unter Daten mit der Pipe-Syntax analysieren. Ausführliche Informationen zur Syntax finden Sie in der Pipe-Abfragesyntax Referenz dokumentation.

Grundlegende Syntax

In der Pipe-Syntax beginnen Abfragen mit einer Standard-SQL-Abfrage oder einer FROM-Klausel. Beispielsweise ist eine eigenständige FROM Anweisung wie FROM MyTable eine gültige Pipe-Syntax. Das Ergebnis der Standard-SQL-Abfrage oder die Tabelle aus der FROM Anweisung kann dann als Eingabe an ein Pipe-Symbol, |>, übergeben werden, gefolgt von einem Pipe Operatornamen 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, zu verknüpfen oder zu aggregieren. Die Namen der Pipe-Operatoren entsprechen ihren Pendants in der Standardsyntax und haben in der Regel dasselbe Verhalten. Der Hauptunterschied zwischen der Standardsyntax und der Pipe-Syntax besteht in der Struktur Ihrer Abfrage. Auch wenn die von Ihrer Abfrage ausgedrückte Logik komplexer wird, kann die Abfrage weiterhin als lineare Folge von Pipe-Operatoren ausgedrückt werden, ohne dass tief verschachtelte Unterabfragen verwendet werden müssen. Dadurch ist sie leichter zu lesen und zu verstehen.

Die Pipe-Syntax hat die folgenden Hauptmerkmale:

  • Jeder Pipe-Operator in der Pipe-Syntax besteht aus dem Pipe-Symbol, |>, einem Operatornamen und allen Argumenten:
    |> operator_name argument_list
  • Pipe-Operatoren können am Ende jeder gültigen Abfrage hinzugefügt werden.
  • Pipe-Operatoren können in beliebiger Reihenfolge und beliebig oft angewendet werden.
  • Die Pipe-Syntax funktioniert überall dort, wo die Standardsyntax unterstützt wird: in Abfragen, Ansichten, Tabellenwertfunktionen 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 alle Aliasse sehen, die in der Tabelle vor der Pipe vorhanden sind.
  • Eine Abfrage kann mit einer FROM Anweisung beginnen und nach der FROM Anweisung können optional Pipe-Operatoren hinzugefügt werden.

Sehen Sie sich die folgende Tabelle an:

CREATE OR REPLACE TABLE mydataset.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 eine gültige Pipe-Syntax, die 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 mydataset.Produce;

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

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

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

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

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

-- Compute total sales by item.
FROM mydataset.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 mydataset.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 verknüpfen, um die ID jedes Artikels einzufügen:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.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:

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

Anwendungsfälle

Häufige Anwendungsfälle für die Pipe-Syntax sind:

Zusätzliche Funktionen in der Pipe-Syntax

Mit wenigen Ausnahmen unterstützt die Pipe-Syntax alle Operatoren, die auch die Standardsyntax unterstützt, und zwar mit derselben Syntax. Darüber hinaus führt die Pipe-Syntax zusätzliche Pipe-Operatoren ein und verwendet eine geänderte Syntax für Aggregationen und Verknüpfungen. In den folgenden Abschnitten werden einige dieser Operatoren erläutert. Eine vollständige Liste aller unterstützten Operatoren finden Sie unter Liste der Pipe-Operatoren.

EXTEND-Pipe-Operator

Mit dem EXTEND-Pipe-Operator können Sie der aktuellen Tabelle berechnete Spalten hinzufügen. Der EXTEND-Pipe-Operator ähnelt der Anweisung SELECT *, new_column, bietet aber mehr Flexibilität beim Verweisen auf Spaltenaliasse.

Sehen Sie sich die folgende Tabelle an, die zwei Testergebnisse für jede Person enthält:

CREATE OR REPLACE TABLE mydataset.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 die durchschnittliche Rohpunktzahl und die durchschnittliche prozentuale Punktzahl berechnen, die jeder Schüler im Test erzielt hat. In der Standardsyntax haben spätere Spalten in einer SELECT-Anweisung keinen Zugriff auf frühere Aliasse. Um eine Unterabfrage 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 mydataset.Scores;

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

FROM mydataset.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 SET Pipe-Operator ähnelt der SELECT * REPLACE (expression AS column) Anweisung. 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 DROP Pipe-Operator ähnelt der SELECT * EXCEPT(column) Anweisung. Nachdem eine Spalte entfernt 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 RENAME Pipe-Operator ähnelt der SELECT * EXCEPT(old_column), old_column AS new_column Anweisung.

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

Verwenden Sie den AGGREGATE Pipe Operator, gefolgt von einer beliebigen Anzahl von Aggregatfunktionen und einer GROUP BY Klausel, um eine Aggregation in der Pipe-Syntax durchzuführen. Sie müssen Spalten nicht in einer SELECT-Klausel wiederholen.

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

CREATE OR REPLACE TABLE mydataset.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 mydataset.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-Anweisung, die Sie sortieren möchten, mit ASC oder DESC kennzeichnen. Nicht gekennzeichnete 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 Sie in absteigender Reihenfolge sortieren möchten. Die folgenden drei Abfragen sind beispielsweise gleichwertig:

-- Use a separate ORDER BY clause.
FROM mydataset.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 mydataset.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 mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

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

Verwenden Sie GROUP BY() oder lassen Sie die GROUP BY Klausel ganz weg, um eine vollständige Tabellenaggregation durchzuführen:

FROM mydataset.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 verknüpfen. Er unterstützt die Standard-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 mydataset.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 mydataset.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 mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

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

Um auf Spalten in der aktuellen Tabelle zu verweisen, z. B. um Spalten in einer ON Klausel zu disambiguieren, müssen Sie die aktuelle Tabelle mit dem AS Pipe Operator aliasieren. Optional können Sie die verknüpfte Tabelle aliasieren. Sie können nachfolgende Pipe-Operatoren auf beide Aliasse verweisen:

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.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 hat keinen Zugriff auf die linke Seite des Joins. 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 mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Um einen Self Join mit einer geänderten Tabelle durchzuführen, können Sie einen allgemeinen Tabellenausdruck (Common Table Expression, CTE) in einer WITH-Klausel verwenden.

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

Beispiel

Sehen Sie sich die folgende Tabelle mit Informationen zu Kundenbestellungen an:

CREATE OR REPLACE TABLE mydataset.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 jeden Artikeltyp den durchschnittlichen Betrag ermitteln, der von wiederkehrenden Kunden ausgegeben wird. 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 mydataset.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, stoßen Sie auf die Spalte total_cost, bevor sie definiert wurde. Auch in der Unterabfrage lesen Sie 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, dann noch einmal in der ORDER BY-Klausel.

Die folgende gleichwertige Abfrage wird mit der Pipe-Syntax geschrieben:

FROM mydataset.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    |
 +-------+------------+---------*/

Mit 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 durchführen würden. Die Syntaxzeilen in der Abfrage entsprechen den folgenden logischen Schritten:

  • Beginnen Sie mit der Tabelle der Kundenbestellungen.
  • Ermitteln Sie, wie viel jeder Kunde für jeden Artikeltyp nach Bundesstaat ausgegeben hat.
  • 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 für jeden Bundesstaat und Artikeltyp ausgeben.

Beschränkungen

  • Sie können keine Differential Privacy-Klausel in eine SELECT Anweisung nach einem Pipe-Operator einfügen. Verwenden Sie stattdessen eine Differential Privacy-Klausel in der Standardsyntax und wenden Sie Pipe-Operatoren nach der Abfrage an.

Nächste Schritte