パイプクエリ構文を使用する

プレビュー

このプロダクトまたは機能は、サービス固有規約の「一般サービス規約」セクションの「一般提供前のサービス規約」の対象となります。 pre-GA のプロダクトと機能は「現状のまま」利用できますが、サポートが制限されることがあります。詳細については、リリース ステージの説明をご覧ください。

注: この機能に関するフィードバックやサポートのリクエストを行う場合は、[e-mail] 宛てにメールを送信してください。

パイプ構文のプレビューにプロジェクトを登録するには、パイプ構文の登録フォームにご記入ください。

パイプクエリ構文は、クエリの読み取り、書き込み、メンテナンスが容易になるように設計されたリニアクエリ構造をサポートする GoogleSQL の拡張機能です。パイプ構文は、GoogleSQL を記述するすべての場所で使用できます。

パイプ構文は、既存の GoogleSQL クエリ構文または標準構文と同じオペレーション（選択、集計、グループ化、結合、フィルタリングなど）をサポートしていますが、オペレーションは任意の順序で任意の回数適用できます。パイプ構文のリニア構造により、クエリ構文の順序が結果テーブルの作成に必要な論理ステップの順序と一致するようにクエリを記述できます。

パイプ構文を使用するクエリは、同等の標準構文クエリと同じ方法で料金設定、実行、最適化されます。パイプ構文でクエリを作成する場合は、ガイドラインに沿って費用を見積もるとクエリ計算を最適化することができます。

標準構文には、読み取り、書き込み、メンテナンスが困難になる可能性がある問題があります。次の表に、パイプ構文でこれらの問題に対処する方法を示します。

標準の構文	パイプ構文
句は特定の順序で記述する必要があります。	パイプ演算子は任意の順序で適用できます。
マルチレベル集計を含むクエリなど、より複雑なクエリには通常、CTE またはネストされたサブクエリが必要です。	より複雑なクエリは通常、クエリの末尾にパイプ演算子を追加して表現します。
集計中、列は SELECT、GROUP BY、ORDER BY 句で繰り返されます。	列は集計ごとに 1 回だけ指定できます。

構文の詳細については、パイプクエリの構文リファレンス ドキュメントをご覧ください。

基本的な構文

パイプ構文では、クエリは標準 SQL クエリまたは FROM 句で始まります。たとえば、FROM MyTable などのスタンドアロンの FROM 句は、有効なパイプ構文です。標準 SQL クエリの結果または FROM 句のテーブルは、パイプ記号 |> に入力として渡すことができます。その後にパイプ演算子名とその演算子への引数を指定します。パイプ演算子はテーブルをなんらかの方法で変換し、その変換の結果を別のパイプ演算子に渡すことができます。

クエリでは、パイプ演算子を任意の数使用して、列の選択、並べ替え、フィルタ、結合、集計などの操作を行うことができます。パイプ演算子の名前は標準構文の演算子と同じで、通常は同じ動作をします。標準構文とパイプ構文の主な違いは、クエリの構造方法です。クエリで表現されるロジックが複雑になっても、深くネストされたサブクエリを使用せずに、パイプ演算子の線形シーケンスとしてクエリを表現できるため、読みやすく理解しやすくなります。

パイプ構文には次の主な特徴があります。

• パイプ構文の各パイプ演算子は、パイプ記号 |>、演算子名、引数（
  |> operator_name argument_list）で構成されます。
• パイプ演算子は、有効なクエリの末尾に追加できます。
• パイプ演算子は、任意の順序で任意の数だけ適用できます。
• パイプ構文は、クエリ、ビュー、テーブル値関数などのコンテキストで、標準構文がサポートされているすべての場所で機能します。
• パイプ構文と標準構文は、同じクエリ内で混在させることができます。たとえば、サブクエリでは親クエリとは異なる構文を使用できます。
• パイプ演算子を使用すると、パイプの前にテーブルにあるすべてのエイリアスを確認できます。
• クエリは FROM 句で開始できます。必要に応じて、FROM 句の後にパイプ演算子を追加できます。

次のテーブルについて考えてみましょう。

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

次のクエリには、クエリを順番に構築する方法を示した有効なパイプ構文がそれぞれ含まれています。

クエリは FROM 句で始めることができます。パイプ記号を含める必要はありません。

-- View the table.
FROM Produce;

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

WHERE パイプ演算子を使用してフィルタできます。

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

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

集計を実行するには、AGGREGATE パイプ演算子、任意の数の集計関数、GROUP BY 句の順に使用します。GROUP BY 句は AGGREGATE パイプ演算子の一部であり、パイプ記号（|>）で区切られません。

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

アイテムの ID を含む次のテーブルがあるとします。

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

JOIN パイプ演算子を使用して、前のクエリの結果をこのテーブルと結合し、各アイテムの ID を含めることができます。

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

標準構文との主な違い

パイプ構文は、標準構文と次の点で異なります。

• クエリは FROM 句で開始できます。
• SELECT パイプ演算子は集計を行いません。代わりに AGGREGATE パイプ演算子を使用する必要があります。
• フィルタリングは常に WHERE パイプ演算子で行われ、これはどこでも適用できます。HAVING と QUALIFY に代わるパイプ演算子 WHERE を使用すると、集計関数またはウィンドウ関数の結果をフィルタできます。

詳細については、パイプ演算子の完全なリストをご覧ください。

ユースケース

パイプ構文の一般的なユースケースは次のとおりです。

• アドホック分析と増分クエリの作成: オペレーションの論理的な順序により、クエリの作成とデバッグが容易になります。パイプ記号 |> までのクエリの接頭辞は有効なクエリです。これにより、長いクエリの中間結果を表示できます。生産性が向上することで、組織全体の開発プロセスを迅速化できます。
• ログ分析: ログ分析のユーザーに人気のある、パイプのような他のタイプの構文もあります。パイプ構文は、 と に慣れているユーザーのオンボーディングを簡素化する使い慣れた構造を提供します。

パイプ構文の追加機能

パイプ構文は、標準構文がサポートするすべての演算子を同じ構文でサポートしています（例外はほとんどありません）。また、パイプ構文では、次のパイプ演算子も導入されています。

EXTEND パイプ演算子

EXTEND パイプ演算子を使用すると、計算された列を現在のテーブルに追加できます。EXTEND パイプ演算子は SELECT *, new_column ステートメントに似ていますが、列エイリアスの参照に柔軟性があります。

次の表は、各個人の 2 つのテストスコアを示しています。

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

各生徒がテストで獲得した平均得点と平均パーセンテージ スコアを計算したいとします。標準構文では、SELECT ステートメントの後半の列には、前のエイリアスが公開されません。サブクエリを回避するには、平均の式を繰り返す必要があります。

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

EXTEND パイプ演算子を使用すると、以前に使用したエイリアスを参照できるため、クエリの読み取りが容易になり、エラーが発生しにくくなります。

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 パイプ演算子

SET パイプ演算子を使用すると、現在のテーブルの列の値を置き換えることができます。SET パイプ演算子は SELECT * REPLACE (expression AS column) ステートメントに似ています。元の値を参照するには、列名にテーブルの別名を指定します。

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

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

DROP パイプ演算子

DROP パイプ演算子を使用すると、現在のテーブルから列を削除できます。DROP パイプ演算子は SELECT * EXCEPT(column) ステートメントに似ています。列を削除した後でも、列名にテーブル エイリアスを指定することで、元の値を参照できます。

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

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

RENAME パイプ演算子

RENAME パイプ演算子を使用すると、現在のテーブルの列の名前を変更できます。RENAME パイプ演算子は 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 パイプ演算子

パイプ構文で集計を実行するには、AGGREGATE パイプ演算子、任意の数の集計関数、GROUP BY 句の順に使用します。SELECT 句で列を繰り返す必要はありません。

このセクションの例では、Produce テーブルを使用します。

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

集計の直後に結果を並べ替える準備が整っている場合は、並べ替える列を GROUP BY 句で ASC または DESC でマークできます。マークされていない列は並べ替えられません。

すべての列を並べ替える場合は、GROUP BY 句を GROUP AND ORDER BY 句に置き換えることができます。この場合、デフォルトですべての列が昇順で並べ替えられます。降順に並べ替える列の後に DESC を指定できます。たとえば、次の 3 つのクエリは同等です。

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

GROUP AND ORDER BY 句を使用する利点は、列名を 2 か所で繰り返す必要がないことです。

テーブル全体の集計を実行するには、GROUP BY() を使用するか、GROUP BY 句を完全に省略します。

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

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

JOIN パイプ演算子

JOIN パイプ演算子を使用すると、現在のテーブルを別のテーブルと結合できます。また、CROSS、INNER、LEFT、RIGHT、FULL などの標準の結合オペレーションもサポートしています。

次の例では、Produce テーブルと ItemData テーブルを参照しています。

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

次の例では、USING 句を使用して列の曖昧さを回避しています。

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

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

ON 句の列の曖昧さを解消するなど、現在のテーブルの列を参照するには、AS パイプ演算子を使用して現在のテーブルにエイリアスを設定する必要があります。必要に応じて、結合されたテーブルにエイリアスを指定できます。パイプ演算子の後に両方のエイリアスを参照できます。

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

結合の右側には結合の左側が可視されません。つまり、現在のテーブルを自身と結合することはできません。たとえば、次のクエリは失敗します。

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

変更されたテーブルでセルフ結合を実行するには、WITH 句内で共通テーブル式（CTE）を使用します。

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

例

顧客の注文に関する情報を含む次の表について考えてみましょう。

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

たとえば、州と商品タイプごとに、リピーターが費やした平均金額を知りたいとします。クエリは次のように記述できます。

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;

クエリを上から下に読むと、列 total_cost が定義される前に列 total_cost が見つかります。サブクエリ内でも、列の名前を読み取ってから、その列がどのテーブルから取得されたかを確認します。

このクエリを理解するには、内側から外側に読み取る必要があります。列 state と item_type は、SELECT 句と GROUP BY 句で複数回繰り返され、ORDER BY 句でも繰り返されます。

次の同等のクエリは、パイプ構文を使用して記述されます。

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

パイプ構文を使用すると、元の問題を解決するために考えられる論理的な手順に沿ってクエリを作成できます。クエリの構文の行は、次の論理ステップに対応しています。

• まず、顧客の注文の表から始めます。
• 各顧客が各種類の商品に支払った金額を州別に確認できます。
• 各顧客の注文数をカウントします。
• 結果をリピーターだけに制限する。
• リピーターが州とアイテムタイプごとに費やした平均金額を確認します。

制限事項

• パイプ演算子の後の SELECT ステートメントに差分プライバシー句を含めることはできません。代わりに、標準構文で差分プライバシー クラスタを使用します。クエリの後にパイプ演算子を適用します。
• パイプ構文で名前付きウィンドウを使用することはできません。

関連リソース

• パイプクエリ構文リファレンス
• 標準クエリ構文リファレンス
• パイプ構文に関する VLDB 2024 カンファレンス ペーパー