クエリ ステートメントは、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
に指定できます。定数にするか、これらのルールに従う必要があります。
ルール
from_item
は、任意のテーブルまたはサブクエリの結果で構成されます。from_item
によって値テーブルが生成されない場合があります。SELECT AS STRUCT
を使用するサブクエリをfrom_item
として指定することはできません。
- 集計関数にする必要があります。例:
SUM
- 相関列だけでなく
PIVOT
に渡されるテーブル内の列も参照できますが、PIVOT
句自体で定義された列にはアクセスできません。 - エイリアスが指定されると、エイリアスを介して、
PIVOT
に渡されたテーブルにアクセスできます。 - 1 つの引数を取る集計関数のみを使用できます。
COUNT
を除いて、NULL
入力を無視する集計関数を使用できます。COUNT
を使用する場合、引数として*
を使用できます。
- 入力テーブルの列と(
PIVOT
句自体で定義された列ではない)相関列にアクセスできます。 - 入力テーブルの各行に対して評価されます。集約関数やウィンドウ関数の呼び出しは禁止されています。
- 不確実な処理は許容されます。
- タイプはグループ化可能にする必要があります。
- 入力テーブルのエイリアスが指定されている場合は、エイリアスを介して入力テーブルにアクセスできます。
pivot_column
は、定数にする必要があります。- 変数など、名前付き定数はサポートされていません。
- クエリ パラメータはサポートされていません。
- 名前付き定数またはクエリ パラメータに名前を使用する場合は、エイリアスを使用して明示的に指定します。
- 異なる
pivot_column
が同じデフォルト列名を持つ特殊なケースが存在します。たとえば、入力列にNULL
値と文字列リテラル"NULL"
の両方が含まれる場合があります。この場合、複数のピボット列が同じ名前で作成されます。このような状況を回避するには、ピボット列名にエイリアスを使用します。 pivot_column
でエイリアスが指定されていない場合、列名は次のようになります。
元の型 | 変換後 | 例 |
---|---|---|
NULL | NULL |
入力: NULL 出力: 「NULL」 |
INT64 NUMERIC BIGNUMERIC |
次のルールを含む文字列形式の数値。
|
入力: 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 名を計算し、結果をアンダースコアで結合して形成される文字列。以下のルールが適用されます。
|
入力: 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
になります。行値のエイリアスを作成する場合は、これらのルールに従います。
ルール
from_item
は、任意のテーブルまたはサブクエリの結果で構成されます。from_item
によって値テーブルが生成されない場合があります。from_item
内の重複列はUNPIVOT
句で参照できません。
- 式は使用できません。
- 修飾名は使用できません。たとえば、
mytable.mycolumn
は許可されません。 UNPIVOT
の結果に重複する列名が含まれている場合:SELECT *
は許可されます。SELECT values_column
を指定すると、曖昧さが発生します。
name_column
またはunpivot_column
に使用する名前にすることはできません。from_item
の列と同じ名前を使用できます。
values_column
またはunpivot_column
に使用する名前にすることはできません。from_item
の列と同じ名前を使用できます。
from_item
の列名を指定する必要があります。- 重複する
from_item
列名は参照できません。 - 列セット内のすべての列は、同等のデータ型を持つ必要があります。
- データ型を共通のスーパータイプに強制的に変換することはできません。
- データ型が完全に一致する場合(フィールド名が異なる構造体など)、最初の入力のデータ型が出力のデータ型になります。
- 同じ列セットに同じ名前を付けることはできません。たとえば、
(emp1, emp1)
はエラーになります。 - 異なる列セットで同じ名前を使用できます。たとえば、
(emp1, emp2), (emp1, emp3)
は有効です。
- これは、
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)