クエリ構文

クエリステートメントは、1 つ以上のテーブルまたは式をスキャンし、計算結果の行を返します。このトピックでは、BigQuery での SQL クエリの構文について説明します。

SQL 構文

query_statement:
    query_expr

query_expr:
    [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ]
    { select | ( query_expr ) | set_operation }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT
        [ { ALL | DISTINCT } ]
        [ AS { STRUCT | VALUE } ]
        select_list
    [ FROM from_clause[, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]
    [ HAVING bool_expression ]
    [ QUALIFY bool_expression ]
    [ WINDOW window_clause ]

表記ルール

角かっこ [ ] は省略可能な句を示します。
かっこ ( ) は、リテラルのかっこを示します。
縦線 | は論理 OR を表します。
中かっこ { } では一連のオプションを囲みます。
角かっこ内のカンマと省略記号 [, ... ] は、角かっこの前の項目をカンマ区切りのリストの形で繰り返すことができることを示します。

サンプルテーブル

以降のテーブルでは、このリファレンスのさまざまなクエリ句の動作を例で説明しています。

Roster テーブル

Roster テーブルには、選手名（LastName）と、選手が所属する学校に割り当てられた一意の ID（SchoolID）が含まれます。次のようになります。

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

このリファレンスのサンプルの一時テーブル名は、次の WITH 句を使用して再現できます。

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

PlayerStats テーブル

PlayerStats テーブルには、選手名（LastName）、ある試合の対戦相手に割り当てられた一意の ID（OpponentID）、その試合での選手の得点数（PointsScored）が含まれます。

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Adams      | 52         | 4            |
| Buchanan   | 50         | 13           |
+----------------------------------------+

このリファレンスのサンプルの一時テーブル名は、次の WITH 句を使用して再現できます。

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

TeamMascot テーブル

TeamMascot テーブルには、一意の学校 ID（SchoolID）とその学校のマスコット（Mascot）が含まれます。

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

このリファレンスのサンプルの一時テーブル名は、次の WITH 句を使用して再現できます。

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

SELECT ステートメント

SELECT
    [ { ALL | DISTINCT } ]
    [ AS { STRUCT | VALUE } ]
   select_list

select_list:
    { select_all | select_expression } [, ...]

select_all:
    [ expression. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

select_expression:
    expression [ [ AS ] alias ]

SELECT リストは、クエリが返す列を定義します。SELECT リスト内の式は、対応する FROM 句の from_item の列を参照します。

SELECT リストの各項目は、次のうちの 1 つです。

*
expression
expression.*

SELECT *

SELECT * は、select star とも呼ばれ、クエリ全体を実行した後に表示される各列に対して 1 つの出力列を生成します。

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT `expression`

SELECT リストの項目には、式を指定できます。これらの式から、1 つの値が求められ、オプションの明示的 alias を持つ 1 つの出力列が生成されます。

式が明示的なエイリアスを持たない場合、可能な場合は暗黙のエイリアスのルールに従って、暗黙のエイリアスを受け取ります。受け取れない場合、列は匿名となり、クエリの他の場所の名前でそれを参照できなくなります。

SELECT `expression.*`

SELECT リスト内の項目は expression.* という形式で指定することもできます。この場合、expression の各列または先頭フィールドに 1 つの出力列が生成されます。式は、テーブルエイリアスか、STRUCT のような単一値フィールド付きのデータ型になります。

次のクエリは、テーブル groceries の各列に対して 1 つの出力列を生成し、エイリアスには g が設定されます。

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

その他の例:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+

WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

SELECT * EXCEPT

SELECT * EXCEPT ステートメントは、結果から除外する 1 つ以上の列の名前を指定します。指定された名前と一致する列はすべて出力から除外されます。

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

SELECT * REPLACE

SELECT * REPLACE ステートメントでは、expression AS identifier 句を 1 つ以上指定します。個々の ID が SELECT * ステートメントの列名と一致している必要があります。出力列のリストで、REPLACE 句の ID と一致する列が REPLACE 句の表現で置き換えられます。

SELECT * REPLACE ステートメントでは、列の名前や順序は変更されません。ただし、値と値の型は変更できます。

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

SELECT DISTINCT

SELECT DISTINCT ステートメントは、重複した行を破棄し、残りの行のみを返します。SELECT DISTINCT は、次の型の列を返すことはできません。

STRUCT
ARRAY

SELECT ALL

SELECT ALL ステートメントは、重複した行も含めて、すべての行を返します。SELECT ALL が SELECT のデフォルト動作です。

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

上記の構文を使用すると、行の型が STRUCT である値テーブルが生成されます。この場合、STRUCT フィールドの名前と型は、SELECT リストで生成される列の名前と型と一致します。

例:

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

SELECT AS STRUCT をスカラーサブクエリや配列サブクエリで使用すると、複数の値をまとめた単一の STRUCT 型を生成できます。通常、スカラーサブクエリと配列サブクエリ（サブクエリを参照）で複数の列を返すことは許可されていませんが、STRUCT 型の単一列を返すことはできます。

SELECT AS VALUE

SELECT AS VALUE では、1 列だけを生成する SELECT リストから値テーブルが生成されます。1 列の出力テーブル（名前付きの場合もある）が生成される代わりに、出力として値テーブルが生成されます。この値テーブルの行の型は 1 つの SELECT 列で生成された値の型になります。その列にエイリアスがあっても、値テーブルでは破棄されます。

例:

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

上記のクエリを使用すると、行の型が STRUCT<a int64, b int64> であるテーブルが生成されます。

FROM 句

FROM from_clause[, ...]

from_clause:
    from_item
    [ { pivot_operator | unpivot_operator } ]
    [ tablesample_operator ]

from_item:
    {
      table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

FROM 句は行を取得する 1 つまたは複数のテーブルを示し、これらの行を結合して、残りのクエリの処理のための単一ストリームの行を生成する方法を指定します。

pivot_operator

PIVOT 演算子をご覧ください。

unpivot_operator

UNPIVOT 演算子をご覧ください。

tablesample_operator

TABLESAMPLE 演算子をご覧ください。

table_name

既存のテーブルの名前（修飾可）。

SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;

FOR SYSTEM_TIME AS OF

FOR SYSTEM_TIME AS OF は、timestamp_expression の時点で最新であったテーブル定義と行の履歴バージョンを参照します。

制限事項:

FOR SYSTEM_TIME AS OF を含む FROM 句のソーステーブルには、次のいずれも指定できません。

ARRAY スキャン。フラット化配列、UNNEST 演算子の出力などを含む。
WITH 句で定義された共通テーブル式。

timestamp_expressionは、定数式である必要があります。以下を含めることはできません。

サブクエリ。
相関参照（SELECT リストなど、より高いレベルのクエリステートメントに表示されるテーブルの列への参照）。
ユーザー定義関数（UDF）。

次の範囲に該当する timestamp_expression の値は指定できません。

現在のタイムスタンプより後（将来）。
現在のタイムスタンプより 7 日以上前。

1 つのクエリステートメントで、現在の時刻を含めた複数の時点における 1 つのテーブルを参照することはできません。つまり、クエリで同じタイムスタンプで複数回テーブルを参照することはできますが、現在のバージョンと履歴バージョン、または異なる 2 つの履歴バージョンを参照することはできません。

例:

次のクエリは、1 時間前のテーブルの履歴バージョンを返します。

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

次のクエリは、絶対的な時刻でのテーブルの履歴バージョンを戻します。

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

次のクエリでは、timestamp_expression が、同じクエリの別の列に対する相関参照を行っているため、エラーになります。

SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
               FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

次のオペレーションは、テーブルが置換される前のテーブルの履歴バージョンへのアクセスを示しています。

DECLARE before_replace_timestamp TIMESTAMP;

-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();

-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;

-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;

次の演算は、DML ジョブの前のテーブルの履歴バージョンの表へのアクセスを示しています。

DECLARE JOB_START_TIMESTAMP TIMESTAMP;

-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
      ('War and Peace', 'Leo Tolstoy');

SELECT * FROM books;

SET JOB_START_TIMESTAMP = (
  SELECT start_time
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE job_type="QUERY"
    AND statement_type="INSERT"
  ORDER BY start_time DESC
  LIMIT 1
 );

-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;

次のクエリでは、DML がテーブルの現在のバージョンおよび 1 日前のテーブルの履歴バージョンで動作するため、エラーになります。

INSERT INTO t1
SELECT * FROM t1
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

join_operation

JOIN 演算をご覧ください。

query_expr

( query_expr ) [ [ AS ] alias ] は、テーブルのサブクエリです。

field_path

FROM 句内で、field_path は特定のデータ型内のフィールドに解決する任意のパスです。field_path は、ネストされたデータ構造の中に任意で深く入り込むことができます。

有効な field_path の値の例を以下に示します。

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

FROM 句のフィールドパスは、配列フィールドで終了しなければなりません。さらに、フィールドパスでは、パスの末尾の前に配列を含めることができません。たとえば、パス array_column.some_array.some_array_field は、パスの末尾の前に配列が含まれているため無効です。

unnest_operator

UNNEST 演算子をご覧ください。

cte_name

WITH 句の共通テーブル式（CTE）は、FROM 句の任意の場所から参照できる一時テーブルのように機能します。次の例では、subQ1 と subQ2 が CTE です。

例:

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

WITH 句では、次のようにテーブル名を修飾する場合を除き、クエリの実行中は同名の永続テーブルを非表示にします。

dataset.Roster または project.dataset.Roster

UNNEST 演算子

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

as_alias:
    [AS] alias

UNNEST 演算子は ARRAY を受け取り、ARRAY 内の各要素を 1 行にしてテーブルを返します。UNNEST を FROM 句の外で IN 演算子とともに使用することもできます。

ほとんどの要素型の入力 ARRAY について、UNNEST の出力には通常 1 つの列が含まれます。この単一の列にはオプションの alias があり、これを使用するとクエリの別の場所で列を参照できます。こうした要素型を持つ ARRAYS は、以下のような複数の列を返します。

STRUCT

UNNEST は入力 ARRAY 内の要素の順序を破壊します。配列要素のインデックスを含む 2 番目の列を返すには、オプションの WITH OFFSET 句を使用します。

UNNEST のさまざまな使い方（作成、フラット化、フィルタリングなど）については、配列の操作をご覧ください。

UNNEST と STRUCT

STRUCT の入力 ARRAY について、UNNEST は STRUCT ごとに 1 行を返し、STRUCT 内の各フィールドについてそれぞれ別個の列が生成されます。各列のエイリアスは、対応する STRUCT フィールドの名前になります。

例:

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

UNNEST 演算子は値テーブルを返すため、UNNEST のエイリアスを作成して範囲変数を定義し、それをクエリの他の場所で参照できます。その範囲変数を SELECT リスト内で参照すると、クエリの結果として、入力テーブルの元の STRUCT の全フィールドを含む STRUCT が返されます。

例:

SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
       AS struct_value;

+---+-----+--------------+
| x | y   | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar}     |
| 1 | foo | {1, foo}     |
+---+-----+--------------+

明示的な UNNEST と暗黙的な UNNEST

ARRAY のネスト解除は、明示的にも、暗黙的にもできます。明示的なネスト解除の場合、array_expression は ARRAY 値を返す必要がありますが、ARRAY に解決される必要はなく、UNNEST キーワードが必要になります。

例:

SELECT * FROM UNNEST ([1, 2, 3]);

暗黙のネスト解除の場合、array_path は ARRAY に解決される必要があり、UNNEST キーワードは任意になります。

例:

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

このシナリオでは、array_path はデータ構造に任意に深く入ることができますが、末尾のフィールドは ARRAY データ型でなければなりません。式の前のフィールドは、ARRAY から名前付きのフィールドを抽出できないため、いずれも ARRAY データ型にすることはできません。

UNNEST と NULL

UNNEST は、NULL を次のように扱います。

NULL と空の配列はゼロ行を生成します。
NULL を含む配列は、NULL 値を含む行を生成します。

UNNEST と WITH OFFSET

オプションの WITH OFFSET 句は、オフセット値を含む別の列を返します。この列では、UNNEST 演算で生成された各行のカウントはゼロから始まります。この列にはオプションのエイリアスがあります。オプションのエイリアスを使用しない場合、デフォルトの列名は offset です。

例:

SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;

+---------+--------+
| numbers | offset |
+---------+--------+
| 10      | 0      |
| 20      | 1      |
| 30      | 2      |
+---------+--------+

PIVOT 演算子

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

PIVOT 演算子は、集計を使用して行を列に変換します。PIVOT は FROM 句の一部です。

PIVOT を使用して、任意のテーブル式を変更できます。
PIVOT と FOR SYSTEM_TIME AS OF の組み合わせは許可されませんが、FOR SYSTEM_TIME AS OF を使用するサブクエリ入力に対して PIVOT を使用できます。
PIVOT 演算子の直前に WITH OFFSET 句を使用することはできません。

概念例:

-- Before PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
+---------+-------+---------+------+

-- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+------+----+------+------+------+
| product | year | Q1 | Q2   | Q3   | Q4   |
+---------+------+----+------+------+------+
| Apple   | 2020 | 77 | 0    | NULL | NULL |
| Apple   | 2021 | 1  | NULL | NULL | NULL |
| Kale    | 2020 | 51 | 23   | 45   | 3    |
| Kale    | 2021 | 70 | 85   | NULL | NULL |
+---------+------+----+------+------+------+

定義

最上位の定義:

from_item: ピボット演算を実行するテーブルまたはサブクエリ。from_item はこれらのルールに従う必要があります。
pivot_operator: from_item で実行するピボット演算。
alias: クエリ内のアイテムに使用するエイリアス。

pivot_operator 定義:

aggregate_function_call: input_column が pivot_column 内の特定の値と一致するように、すべての入力行を集計する集計関数呼び出し。異なる pivot_column 値に対応する各集計は、出力に異なる列を生成します。集計関数呼び出しを作成する場合は、これらの規則に従います。
input_column: これらのルールに従って列を取得し、列の行値を取得します。
pivot_column: 集計関数の呼び出しごとに作成するピボット列。エイリアスが指定されていない場合は、デフォルトのエイリアスが作成されます。ピボット列の値の型は、値を比較できるように input_column 内の値の型と一致させる必要があります。input_column の値と一致しない値を pivot_column に指定できます。定数にするか、これらのルールに従う必要があります。

ルール

PIVOT に渡される from_item のルール:

from_item は、任意のテーブルまたはサブクエリの結果で構成されます。
from_item によって値テーブルが生成されない場合があります。
SELECT AS STRUCT を使用するサブクエリを from_item として指定することはできません。

aggregate_function_call のルール:

集計関数にする必要があります。例: SUM
相関列だけでなく PIVOT に渡されるテーブル内の列も参照できますが、PIVOT 句自体で定義された列にはアクセスできません。
エイリアスが指定されると、エイリアスを介して、PIVOT に渡されたテーブルにアクセスできます。
1 つの引数を取る集計関数のみを使用できます。
COUNT を除いて、NULL 入力を無視する集計関数を使用できます。
COUNT を使用する場合、引数として * を使用できます。

input_column のルール:

入力テーブルの列と（PIVOT 句自体で定義された列ではない）相関列にアクセスできます。
入力テーブルの各行に対して評価されます。集約関数やウィンドウ関数の呼び出しは禁止されています。
不確実な処理は許容されます。
タイプはグループ化可能にする必要があります。
入力テーブルのエイリアスが指定されている場合は、エイリアスを介して入力テーブルにアクセスできます。

pivot_column のルール:

pivot_column は、定数にする必要があります。
変数など、名前付き定数はサポートされていません。
クエリパラメータはサポートされていません。
名前付き定数またはクエリパラメータに名前を使用する場合は、エイリアスを使用して明示的に指定します。
異なる pivot_column が同じデフォルト列名を持つ特殊なケースが存在します。たとえば、入力列に NULL 値と文字列リテラル "NULL" の両方が含まれる場合があります。この場合、複数のピボット列が同じ名前で作成されます。このような状況を回避するには、ピボット列名にエイリアスを使用します。
pivot_column でエイリアスが指定されていない場合、列名は次のようになります。

元の型	変換後	例
NULL	NULL	入力: NULL 出力: 「NULL」
INT64 NUMERIC BIGNUMERIC	次のルールを含む文字列形式の数値。正の数値の先頭には `_` が付加されています。負の数値の先頭には `minus_` が付加されています。小数点は `_point_` に置き換えられます。	入力: 1 出力: _1 入力: -1 出力: minus_1 入力: 1.0 出力: _1_point_0
BOOL	`TRUE` または `FALSE`	入力: TRUE 出力: TRUE 入力: FALSE 出力: FALSE
STRING	文字列値。	入力: "PlayerName" 出力: PlayerName
DATE	日付（`_YYYY_MM_DD` 形式）。	入力: DATE '2013-11-25' 出力: _2013_11_25
ENUM	列挙型定数の名前。	入力: COLOR.RED 出力: RED
STRUCT	各フィールドの `pivot_column` 名を計算し、結果をアンダースコアで結合して形成される文字列。以下のルールが適用されます。フィールドに名前が付いている場合: `<field_name>_<pivot_column_name_for_field_name>`。名前のないフィールドの場合: `<pivot_column_name_for_field_name>`。 `<pivot_column_name_for_field_name>` は、この表のルールを再帰的に適用して決定されます。任意の `STRUCT` フィールドに適用できるルールが存在しない場合は、ピボット列全体が名前なしになります。 `IN` リスト値から `<value-expression>` の型への暗黙的な型の強制変換が行われるため、ピボット列の名前に対して影響を与えるには、フィールド名が `input_column` に存在する必要があります。	入力: STRUCT("one", "two") 出力: one_two 入力: STRUCT("one" AS a, "two" AS b) 出力: one_a_two_b
その他のすべてのデータ型	サポートされていません。エイリアスを指定する必要があります。

例

次の例では、以下のような Produce というテーブルを参照しています。

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
  SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
  SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
  SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
  SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
  SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
  SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
  SELECT 'Apple', 1, 'Q1', 2021)
SELECT * FROM Produce

+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale    | 51    | Q1      | 2020 |
| Kale    | 23    | Q2      | 2020 |
| Kale    | 45    | Q3      | 2020 |
| Kale    | 3     | Q4      | 2020 |
| Kale    | 70    | Q1      | 2021 |
| Kale    | 85    | Q2      | 2021 |
| Apple   | 77    | Q1      | 2020 |
| Apple   | 0     | Q2      | 2020 |
| Apple   | 1     | Q1      | 2021 |
+---------+-------+---------+------+

PIVOT 演算子を使用すると、quarter 列の行が新しい列（Q1、Q2、Q3、Q4）に変換されます。集計関数 SUM は、pivot_column 以外のすべての集計されていない列（product と year）によって暗黙的にグループ化されます。

SELECT * FROM
  Produce
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+------+----+------+------+------+
| product | year | Q1 | Q2   | Q3   | Q4   |
+---------+------+----+------+------+------+
| Apple   | 2020 | 77 | 0    | NULL | NULL |
| Apple   | 2021 | 1  | NULL | NULL | NULL |
| Kale    | 2020 | 51 | 23   | 45   | 3    |
| Kale    | 2021 | 70 | 85   | NULL | NULL |
+---------+------+----+------+------+------+

year を含めない場合、SUM は product でのみグループ化されます。

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+-----+-----+------+------+
| product | Q1  | Q2  | Q3   | Q4   |
+---------+-----+-----+------+------+
| Apple   | 78  | 0   | NULL | NULL |
| Kale    | 121 | 108 | 45   | 3    |
+---------+-----+-----+------+------+

pivot_column の値のサブセットを選択できます。

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3')
+---------+-----+-----+------+
| product | Q1  | Q2  | Q3   |
+---------+-----+-----+------+
| Apple   | 78  | 0   | NULL |
| Kale    | 121 | 108 | 45   |
+---------+-----+-----+------+

SELECT * FROM
  (SELECT sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3')
+-----+-----+----+
| Q1  | Q2  | Q3 |
+-----+-----+----+
| 199 | 108 | 45 |
+-----+-----+----+

PIVOT には複数の集計関数を含めることができます。この場合、各集計にエイリアスを指定する必要があります。これらのエイリアスは、結果のテーブルの列名を構築するために使用されます。

SELECT * FROM
  (SELECT product, sales, quarter FROM Produce)
  PIVOT(SUM(sales) total_sales, COUNT(*) num_records FOR quarter IN ('Q1', 'Q2'))

+--------+----------------+----------------+----------------+----------------+
|product | total_sales_Q1 | num_records_Q1 | total_sales_Q2 | num_records_Q2 |
+--------+----------------+----------------+----------------+----------------+
| Kale   | 121            | 2              | 108            | 2              |
| Apple  | 78             | 2              | 0              | 1              |
+--------+----------------+----------------+----------------+----------------+

UNPIVOT 演算子

FROM from_item[, ...] unpivot_operator

unpivot_operator:
    UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
        { single_column_unpivot | multi_column_unpivot }
    ) [unpivot_alias]

single_column_unpivot:
    values_column
    FOR name_column
    IN (columns_to_unpivot)

multi_column_unpivot:
    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set:
    (values_column[, ...])

columns_to_unpivot:
    unpivot_column [row_value_alias][, ...]

column_sets_to_unpivot:
    (unpivot_column [row_value_alias][, ...])

unpivot_alias and row_value_alias:
    [AS] alias

UNPIVOT 演算子は、列を行に変換します。UNPIVOT は FROM 句の一部です。

UNPIVOT を使用して、任意のテーブル式を変更できます。
UNPIVOT と FOR SYSTEM_TIME AS OF の組み合わせは許可されませんが、FOR SYSTEM_TIME AS OF を使用するサブクエリ入力に対して UNPIVOT を使用できます。
UNPIVOT 演算子の直前に WITH OFFSET 句を使用することはできません。
UNPIVOT では、PIVOT の集計を元に戻せません。

概念例:

-- Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

-- After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

定義

最上位の定義:

from_item: ピボット演算を実行するテーブルまたはサブクエリ。from_item はこれらのルールに従う必要があります。
unpivot_operator: from_item で実行するピボット演算。

unpivot_operator 定義:

INCLUDE NULLS: NULL 値を含む行を結果に追加します。
EXCLUDE NULLS: NULL 値を持つ行を結果に追加しません。デフォルトでは、UNPIVOT は NULL 値を持つ行を除外します。
single_column_unpivot: 列を 1 つの values_column と 1 つの name_column に変換します。
multi_column_unpivot: 列を複数の values_column と 1 つの name_column に変換します。
unpivot_alias: UNPIVOT 演算の結果のエイリアス。このエイリアスは、クエリの他の場所で参照できます。

single_column_unpivot 定義:

values_column: columns_to_unpivot の行の値を格納する列。値列を作成する際は、これらのルールに従います。
name_column: columns_to_unpivot の列名を格納する列。名前列を作成する際は、これらのルールに従います。
columns_to_unpivot: values_column と name_column を入力する from_item の列。ピボット解除列を作成する際は、これらのルールに従います。
- row_value_alias: name_column の列に対して表示される列の任意のエイリアス。指定しない場合、列名の文字列値が使用されます。行値のエイリアスを作成する場合は、これらのルールに従います。

multi_column_unpivot 定義:

values_column_set: columns_to_unpivot の行の値を格納する一連の列。値列を作成する際は、これらのルールに従います。
name_column: columns_to_unpivot の列名を含む一連の列。名前列を作成する際は、これらのルールに従います。
column_sets_to_unpivot: ピボット解除する from_item の列。ピボット解除列を作成する際は、これらのルールに従います。
- row_value_alias: name_column の列セットに対して表示される列セットの任意のエイリアス。指定しない場合は、列セットの文字列値が使用され、文字列の各列はアンダースコア（_）で区切られます。たとえば、(col1, col2) は col1_col2 になります。行値のエイリアスを作成する場合は、これらのルールに従います。

ルール

UNPIVOT に渡される from_item のルール:

from_item は、任意のテーブルまたはサブクエリの結果で構成されます。
from_item によって値テーブルが生成されない場合があります。
from_item 内の重複列は UNPIVOT 句で参照できません。

unpivot_operator のルール:

式は使用できません。
修飾名は使用できません。たとえば、mytable.mycolumn は許可されません。
UNPIVOT の結果に重複する列名が含まれている場合:
- SELECT * は許可されます。
- SELECT values_column を指定すると、曖昧さが発生します。

values_column のルール:

name_column または unpivot_column に使用する名前にすることはできません。
from_item の列と同じ名前を使用できます。

name_column のルール:

values_column または unpivot_column に使用する名前にすることはできません。
from_item の列と同じ名前を使用できます。

unpivot_column のルール:

from_item の列名を指定する必要があります。
重複する from_item 列名は参照できません。
列セット内のすべての列は、同等のデータ型を持つ必要があります。
- データ型を共通のスーパータイプに強制的に変換することはできません。
- データ型が完全に一致する場合（フィールド名が異なる構造体など）、最初の入力のデータ型が出力のデータ型になります。
同じ列セットに同じ名前を付けることはできません。たとえば、(emp1, emp1) はエラーになります。
異なる列セットで同じ名前を使用できます。たとえば、(emp1, emp2), (emp1, emp3) は有効です。

row_value_alias のルール:

これは、STRING または INT64 のリテラルです。
すべての row_value_alias 句のデータ型を同じにする必要があります。
値が INT64 の場合は、各 unpivot_column の row_value_alias を指定する必要があります。

例

次の例では、以下のような Produce というテーブルを参照しています。

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

UNPIVOT 演算子を使用すると、Q1、Q2、Q3、Q4 列が変換されます。これらの列の値には Sales という新しい列が入力され、これらの列の名前に Quarter という新しい列が入力されます。これは、単一列のピボット解除演算です。

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

この例では、UNPIVOT で 4 学期が 2 学期に変換されています。これは複数列のピボット解除演算です。

SELECT * FROM Produce
UNPIVOT(
  (first_half_sales, second_half_sales)
  FOR semesters
  IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))

+---------+------------------+-------------------+------------+
| product | first_half_sales | second_half_sales | semesters  |
+---------+------------------+-------------------+------------+
| Kale    | 51               | 23                | semester_1 |
| Kale    | 45               | 3                 | semester_2 |
| Apple   | 77               | 0                 | semester_1 |
| Apple   | 25               | 2                 | semester_2 |
+---------+------------------+-------------------+------------+

TABLESAMPLE 演算子

TABLESAMPLE SYSTEM ( percent PERCENT )

説明

TABLESAMPLE 演算子を使用して、データセットのランダムサンプルを選択できます。この演算子は、大量のデータがあり、正確な答えを必要としないテーブルを操作する場合に便利です。

サンプリングは、テーブル全体のスキャンと処理に関連する費用を回避しながら、さまざまなレコードを返します。実行ごとに個別に計算されたサンプルを処理するため、クエリが実行されるたびに異なる結果が返されることがあります。BigQuery は、TABLESAMPLE 句を含むクエリの結果をキャッシュに保存しません。

percent は、結果に含めるデータセットの割合に置き換えます。値は 0～100 の範囲で指定する必要があります。値は、リテラル値またはクエリパラメータです。変数にすることはできません。

詳細については、テーブルのサンプリングをご覧ください。

例

次のクエリでは、テーブルのデータの約 10% を選択しています。

SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)

JOIN 演算

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator from_item

condition_join_operation:
    from_item condition_join_operator from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

condition_join_operator:
    {
      [INNER] JOIN
      | FULL [OUTER] JOIN
      | LEFT [OUTER] JOIN
      | RIGHT [OUTER] JOIN
    }

join_condition:
    { on_clause | using_clause }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

JOIN 演算は 2 つの from_item を結合して、SELECT 句でそれらを 1 つのソースとしてクエリできるようにします。join_type と ON または USING 句（「結合条件」）によって、2 つの from_item で行の結合と廃棄を行い、1 つのソースを形成する方法が指定されます。

[INNER] JOIN

INNER JOIN または単に JOIN は、2 つの from_item のデカルト積を効率的に計算し、結合条件を満たさないすべての行を廃棄します。「効率的に」とは、実際にデカルト積を計算せずに、INNER JOIN を実行することができることを意味します。

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+

FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

例

次のクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

CROSS JOIN

CROSS JOIN は、2 つの from_item のデカルト積を返します。言い換えれば、最初の from_item の各行を 2 番目の from_item の各行と結合します。

2 つの from_item の行が独立している場合、一方の from_item が M 行、もう一方が N 行とすると、結果は M * N 行になります。これは、どちらかの from_item がゼロ行の場合にも当てはまります。

FROM 句では、CROSS JOIN を次のように記述できます。

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

相関クロス結合を使用すると、ARRAY を一連の行に変換またはフラット化できます。詳細については、配列内の要素をテーブル内の行に変換するをご覧ください。

例

次のクエリは、Roster テーブルと TeamMascot テーブルに対して CROSS JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

カンマクロス結合（,）

CROSS JOIN は、カンマを使用して暗黙的に記述できます。これをカンマクロス結合と呼びます。

カンマクロス結合は、FROM 句内で次のようになります。

FROM A, B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

括弧内に、カンマクロス結合を記述することはできません。詳細については、シーケンス内の結合演算をご覧ください。

FROM (A, B)  // INVALID

相関カンマクロス結合を使用すると、ARRAY を一連の行に変換またはフラット化できます。詳細については、配列内の要素をテーブル内の行に変換するをご覧ください。

例

このクエリでは、Roster テーブルと TeamMascot テーブルに対してカンマクロス結合を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

FULL [OUTER] JOIN

FULL OUTER JOIN（または単に FULL JOIN）は、結合条件を満たす両方の from_item 内のすべての行のすべてのフィールドを返します。

FULL は、結合条件を満たさない場合でも、両方の from_item からすべての行が返されるように指定します。

OUTER は 1 つの from_item の指定された行が他の from_item の任意の行に結合しない場合、行が他の from_item からのすべての列に対して NULL を返すことを示します。

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

例

このクエリは、Roster テーブルと TeamMascot テーブルに対して FULL JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

LEFT [OUTER] JOIN

2 つの from_item に対する LEFT OUTER JOIN（または単に LEFT JOIN）の結果は、右側の from_item に結合の述語を満たす行がない場合でも、常に JOIN 演算の左側の from_item のすべての行を保持します。

LEFT は、左側の from_item からすべての行が返されることを示します。左側の from_item のいずれかの行が右側の from_item のどの行とも結合しない場合、行は右側の from_item のすべての列に対して NULL を返します。左側の from_item のどの行にも結合されない右側の from_item の行は破棄されます。

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+

FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

例

このクエリは、Roster テーブルと TeamMascot テーブルに対して LEFT JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN（または単に RIGHT JOIN）の結果は、LEFT OUTER JOIN の結果と同様になり、対称的になります。

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

例

このクエリは、Roster テーブルと TeamMascot テーブルに対して RIGHT JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

ON 句

結合された行（2 つの行を結合した結果）は、結合条件で TRUE が返された場合、ON 結合条件を満たします。

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

例

このクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

USING 句

USING 句では、両方の入力テーブルで発生する 1 列以上の列リストが必要です。その列で等価比較を行います。等価比較で TRUE が返された場合、その行は結合条件を満たします。

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

例

このクエリは、Roster テーブルと TeamMascot テーブルに対して INNER JOIN を実行します。

このステートメントは、Roster.SchoolID が TeamMascot.SchoolID と同じ場合に Roster と TeamMascot から行を返します。結果には、単一の SchoolID 列が含まれます。

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

ON と USING の等価性

キーワードの ON と USING は等価ではありませんが類似しています。ON では複数の列が返され、USING では 1 列が返されます。

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

ON と USING は等価ではありませんが、返す列を指定すると同じ結果が得られます。

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

シーケンス内の結合演算

FROM 演算には、複数の JOIN 句を続けて含めることができます。JOIN は左から右にバインドされます。次に例を示します。

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

丸括弧を挿入して JOIN をグループ化することも可能です。

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

丸括弧を使用すると、異なる順序でバインドされるように JOIN をグループ化できます。

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

FROM 句は複数の結合を含むことができます。括弧は読みやすさを向上させますが、FROM 句にカンマクロス結合がない場合に限り、結合に括弧は不要になります。

FROM A JOIN B JOIN C JOIN D USING (w) ON B.x = C.y ON A.z = B.x

句にカンマクロス結合が含まれている場合は、丸括弧を使用する必要があります。

FROM A, B JOIN C JOIN D ON C.x = D.y ON B.z = C.x    // INVALID

FROM A, B JOIN (C JOIN D ON C.x = D.y) ON B.z = C.x  // VALID

カンマクロス結合が JOIN の順序でクエリにある場合、それらは他の JOIN データ型と同様に、左から右にグループ化されます。

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

丸括弧で囲まれた場合を除き、カンマクロス結合の後に、RIGHT JOIN または FULL JOIN があってはありません。

FROM A, B RIGHT JOIN C ON TRUE // INVALID

FROM A, B FULL JOIN C ON TRUE  // INVALID

FROM A, B JOIN C ON TRUE       // VALID

FROM A, (B RIGHT JOIN C ON TRUE) // VALID

FROM A, (B FULL JOIN C ON TRUE)  // VALID

相関結合演算

右の from_item に、左の from_item により導入された 1 つ以上の範囲変数または列名への参照が含まれている場合、結合演算は相関しています。

相関結合演算では、右の from_item の行は左の from_item の行によって決まります。したがって、左の from_item の行がない場合には右の from_item 行を決定できないため、RIGHT OUTER と FULL OUTER の結合は関連付けることができません。

すべての相関結合演算では、右の from_item 内の配列を参照する必要があります。

これは、相関サブクエリを含む相関結合演算の概念例です。

FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C

左の from_item: A
右の from_item: UNNEST(...) AS C
相関サブクエリ: (SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

これは、相関結合演算のもう 1 つの概念例です。array_of_IDs は左側の from_item の一部ですが、右側の from_item で参照されています。

FROM A JOIN UNNEST(A.array_of_IDs) AS C

UNNEST 演算子は、明示的にも、暗黙的にも使用できます。どちらも許可されます。

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs

FROM A JOIN A.array_of_IDs AS IDs

相関結合演算では、左の from_item の各行に対して、右の from_item が再評価されます。次の概念例では、相関結合演算によって最初に A と B が評価され、次に A と C が評価されます。

FROM
  A
  JOIN
  UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  ON A.Name = C.Name

注意点

相関 LEFT JOIN では、左側の行に対する右側の入力テーブルが空の場合、通常の LEFT JOIN で結合条件を満たす右側からの行が存在しないように扱われます。結合する行がない場合、右側のすべての列に NULL 値で行が生成され、左側から行と結合されます。
相関 CROSS JOIN では、左側の行に対する右側の入力テーブルが空の場合、通常の相関 INNER JOIN で結合条件を満たす右側からの行が存在しないように扱われます。つまり、その行が結果から除外されます。

例

これは、Roster テーブルと PlayerStats テーブルを使用した相関結合の例です。

SELECT *
FROM
  Roster
JOIN
  UNNEST(
    ARRAY(
      SELECT AS STRUCT *
      FROM PlayerStats
      WHERE PlayerStats.OpponentID = Roster.SchoolID
    )) AS PlayerMatches
  ON PlayerMatches.LastName = 'Buchanan'

+------------+----------+----------+------------+--------------+
| LastName   | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams      | 50       | Buchanan | 50         | 13           |
| Eisenhower | 77       | Buchanan | 77         | 0            |
+------------+----------+----------+------------+--------------+

相関 LEFT JOIN の一般的なパターンは、右側に UNNEST オペレーションがあり、左側への入力で導入された列から配列を参照するというものです。配列が空であるか NULL の場合、UNNEST オペレーションでは右入力に行が生成されません。この場合、右入力の各列に NULL エントリが含まれる行が作成され、左入力からの行と結合されます。例:

SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
LEFT JOIN
  A.items AS item;

+--------+------+---------------------+
| name   | item | item_count_for_name |
+--------+------+---------------------+
| first  | 1    | 4                   |
| first  | 2    | 4                   |
| first  | 3    | 4                   |
| first  | 4    | 4                   |
| second | NULL | 0                   |
+--------+------+---------------------+

相関 CROSS JOIN の場合、右側の入力が左側からの行に対して空の場合、最終行は結果から削除されます。例:

SELECT A.name, item
FROM
  UNNEST(
    [
      STRUCT(
        'first' AS name,
        [1, 2, 3, 4] AS items),
      STRUCT(
        'second' AS name,
        [] AS items)]) AS A
CROSS JOIN
  A.items AS item;

+-------+------+
| name  | item |
+-------+------+
| first | 1    |
| first | 2    |
| first | 3    |
| first | 4    |
+-------+------+

WHERE 句

WHERE bool_expression

WHERE 句は FROM 句の結果をフィルタリングします。

bool_expression が TRUE と評価された行のみが含まれます。bool_expression が NULL または FALSE と評価された行は破棄されます。

WHERE 句を使用したクエリの評価は、通常、次の順序で完了します。

FROM
WHERE
GROUP BY と集計
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

評価順序は、必ずしも構文順序と一致するわけではありません。

WHERE 句は、FROM 句のみを介して使用可能な列を参照します。SELECT リストのエイリアスは参照できません。

例

このクエリは、Roster テーブルから、SchoolID 列の値が 52 であるすべての行を返します。

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression には、複数の従属条件を含めることができます。

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

INNER JOIN の式には、WHERE 句に同等の式があります。たとえば、INNER JOIN と ON を使用するクエリには、CROSS JOIN と WHERE を使用する同等の式があります。たとえば、次のクエリは同等です。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY 句

GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }

GROUP BY 句は、GROUP BY 句にある expression の値が同じ行をグループ化し、1 つのテーブルにまとめます。ソーステーブルのうち、expression の値が同じ複数の行に対して、GROUP BY 句は 1 つの結合行を生成します。一般に GROUP BY は、集計関数が SELECT リストに存在する場合に、または出力の冗長性を排除する場合に使用されます。expression のデータ型は Groupable にする必要があります。

例:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

GROUP BY 句は SELECT リストで式の名前を参照できます。また GROUP BY 句では、整数値を使用して SELECT リスト内の式をその順序で参照できます。1 は SELECT リストの最初の式、2 は 2 番目の式というように参照します。式リストでは、順序数と式名を組み合わせることができます。

例:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

上記のクエリは、次のものと同等です。

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

GROUP BY 句もまた、エイリアスを参照する場合があります。クエリの SELECT 句にエイリアスが含まれる場合、これらのエイリアスは、対応する FROM 句の名前をオーバーライドします。

例:

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

GROUP BY ROLLUP は、ROLLUP リスト内の式の接頭辞に対する GROUP BY の結果を返します。各接頭辞はグループ化セットと呼ばれます。ROLLUP リスト (a, b, c) の場合、グループ化セットは (a, b, c)、(a, b)、(a)、() です。特定のグループ化セットの GROUP BY の結果を評価する場合、GROUP BY ROLLUP はグループ化セットに含まれない式を NULL 値があるものとして処理します。次のような SELECT 文では、

SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);

ロールアップリスト (a, b) が使用されています。結果には、すべての行が含まれるグループ化セット (a, b)、(a)、() の GROUP BY の結果が含まれます。これによって、次と同じ行が返されます。

SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

これにより、ROLLUP リスト内の式とそのリストの接頭辞によって定義されたグループ化セットの集計の計算が可能になります。

例:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);

上記のクエリは、すべての日のロールアップされた合計に加えて、NULL 日と示されている各日に対する行を出力します。

+------+-------+
| day  | total |
+------+-------+
| NULL | 39.77 |
|    1 | 23.54 |
|    2 |  9.99 |
|    3 |  6.24 |
+------+-------+

例:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

上記のクエリは、次のグループ化セットによってグループ化された行を返します。

sku と day
sku（day は NULL）
空のグループ化セット（day と sku が NULL）

これらのグループ化セットの合計は、sku と day の組み合わせごとの合計、すべての日の各 sku の合計、および総計に対応しています。

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
| NULL | NULL | 39.77 |
|  123 | NULL | 28.97 |
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  456 | NULL |  8.81 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
+------+------+-------+

HAVING 句

HAVING bool_expression

HAVING 句は、GROUP BY または集計で生成された結果をフィルタリングします。GROUP BY または集計がクエリに存在する必要があります。集計が存在する場合、結果セットの集計行ごとに HAVING 句が 1 回評価されます。

bool_expression が TRUE と評価された行のみが含まれます。bool_expression が NULL または FALSE と評価された行は破棄されます。

HAVING 句を使用したクエリの評価は、通常、次の順序で完了します。

FROM
WHERE
GROUP BY と集計
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

評価順序は、必ずしも構文順序と一致するわけではありません。

HAVING 句は FROM 句のみならず SELECT リストエイリアス経由でも使用できる列を参照します。HAVING で参照される式は、GROUP BY 句に出現するか、または集計関数の結果である必要があります。

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

クエリに SELECT 句のエイリアスが含まれる場合、これらのエイリアスは FROM 句の名前をオーバーライドします。

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

必須の集計

集計は HAVING 句自体に存在する必要はありませんが、集計は以下の形式の少なくとも 1 つで存在しなければなりません。

`SELECT` リストの集計関数

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

HAVING 句の集計関数。

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

`SELECT` リストと `HAVING` 句の両方の集計

集計関数が SELECT リストと HAVING 句の両方に存在する場合、集計関数とそれらが参照する列は同じである必要がありません。以下の例では、2 つの集計関数 COUNT() と SUM() は異なり、列も異なるものを使用します。

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

ORDER BY 句

ORDER BY expression
  [{ ASC | DESC }]
  [{ NULLS FIRST | NULLS LAST }]
  [, ...]

ORDER BY 句は結果セットの並べ替え基準として列または式を指定します。ORDER BY 句が存在しない場合、クエリ結果の順序は定義されません。FROM 句からの列のエイリアスまたは SELECT リストを使用できます。クエリの SELECT 句にエイリアスが含まれる場合、これらのエイリアスは、対応する FROM 句の名前をオーバーライドします。expression のデータ型は Orderable である必要があります。

省略可能な句

NULLS FIRST | NULLS LAST:
- NULLS FIRST: Null 以外の値の前に Null 値がくるように並べ替えます。
- NULLS LAST: Null 以外の値の後に Null 値がくるように並べ替えます。
ASC | DESC: 結果を、expression の値の昇順または降順に並べ替えます。ASC がデフォルト値です。NULLS FIRST または NULLS LAST で Null の順序が指定されない場合は、次のように処理されます。
- 並べ替え順序が昇順の場合、デフォルトでは NULLS FIRST が適用されます。
- 並べ替え順序が降順の場合、デフォルトでは NULLS LAST が適用されます。

例

デフォルトの並べ替え順序（昇順）を使用します。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 1    | true  |
| 9    | true  |
+------+-------+

デフォルトの並べ替え順序（昇順）を使用しますが、Null 値は最後に返します。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x NULLS LAST;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
| NULL | false |
+------+-------+

降順で並べ替えます。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
| NULL | false |
+------+-------+

降順の並べ替えを使用しますが、初めに Null 値を返します。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 9    | true  |
| 1    | true  |
+------+-------+

複数の列を基準にした並べ替えを行うことができます。下の例では、結果セットは最初に SchoolID で、次に LastName で並べ替えられます。

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

集合演算子と組み合わせて使用すると、ORDER BY 句はクエリ全体の結果セットに適用されます。最も近くにある SELECT ステートメントだけに適用されるわけではありません。このため、かっこを使用して ORDER BY の範囲を示すと便利です（ただし、必須ではありません）。

括弧なしの次のクエリがあるとします。

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

これは、括弧ありのこのクエリと同等です。

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

ただし、ORDER BY 句が 2 番目の SELECT ステートメントにのみ適用される次のクエリとは同等ではありません。

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

ORDER BY 句では、整数リテラルを列参照として使用することもできます。整数リテラルは SELECT リストの順序となります（例: カウントは 1 から開始）。

例 - 次の 2 つのクエリは同等です。

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;

QUALIFY 句

QUALIFY bool_expression

QUALIFY 句は、ウィンドウ関数の結果をフィルタリングします。ウィンドウ関数は、QUALIFY 句または SELECT リストに含める必要があります。

bool_expression が TRUE と評価された行のみが含まれます。bool_expression が NULL または FALSE と評価された行は破棄されます。

QUALIFY 句を使用したクエリの評価は、通常、次の順序で完了します。

FROM
WHERE
GROUP BY と集計
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

評価順序は、必ずしも構文順序と一致するわけではありません。

例

次のクエリは、Produce テーブルで最も人気のある野菜とそれらのランクを返します。

SELECT
  item,
  RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3

+---------+------+
| item    | rank |
+---------+------+
| kale    | 1    |
| lettuce | 2    |
| cabbage | 3    |
+---------+------+

QUALIFY を使用するために、SELECT リストにウィンドウ関数を含める必要はありません。次のクエリは、Produce テーブルで最も人気のある野菜を返します。

SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3

+---------+
| item    |
+---------+
| kale    |
| lettuce |
| cabbage |
+---------+

WINDOW 句

WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

WINDOW 句は名前付きウィンドウのリストを定義します。名前付きウィンドウは、ウィンドウ関数を使用するテーブル内の行のグループを表します。名前付きウィンドウは、ウィンドウ指定で定義するか、別の名前付きウィンドウを参照することが可能です。別の名前付きウィンドウを参照している場合、参照されるウィンドウは、参照するウィンドウよりも前に定義されている必要があります。

例

この例では、Produce というテーブルを参照しています。どの例でもすべて同じ結果が返されます。名前付きウィンドウは、組み合わせや、ウィンドウ関数の OVER 句での使用などさまざまな方法で利用できます。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (d) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  d AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS b

集合演算子

set_operation:
  query_expr set_operator query_expr

set_operator:
  UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

集合演算子は、2 つ以上の入力クエリの結果を 1 つの結果セットに結合します。ALL または DISTINCT を指定する必要があります。ALL を指定する場合、すべての行が保留されます。DISTINCT が指定された場合、重複する行は廃棄されます。

指定された行 R が最初の入力クエリで正確に m 回表示され、2 番目の入力クエリで n 回表示される場合（m >= 0, n >= 0）:

UNION ALL の場合、R は結果で正確に m + n 回表示されます。
UNION DISTINCT の場合、DISTINCT は UNION が計算された後に計算されるため、R は正確に 1 回表示されます。
INTERSECT DISTINCT の場合、上記の結果が計算された後に DISTINCT が計算されます。
EXCEPT DISTINCT の場合、m> 0 かつ n = 0 の場合に行 R が出力に 1 回表示されます。
2 つ以上の入力クエリがある場合、上記のオペレーションは概算され、出力は入力が左から右へ結合されて漸増する場合と同じになります。

以下のルールが適用されます。

UNION ALL 以外の set オペレーションの場合、すべての列の型で等価比較がサポートされている必要があります。
演算子の左右の入力クエリが、同じ数の列を返さなければなりません。
演算子は、該当する SELECT リストの列の位置に従って、各入力クエリにより返される列をペアリングします。つまり、最初の入力クエリの最初の列は、2 番目の入力クエリの最初の列とペアリングされます。
結果セットは常に、最初の入力クエリの列名を使用します。
結果セットは常に対応する列の入力データ型のスーパーデータ型を使用するため、ペアリングした列もまた同じデータ型であるか、共通のスーパーデータ型でなければなりません。
UNION ALL や UNION DISTINCT などの異なる set オペレーションの処理を行う場合は、括弧を使用してそれらを区切る必要があります。ステートメントが同じ set オペレーションを繰り返すだけであれば、括弧は必要ありません。

例:

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

無効:

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

UNION 演算子は各クエリの結果セットの列を組み合わせて、縦方向に連結させることにより、2 つ以上の入力クエリの結果セットを結合します。

INTERSECT

INTERSECT 演算子は、左右の入力クエリの結果セットにある行を戻します。EXCEPT とは異なり、入力クエリの配置（INTERSECT 演算子の左辺と右辺）は重要ではありません。

EXCEPT

EXCEPT 演算子は、左側の入力クエリに存在し、右側の入力クエリには存在しない行を返します。

例:

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

LIMIT 句と OFFSET 句

LIMIT count [ OFFSET skip_rows ]

LIMIT はデータ型 INT64 の負ではない count を指定し、count を超えない行数が返されます。LIMIT 0 で返される行数は 0 です。

set オペレーションが存在する場合、set オペレーションを評価してから LIMIT が適用されます。

OFFSET は、LIMIT を適用する前にスキップする行数を示す 0 以上の数を指定します。skip_rows のデータ型は INT64 です。

これらの句はリテラルまたはパラメータの値のみを受け付けます。LIMIT と OFFSET により返される行は、これらの演算子が ORDER BY の後に使用されない限り、未指定になります。

例:

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

WITH 句

WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...]

WITH 句には 1 つ以上の共通テーブル式（CTE）が含まれています。CTE は、単一のクエリ式で参照できる一時テーブルのような役割を果たします。各 CTE は、サブクエリの結果をテーブル名にバインドします。このテーブルは、同じクエリ式の他の場所で使用できますが、ルールが適用されます。

CTE は、非再帰または再帰のいずれかになります。これらは両方とも WITH 句に含めることができます。再帰 CTE はそれ自体を参照しますが、非再帰 CTE は参照しません。WITH 句に再帰 CTE が含まれている場合は、RECURSIVE キーワードも含める必要があります。

再帰 CTE が存在しない場合でも、WITH 句に RECURSIVE キーワードを含めることができます。RECURSIVE キーワードの詳細については、こちらをご覧ください。

BigQuery は再帰的な CTE の結果のみを実体化しますが、非再帰 CTE の結果は WITH 句内に実体化しません。クエリ内の複数の場所で CTE が参照されている場合、CTE は参照ごとに 1 回実行されます。非再帰 CTE を含む WITH 句は、主に可読性を高めるために役立ちます。

RECURSIVE キーワード

WITH 句には、オプションで RECURSIVE キーワードを含めることができます。これにより、次の 2 つのことが行われます。

WITH 句で再帰を有効にします。このキーワードが存在しない場合は、非再帰的な共通テーブル式（CTE）のみを含めることができます。このキーワードが存在する場合、再帰 CTE と非再帰 CTE の両方を使用できます。
WITH 句の CTE の公開設定を変更します。このキーワードが存在しない場合、WITH 句でその後に定義された CTE にのみ CTE が表示されます。このキーワードが存在する場合、WITH 句内のすべての CTE に CTE が表示されます。

非再帰 CTE

non_recursive_cte:
    cte_name AS ( query_expr )

非再帰的な共通テーブル式（CTE）には、非再帰的なサブクエリと、CTE に関連付けられた名前が含まれています。

非再帰 CTE はそれ自体を参照できません。
非再帰 CTE は、WITH 句を含むクエリ式で参照できますが、ルールが適用されます。

Examples

この例では、WITH 句は 2 つの非再帰 CTE を定義しています。これらの非再帰 CTE は、関連する set オペレーションで参照されています。ここでは、1 つの CTE は、各 set オペレーションの入力クエリ式で参照されています。

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

ネストされたテーブルのサブクエリを記述する代わりに、より複雑なクエリを WITH 句と WITH SELECT ステートメントに分割できます。次に例を示します。

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery on the previous line.
    SELECT * FROM q1)              # q1 resolves to the third inner WITH subquery.

再帰 CTE

recursive_cte:
    cte_name AS ( recursive_union_operation )

recursive_union_operation:
    base_term union_operator recursive_term

base_term:
    query_expr

recursive_term:
    query_expr

union_operator:
    UNION ALL

再帰的な共通テーブル式（CTE）には、再帰的なサブクエリと CTE に関連付けられた名前が含まれています。

再帰 CTE は自身を参照します。
再帰 CTE は、WITH 句を含むクエリ式で参照できますが、ルールが適用されます。
WITH 句で再帰 CTE が定義されている場合は、RECURSIVE キーワードが存在する必要があります。

再帰 CTE は、再帰ユニオン演算で定義されます。再帰ユニオン演算は、入力を再帰的に処理して最終的な CTE 結果を生成する方法を定義します。再帰ユニオン演算は次の部分から構成されます。

base_term: 再帰ユニオン演算の最初のイテレーションを実行します。この用語は基本用語のルールに従う必要があります。
union_operator: UNION 演算子は、基本用語と再帰用語の結合から行を返します。UNION ALL を使用すると、イテレーション N で生成された各行が最終的な CTE 結果の一部になり、イテレーション N+1 の入力になります。イテレーションが、次のイテレーションに移行する行が生成されない場合に停止します。
recursive_term: 残りのイテレーションを実行します。再帰 CTE への自己参照（再帰参照）が 1 つ含まれている必要があります。自己参照を含めることができるのは、この用語のみです。この用語は、再帰的な用語のルールに従う必要があります。

再帰 CTE は次のようになります。

WITH RECURSIVE
  T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) )
SELECT n FROM T1

+---+
| n |
+---+
| 2 |
| 1 |
| 3 |
+---+

再帰ユニオン演算の最初のイテレーションで基本用語が実行されます。その後の各イテレーションでは、再帰的な用語を実行し、前のイテレーションと結合される新しい行を生成します。再帰ユニオン演算は、再帰的なイテレーションによって新しい行が生成されないと終了します。

再帰が終了しない場合、クエリは 100 回イテレーションした後に失敗します。イテレーションはプロジェクトレベルでカスタマイズできます。

許可される再帰 CTE の例

これは単純な再帰 CTE です。

WITH RECURSIVE
  T1 AS (
    (SELECT 1 AS n) UNION ALL
    (SELECT n + 2 FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n

+---+
| n |
+---+
| 1 |
| 3 |
| 5 |
+---+

各再帰サイクルのサイクル長が 1 である限り、同じ再帰 CTE に複数のサブクエリを使用できます。また、再帰エントリは非再帰エントリに依存することもできます。また、その逆も可能です。

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT * FROM T0) UNION ALL (SELECT n + 1 FROM T1 WHERE n < 4)),
  T2 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T2 WHERE n < 4)),
  T3 AS (SELECT * FROM T1 INNER JOIN T2 USING (n))
SELECT * FROM T3 ORDER BY n

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

集計関数は、定義されているテーブルで集計されていない限り、サブクエリで呼び出すことができます。

WITH RECURSIVE
  T0 AS (SELECT * FROM UNNEST ([60, 20, 30])),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + (SELECT COUNT(*) FROM T0) FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n

+---+
| n |
+---+
| 1 |
| 4 |
+---+

INNER JOIN はサブクエリ内で使用できます。

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 INNER JOIN T0 USING (n)))
SELECT * FROM T1 ORDER BY n

+---+
| n |
+---+
| 1 |
| 2 |
+---+

CROSS JOIN はサブクエリ内で使用できます。

WITH RECURSIVE
  T0 AS (SELECT 2 AS p),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT T1.n + T0.p FROM T1 CROSS JOIN T0 WHERE T1.n < 4))
SELECT * FROM T1 CROSS JOIN T0 ORDER BY n

+---+---+
| n | p |
+---+---+
| 1 | 2 |
| 3 | 2 |
| 5 | 2 |
+---+---+

再帰 CTE は、CREATE TABLE AS SELECT ステートメント内で使用できます。次の例では、mydataset に new_table という名前のテーブルが作成されます。

CREATE OR REPLACE TABLE `myproject.mydataset.new_table` AS
  WITH RECURSIVE
    T1 AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM T1 WHERE n < 3)
  SELECT * FROM T1

再帰 CTE は、CREATE VIEW AS SELECT ステートメント内で使用できます。次の例では、mydataset に new_view という名前のビューが作成されます。

CREATE OR REPLACE VIEW `myproject.mydataset.new_view` AS
  WITH RECURSIVE
    T1 AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM T1 WHERE n < 3)
  SELECT * FROM T1

再帰 CTE は、INSERT ステートメント内で使用できます。次の例は、再帰 CTE を使用してテーブルにデータを挿入する方法を示しています。

-- create a temp table.
CREATE TEMP TABLE tmp_table (n INT64);

-- insert some values into the temp table by using recursive CTEs.
INSERT INTO tmp_table(n)
  WITH RECURSIVE
    T1 AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM T1 WHERE n < 3)
  SELECT * FROM T1

許可されていない再帰 CTE の例

次の再帰 CTE は、自己参照に集合演算子、基本用語、再帰用語が含まれていないため、許可されません。

WITH RECURSIVE
  T1 AS (SELECT * FROM T1)
SELECT * FROM T1

-- Error

次の再帰 CTE は、T1 への自己参照が基本用語に含まれているため許可されません。自己参照は再帰的にのみ使用できます。

WITH RECURSIVE
  T1 AS ((SELECT * FROM T1) UNION ALL (SELECT 1))
SELECT * FROM T1

-- Error

再帰用語内には自己参照が 1 つしか存在できない場合に複数の自己参照があるため、次の再帰 CTE は許可されません。

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL ((SELECT * FROM T1) UNION ALL (SELECT * FROM T1)))
SELECT * FROM T1

-- Error

次の再帰 CTE は、自己参照が式サブクエリ内にあるため許可されません。

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT (SELECT n FROM T1)))
SELECT * FROM T1

-- Error

次の再帰 CTE は、外部結合への入力として自己参照があるため許可されません。

WITH RECURSIVE
  T0 AS (SELECT 1 AS n),
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT * FROM T1 FULL OUTER JOIN T0 USING (n)))
SELECT * FROM T1;

-- Error

次の再帰 CTE は、自己参照で集計を使用できないため許可されません。

WITH RECURSIVE
  T1 AS (
    (SELECT 1 AS n) UNION ALL
    (SELECT COUNT(*) FROM T1))
SELECT * FROM T1;

-- Error

次の再帰 CTE は、自己参照でウィンドウ関数 OVER 句を使用できないため許可されません。

WITH RECURSIVE
  T1 AS (
    (SELECT 1.0 AS n) UNION ALL
    SELECT 1 + AVG(n) OVER(ROWS between 2 PRECEDING and 0 FOLLOWING) FROM T1 WHERE n < 10)
SELECT n FROM T1;

-- Error

次の再帰 CTE は、自己参照で LIMIT 句を使用できないため許可されません。

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n FROM T1 LIMIT 3))
SELECT * FROM T1;

-- Error

次の再帰 CTE は、自己参照で ORDER BY 句を使用できないため、使用できません。

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 ORDER BY n))
SELECT * FROM T1;

-- Error

次の再帰 CTE は、テーブル T1 が WITH 句の内部から再帰的に参照できないため、使用できません。

WITH RECURSIVE
  T1 AS ((SELECT 1 AS n) UNION ALL (WITH t AS (SELECT n FROM T1) SELECT * FROM t))
SELECT * FROM T1

-- Error

CTE のルールと制約

共通テーブル式（CTE）は、WITH 句を含むクエリ式内で参照できます。

一般規則

CTE で作業を行う際に考慮すべき一般的なルールと制約は次のとおりです。

同じ WITH 句の各 CTE には一意の名前を付ける必要があります。
WITH 句に再帰的な CTE が含まれている場合は、RECURSIVE キーワードキーワードを含める必要があります。
WITH 句の RECURSIVE キーワードは、同じ WITH 句内の他の CTE への CTE の可視性を変更します。詳しくはこちらをご覧ください。
WITH を WITH RECURSIVE 内で使用することはできません。
WITH RECURSIVE は SELECT ステートメントで許可できます。
WITH RECURSIVE は、クエリの最上位でのみ指定できます。
WITH RECURSIVE は関数では使用できません。
WITH RECURSIVE は、マテリアライズドビューでは使用できません。
WITH RECURSIVE は BigQuery ML では使用できません。
CREATE RECURSIVE VIEW はサポートされていません。この問題を回避するには、CREATE VIEW ステートメントで WITH RECURSIVE 句を query_expression として使用します。詳細については、ビューの作成をご覧ください。
ローカル CTE は、同じ名前の外側の CTE またはテーブルをオーバーライドします。
サブクエリの CTE では、外側のクエリから相関列を参照できません。

基本用語のルール

再帰 CTE の基本用語には、次のルールが適用されます。

基本用語は非再帰である必要があります。
基本用語は、すべてのテーブル列の名前と型を決定します。
基本用語に STRUCT の出力列を含めることはできません。

再帰用語のルール

再帰 CTE の再帰用語には、次のルールが適用されます。

再帰用語には、基本用語の再帰定義テーブルへの参照を 1 つだけ含める必要があります。
再帰用語には基本用語と同じ数の列を含める必要があります。また、各列の型は、基本用語の対応する列の型に暗黙的に強制型変換できる必要があります。
再帰テーブル参照は、FULL JOIN のオペランド、LEFT JOIN の右側のオペランド、RIGHT JOIN の左側のオペランドとして使用できません。
TABLESAMPLE 演算子では再帰テーブル参照を使用できません。
再帰用語には、非決定性演算子を使用しないでください。
IN と EXISTS 式のサブクエリの使用は、再帰用語の中では制限されます。例:
- [NOT] IN と [NOT] EXISTS は SELECT 句では許可されません。
- NOT IN は WHERE 句では許可されません。

再帰用語内のサブクエリには次のルールが適用されます。

再帰テーブル参照を持つサブクエリは、UNION ALL などのセットオペレーションではなく、SELECT 式である必要があります。
サブクエリに直接または間接的に、FROM 句外の再帰テーブル参照を含めることはできません。
再帰テーブル参照を含むサブクエリには、ORDER BY 句または LIMIT 句を含めることはできません。
再帰テーブル参照を含むサブクエリは、集計関数を呼び出すことはできません。
再帰テーブル参照を含むサブクエリは、ウィンドウ関数を呼び出すことはできません。
再帰テーブル参照を含むサブクエリには、DISTINCT キーワードまたは GROUP BY 句を含めることはできません。

CTE の可視性

クエリ式内の共通テーブル式（CTE）の可視性は、CTE が定義されている WITH 句に RECURSIVE キーワードを追加するかどうかによって決まります。これらの違いについては、次のセクションで詳しく説明します。

`RECURSIVE` キーワードを使用した `WITH` 句の CTE の可視性

RECURSIVE キーワードを含めると、WITH 句の CTE 間の参照が逆方向または逆方向に移動します。サイクルは使用できません。

この現象は、2 つの CTE が RECURSIVE キーワードを使用した WITH 句で自己参照または相互参照している場合に発生します。句の中で最初の CTE が A、2 番目の CTE が B であるとします。

参照 A = 有効
参照 B = 有効
B～A の参照 = 有効
A～B～A の参照 = 無効（循環参照はできません）

A は自己参照がサポートされているため、それ自体を参照できます。

WITH RECURSIVE
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
+---+

CTE 間の前方参照はできるため、A は B を参照できます。

WITH RECURSIVE
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

CTE 間の後方参照はできるため、B は A を参照できます。

WITH RECURSIVE
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

これによりエラーが発生します。A と B が相互参照しているため、循環参照になります。

WITH RECURSIVE
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

`RECURSIVE` キーワードのない `WITH` 句での CTE の可視性

WITH 句に RECURSIVE キーワードを含めない場合、句内の CTE 間は後方参照できますが、前方参照はできません。

この現象は、2 つの CTE が RECURSIVE キーワードなしの WITH 句で自己参照または相互参照している場合に発生します。句の中で最初の CTE が A、2 番目の CTE が B であるとします。

A～A の参照 = 無効
A～B の参照 = 無効
B～A の参照 = 有効
A～B～A の参照 = 無効（循環参照はできません）

これによりエラーが発生します。A は自己参照がサポートされていないため、自身を参照できません。

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

これによりエラーが発生します。CTE 間の後方参照は可能ですが、前方参照はできないため、A は B を参照できません。

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

CTE 間の後方参照はできるため、B は A を参照できます。

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

これによりエラーが発生します。A と B が相互参照しているため、循環参照になります。

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

エイリアスの使用

エイリアスはクエリで示されるテーブル、列、または式に対して与えられる一時的な名前です。SELECT リストまたは FROM 句のいずれかで、明示的エイリアスを使用できます。あるいは BigQuery は一部の式に対する暗黙的なエイリアスを推測します。明示的なエイリアスも暗黙のエイリアスもない式は匿名であり、クエリではこれらを名前で参照できません。

明示的なエイリアス

FROM 句または SELECT リストのいずれかで、明示的なエイリアスを導入できます。

FROM 句では、[AS] alias を使用してテーブル、配列、サブクエリ、UNNEST 句を含む項目の明示的なエイリアスを使用できます。AS キーワードは任意です。

例:

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

[AS] alias を使用して SELECT リストの式に対して明示的なエイリアスを使用できます。AS キーワードは任意です。

例:

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

暗黙のエイリアス

SELECT リストに明示的エイリアスが指定されていない式がある場合、BigQuery は次のルールに従って暗黙的エイリアスを割り当てます。このルールによって、SELECT リストで複数の列に同じエイリアスが割り当てられる場合があります。

識別子の場合、エイリアスは識別子です。たとえば、SELECT abc は AS abc を含意します。
パス式の場合、エイリアスはパスの最後の識別子です。たとえば、SELECT abc.def.ghi は AS ghi を含意します。
「ドット」メンバーフィールドアクセス演算子を使用するフィールドアクセスの場合、エイリアスはフィールド名になります。たとえば、SELECT (struct_function()).fname は AS fname を含意します。

他のすべての事例では暗黙のエイリアスはないので、列は匿名で、名前では参照できません。その列からのデータは引き続き返され、表示されるクエリの結果にはその列に対して生成されたラベルがある場合がありますが、ラベルはエイリアスのようには使用できません。

FROM 句では、from_item はエイリアスが必要なわけではありません。以下のルールが適用されます。

明示的エイリアスが指定されていない式では、BigQuery によって暗黙的エイリアスが以下のように割り当てられます。

識別子の場合、エイリアスは識別子です。たとえば、FROM abc は AS abc を含意します。
パス式の場合、エイリアスはパスの最後の識別子です。たとえば、FROM abc.def.ghi は AS ghi を含意します。
WITH OFFSET を使用して生成された列には、暗黙のエイリアス offset があります。

テーブルのサブクエリに、暗黙のエイリアスはありません。
FROM UNNEST(x) に暗黙のエイリアスはありません。

エイリアスの可視性

クエリに明示的エイリアスを使用した場合、そのエイリアスを参照できるクエリの他の場所について制限があります。エイリアスの可視性に対するこれらの制限は、BigQuery の名前範囲のルールの結果です。

FROM 句の可視性

BigQuery は FROM 句のエイリアスを左から右に処理し、すべてのエイリアスは FROM 句内の後続のパス式のみに表示されます。

例:

Singers テーブルに ARRAY データ型の Concerts 列があることを想定してください。

SELECT FirstName
FROM Singers AS s, s.Concerts;

無効:

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

FROM 句のエイリアスは、同じ FROM 句内のサブクエリでは認識されません。FROM 句のサブクエリには、同じ FROM 句内のその他のテーブルへの相関参照を含めることはできません。

無効:

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

テーブル名による修飾の有無に関係なく、FROM のテーブルの列名を、クエリの任意の場所でエイリアスとして使用できます。

例:

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

FROM 句に明示的なエイリアスが含まれている場合、そのクエリの他の部分では暗黙のエイリアスではなく、明示的なエイリアスを使用する必要があります（暗黙のエイリアスを参照）。テーブルエイリアスは、クエリ処理中に同じテーブルが複数回スキャンされる自己結合の場合などに、簡潔さの実現や曖昧さの排除に役立ちます。

例:

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

無効 - ORDER BY では、テーブルエイリアスは使用されません。

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

SELECT リストの可視性

SELECT リスト内のエイリアスは、次の句でのみ認識されます。

GROUP BY 句
ORDER BY 句
HAVING 句

例:

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

GROUP BY、ORDER BY、HAVING 句の可視性

GROUP BY、ORDER BY、HAVING の 3 つの句は、以下の値のみを参照できます。

FROM 句のテーブルとその列。
SELECT リストのエイリアス。

GROUP BY と ORDER BY は、3 番目のグループも参照できます。

整数リテラル。これは、SELECT リストの項目を参照します。整数 1 は、SELECT リストの最初の項目、2 は 2 番目の項目を参照、などとなります。

例:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

上記のクエリは次と同等です。

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

重複するエイリアス

SELECT リストまたはサブクエリに複数の明示的なエイリアスまたは暗黙のエイリアスを同じ名前で追加できますが、エイリアス名がクエリの任意の場所で参照されている場合は参照が曖昧になるため、このような処理はできません。

最上位の SELECT リストに重複する列名が含まれていて、宛先テーブルが指定されていない場合、最初の列を除き、重複する列の名前が自動的に変更され、一意になります。変更後の列名がクエリ結果に表示されます。

例:

SELECT 1 AS a, 2 AS a;

+---+-----+
| a | a_1 |
+---+-----+
| 1 | 2   |
+---+-----+

テーブルまたはビュー定義で列名の重複はサポートされていません。クエリで重複する列名を含むステートメントを使用すると失敗します。

CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);

CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);

曖昧なエイリアス

BigQuery では曖昧な名前の参照はエラーになります。つまり、クエリまたはテーブルスキーマ（複数の宛先テーブルを含む）で一意の複数のオブジェクトに解決できる場合、エラーが発生します。

例:

このクエリには、Singers と Songs の両方に SingerID という名前の列があるため、テーブル間で競合する列名が含まれます。

SELECT SingerID
FROM Singers, Songs;

このクエリでは、SELECT リストでエイリアスが重複しているため、GROUP BY 句のエイリアスが曖昧になります。

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

このクエリでは、名前が重複しているため、SELECT のリストと FROM 句のエイリアスが曖昧になります。table に列 x、y、z があるとします。z は STRUCT 型で、フィールドは v、w、x です。

例:

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

エイリアス T が曖昧なためエラーを生成します。これは、GROUP BY 句の T.x が table.x と table.z.x のどちらも参照する可能性があるためです。

基礎となる同じオブジェクトでその名前を解決できる場合、列名と SELECT リストのエイリアスの両方で特定の名前が使用されていても、GROUP BY、ORDER BY、HAVING でその名前は曖昧とはなりません。

例:

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

エイリアス BirthYear は、同じ基礎となる列 Singers.BirthYear に解決されるため曖昧ではありません。

範囲変数

BigQuery での範囲変数とは、FROM 句のテーブル式のエイリアスのことです。範囲変数は、table alias と呼ばれることもあります。範囲変数を使用すると、テーブル式からスキャンされた行を参照できます。テーブル式は、テーブルを返す FROM 句の項目を表します。この式で表せる一般的な項目には、値テーブル、サブクエリ、結合、括弧で囲まれた結合があります。

一般に、範囲変数は、テーブル式の行への参照を提供します。範囲変数は、列参照の限定や、関連テーブルを明確に識別するために使用されます。たとえば range_variable.column_1 のようにします。

特定の列サフィックスを含まない範囲変数を単独で参照する場合、テーブル式の結果は関連テーブルの行の型になります。値テーブルには明示的な行の型があるため、値テーブルに関連する範囲変数の場合、結果の型は値テーブルの行の型になります。他のテーブルに明示的な行の型はありません。それらのテーブルの範囲変数は、テーブルのすべての列を含む動的に定義された STRUCT 型になります。

例

以下の例では、Grid 句という一時テーブルのエミュレートに WITH 句を使用しています。このテーブルには x 列と y 列があります。テーブルがスキャンされる際、範囲変数の Coordinate が現在行を指します。Coordinate を使用すると、行全体またはその行の複数列にアクセスできます。

次の例では、列 x を範囲変数 Coordinate から選択することで、実際にはテーブル Grid の列 x を選択しています。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

次の例では、範囲変数 Coordinate からすべての列を選択することで、実際にはテーブル Grid のすべての列を選択しています。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

次の例では、範囲変数 Coordinate を選択します。これは、テーブル Grid 内の行への参照になります。Grid は、値テーブルではないため、Coordinate の結果の型は、Grid のすべての列を含む STRUCT になります。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

値テーブルの操作

BigQuery の値テーブルは、行の型が単一値であるテーブルです。通常のテーブルでは各行が複数の列で構成され、各列には名前と型があります。値テーブルでは行の型が単一値のみであり、列名はありません。

クエリで SELECT AS STRUCT または SELECT AS VALUE を使用する場合、値テーブルが生成されます。

注: BigQuery のクエリでは、型が STRUCT の値テーブルだけを返すことができます。

1 列だけが返されるクエリを使用する場合、代わりに値テーブルが返されるクエリを使用できます。たとえば、スカラーサブクエリと配列サブクエリ（サブクエリを参照）では、通常は 1 列が返されるクエリを使用する必要がありますが、BigQuery では値テーブルが返されるクエリも使用できます。

付録 A: 例とサンプルデータ

Roster テーブル、TeamMascot テーブル、PlayerStats テーブルに対してクエリを実行するステートメントの例を以下に示します。

GROUP BY 句

例:

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;

LastName	SUM
Adams	7
Buchanan	13
Coolidge	1

UNION

UNION 演算子は各 SELECT ステートメントの結果セットの列をペアリングし、縦方向に連結させることにより、2 つ以上の SELECT ステートメントの結果セットを結合します。

例:

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

結果:

X	Y
Jaguars	50
Knights	51
Lakers	52
Mustangs	53
Adams	3
Buchanan	0
Coolidge	1
Adams	4
Buchanan	13

INTERSECT

このクエリは、Roster と PlayerStats の両方に存在する姓を返します。

SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;

結果:

LastName
Adams
Coolidge
Buchanan

EXCEPT

次のクエリは、PlayerStats に存在していない Roster の姓を返します。

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

結果:

LastName
Eisenhower
Davis

SELECT ステートメントの順序を逆にすると、Roster に存在していない PlayerStats の姓が返されます。

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

結果:

(empty)

クエリ構文

SQL 構文

サンプル テーブル

Roster テーブル

PlayerStats テーブル

TeamMascot テーブル

SELECT ステートメント

SELECT *

SELECT expression

SELECT expression.*

SELECT * EXCEPT

SELECT * REPLACE

SELECT DISTINCT

SELECT ALL

SELECT AS STRUCT

SELECT AS VALUE

FROM 句

pivot_operator

unpivot_operator

tablesample_operator

table_name

FOR SYSTEM_TIME AS OF

join_operation

query_expr

field_path

unnest_operator

cte_name

UNNEST 演算子

UNNEST と STRUCT

明示的な UNNEST と暗黙的な UNNEST

UNNEST と NULL

UNNEST と WITH OFFSET

PIVOT 演算子

UNPIVOT 演算子

TABLESAMPLE 演算子

JOIN 演算

[INNER] JOIN

CROSS JOIN

カンマクロス結合（,）

FULL [OUTER] JOIN

LEFT [OUTER] JOIN

RIGHT [OUTER] JOIN

ON 句

USING 句

ON と USING の等価性

シーケンス内の結合演算

相関結合演算

WHERE 句

GROUP BY 句

HAVING 句

必須の集計

SELECT リストの集計関数

HAVING 句の集計関数。

SELECT リストと HAVING 句の両方の集計

ORDER BY 句

QUALIFY 句

WINDOW 句

集合演算子

UNION

INTERSECT

EXCEPT

LIMIT 句と OFFSET 句

WITH 句

RECURSIVE キーワード

非再帰 CTE

Examples

再帰 CTE

許可される再帰 CTE の例

許可されていない再帰 CTE の例

CTE のルールと制約

一般規則

基本用語のルール

再帰用語のルール

CTE の可視性

RECURSIVE キーワードを使用した WITH 句の CTE の可視性

RECURSIVE キーワードのない WITH 句での CTE の可視性

エイリアスの使用

明示的なエイリアス

暗黙のエイリアス

エイリアスの可視性

サンプルテーブル

SELECT `expression`

SELECT `expression.*`

`SELECT` リストの集計関数

`SELECT` リストと `HAVING` 句の両方の集計

`RECURSIVE` キーワードを使用した `WITH` 句の CTE の可視性

`RECURSIVE` キーワードのない `WITH` 句での CTE の可視性