パイプ構文

プレビュー

この機能には、サービス固有の利用規約の「一般的なサービス規約」の「pre-GA サービス規約」が適用されます。 pre-GA の機能は「現状有姿」で利用できますが、サポートが制限される場合があります。 詳しくは、リリース ステージの説明をご覧ください。

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

パイプ構文は、クエリの読み取り、書き込み、メンテナンスが容易になるように設計されたクエリの線形構造をサポートする GoogleSQL の拡張機能です。

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

概要

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

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

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

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

基本的な構文

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

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

以下のテーブルを例に取り上げます。

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

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

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

-- View the table
FROM mydataset.produce;

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

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

-- Filter items with no sales
FROM mydataset.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 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         |
 +---------+-------------+-----------*/

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

CREATE TABLE mydataset.item_data 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 mydataset.produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.item_data USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

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

• パイプ演算子は、任意の順序で任意の回数適用できます。
• パイプ構文は、クエリ、ビュー、テーブル値関数、その他のコンテキストなど、標準構文がサポートされているすべての場所で機能します。
• パイプ構文と標準構文は、同じクエリ内で混在させることができます。たとえば、サブクエリでは親クエリとは異なる構文を使用できます。
• パイプ演算子では、パイプの前のテーブルにあるすべてのエイリアスを確認できます。

標準構文との主な違い

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

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

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

ユースケース

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

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

パイプ構文の追加機能

パイプ構文は、わずかな例外を除いて、標準構文がサポートするすべての演算子を同じ構文でサポートしています。また、パイプ構文では、次のパイプ演算子も導入されています。

EXTEND パイプ演算子

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

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

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

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

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

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

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

パイプ記号の直後にのみ使用できる 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 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           |
 +---------+-----------+-------+-------------*/

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

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

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

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

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

FROM mydataset.produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

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

JOIN パイプ演算子

JOIN パイプ演算子を使用すると、現在のテーブルを別のテーブルと結合できます。また、CROSS、INNER、LEFT、RIGHT、FULL などの標準の結合演算もサポートされます。

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

CREATE 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 TABLE mydataset.item_data 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 `mydataset.produce`
|> JOIN `mydataset.item_data` USING(item)
|> WHERE item = "apples";

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

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

FROM `mydataset.produce`
|> AS produce_table
|> JOIN `mydataset.item_data` 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 `mydataset.produce`
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

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

WITH cte_table AS (
  FROM `mydataset.produce`
  |> WHERE item = "carrots"
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

例

次の表は、顧客の注文に関する情報を示しています。

CREATE TABLE mydataset.customer_orders 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 mydataset.customer_orders
    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 が見つかります。サブクエリ内でも、列がどのテーブルから取得されるかを確認する前に、列の名前を読み取ります。

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

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

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

次のステップ

• パイプクエリの構文の詳細を確認する。
• 標準クエリ構文の詳細を確認する。