クエリ構文

クエリ ステートメントは、1 つ以上のテーブルまたは式をスキャンし、計算結果の行を返します。

SQL 構文

query_statement:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
query_expr query_expr: { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] [ WINDOW window_name AS ( window_definition ) [, ...] ] set_op: UNION { ALL | DISTINCT } from_item: { table_name [ [ AS ] alias ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

表記:

  • 角括弧「[ ]」はオプションの句です。
  • 丸括弧「( )」はリテラルの括弧を表します。
  • 縦線「|」は論理 OR を表します。
  • 波括弧「{ }」ではオプションのセットを囲みます。
  • 角かっこ内の省略記号に先立つカンマは、前の項目をカンマで区切ったリストに繰り返すことができることを示しています。

SELECT リスト

構文:

SELECT  [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]

SELECT リストは、クエリが返す列を定義します。SELECT リストの式は、対応する FROM 句の from_items の任意の項目の列を参照できます。

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(エイリアス g)の各列に 1 つの出力列が生成されます。

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 修飾子

次のとおり、SELECT クエリから返された結果を修正できます。

SELECT DISTINCT

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

  • STRUCT
  • ARRAY

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 * EXCEPT は、名前のない列を除外しません。

SELECT * REPLACE

SELECT * REPLACE ステートメントは 1 つ以上の expression AS identifier 句を指定します。個々の 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 * REPLACE は名前のない列の置き換えは行いません。

SELECT ALL

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

エイリアス

SELECT リストのエイリアスの構文と可視性に関する情報については、エイリアスをご覧ください。

分析関数

分析関数に関係する句については別のページで説明します。

  • OVER 句と PARTITION BY については、分析関数をご覧ください。

  • WINDOW 句とウィンドウ関数については、WINDOW 句をご覧ください。

FROM 句

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

構文

from_item: {
    table_name [ [ AS ] alias ] |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ AS ] alias ]
}

table_name

既存のテーブルの名前(修飾可)。

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

join

以下の JOIN 型をご覧ください。

select

( select ) [ [ 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 はパスの末尾の前にアレイを含むため、無効です。

注: パスに 1 つの名前のみがある場合、テーブルとして解釈されます。それを回避するためには、UNNEST を使用してパスをラップするか、完全修飾パスを使用してください。

UNNEST

FROM 句で、UNNEST は ARRAY を通じて反復され、各 ARRAY 要素に対して 1 行を生成します。結果の列にはオプションの alias があり、クエリの他の場所の列を参照するために使用できます。また、列を返す式を使用することが有効な場合は、UNNESTFROM 句の外でも使用できます。

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 を次のとおり扱います。

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

オプションの WITH OFFSET 句は、UNNEST 演算で生成される行ごとに「オフセット」値(カウントはゼロから始まります)を含む別の列を返します。この列にはオプションの alias があり、デフォルトのエイリアスはオフセットです。

例:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

with_query_name

WITH 句(WITH 句を参照)内のクエリ名は、FROM 句の任意の場所から参照できる一時テーブルの名前のように機能します。以下の例では、subQ1subQ2with_query_names です。

例:

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

WITH 句は、次の例のようにテーブル名を修飾していない限り、クエリの期間中、同じ名前の永続テーブルを隠ぺいします(dataset.Rosterproject.dataset.Roster など)。

サブクエリ

サブクエリは別のステートメントの中に表示されるクエリで、括弧の中に書かれます。これらは、「サブ SELECT」または「ネストされた SELECT」とも呼ばれます。完全な SELECT 構文は、サブクエリで有効です。

サブクエリには次の 2 種類があります。

  • 式サブクエリ: 式が有効なときにいつでもクエリで使用することができます。式サブクエリは、単一の値を返します。
  • テーブル サブクエリ: FROM 句でのみ使用できます。外部クエリは、サブクエリの結果をテーブルとして扱います。

両方のデータ型のサブクエリを囲む括弧がなければならないので、ご注意ください。

例:

SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
  FROM Stats
  WHERE SchoolID = 77 )

オプションで、テーブル サブクエリはエイリアスを持つことができます。

例:

SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;

エイリアス

FROM 句のエイリアスの構文と可視性については、FROM をご覧ください。

JOIN のタイプ

構文

join:
    from_item [ join_type ] JOIN from_item
    [ ON bool_expression | USING ( join_column [, ...] ) ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

JOIN 句は 2 つの from_item を結合して、SELECT 句が 1 つのソースとしてそれらをクエリできます。join_typeON または USING 句(「結合条件」)は、2 つの from_item から行を廃棄して、1 つのソースを形成します。

すべての JOIN 句では、join_type が必要です。

JOIN 句は、次の条件の 1 つが true でない限り、結合条件を必要とします。

  • join_typeCROSS です。
  • from_item の 1 つ または両方はテーブルではありません。例: array_path または field_path

[INNER] JOIN

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

CROSS JOIN

CROSS JOIN は 2 つの from_item のデカルト積を返します。言い換えると、両方の from_item からのすべての行を維持し、最初の from_item の各行を 2 番目の from_item の各行と組み合わせます。

コンマ cross join

CROSS JOIN は明示的に書く(すぐ上を参照)か、from_item を区切るためにコンマを使用して暗黙に書くことができます。

暗黙の「コンマ cross join」の例は、次のとおりです。

SELECT * FROM Roster, TeamMascot;

同等の明示的な cross join は次のとおりです。

SELECT * FROM Roster CROSS JOIN TeamMascot;

括弧の中に、コンマ cross join を書くことはできません。

無効 - 括弧内のコンマ cross join。

SELECT * FROM t CROSS JOIN (Roster, TeamMascot);  // INVALID.

コンマ cross join が JOIN の順序でどのように動作するかについては、JOIN の順序を参照してください。

FULL [OUTER] JOIN

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

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

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

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 は、廃棄されます。

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN(または単に RIGHT JOIN)の結果は、LEFT OUTER JOIN のものと同様で、対称的です。

ON 句

ON 句には bool_expression が含まれます。結合された行(2 つの行を結合した結果)は、bool_expression が TRUE を返した場合、結合条件を満たします。

例:

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

USING 句

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

ほとんどの場合、USING キーワードを持つステートメントは ON キーワードを使用することと同じ意味を持ちます。たとえば、次のステートメントがあるとします。

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);

これは、次のステートメントと同等になります。

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

USING を使ったクエリの結果は、SELECT * を使用すると ON を使用するクエリとは異なります。これを説明するために、次のクエリを考えてみます。

SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);

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

対照的に、次のクエリについて考えてみてください。

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

このステートメントは、RosterPlayerStats からの行を返し、その場合、Roster.LastNamePlayerStats.LastName と同じです。結果には、Roster から 1 つと PlayerStats から 1 つの 2 つの LastName 列が含まれます。

JOIN の順序

FROM 句は、順番に複数の JOIN 句を含むことができます。

例:

SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;

この場合、abc は任意の from_item です。JOIN は左から右にバインドされますが、異なる順番でそれらをグループ化するために、括弧を挿入できます。

次のクエリを考えてみます。A(括弧なし)と B(括弧あり)は互いに同等ですが、C は異なります。太字FULL JOIN が最初にバインドされます。

A.

SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID)
FULL JOIN PlayerStats USING (LastName);

B.

SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID))
FULL JOIN PlayerStats USING (LastName));

C.

SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING
(LastName)) USING (SchoolID)) ;

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

例:

SELECT * FROM a JOIN b ON TRUE, b JOIN c ON TRUE;

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

SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);

コンマ JOIN の後に RIGHT JOIN または FULL JOIN があってはなりません。

無効 - コンマ CROSS JOIN の後の RIGHT JOIN

SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE;  // INVALID.

WHERE 句

構文

WHERE bool_expression

WHERE 句は各行を bool_expression で評価することで、行をフィルタで除外して、TRUE を返さないすべての行(FALSE または NULL を返す行)を破棄します。

例:

SELECT * FROM Roster
WHERE SchoolID = 52;

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

例:

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

WHERE 句の SELECT リストから列のエイリアスを参照できません。

INNER JOIN の式には、WHERE 句に同等の式があります。たとえば、INNER JOINON を使用するクエリは、CROSS JOINWHERE を使用して同等の式を持ちます。

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

SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

これは次と同等です。

SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY 句

構文

GROUP BY expression [, ...]

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

例:

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

GROUP BY 句は SELECT リストで式の名前を参照することができます。また GROUP BY 句では、整数値を使用して SELECT リスト内の式をその順序で参照することができます。1SELECT リストの最初の式、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;

HAVING 句

構文

HAVING bool_expression

HAVING 句は、WHERE 句と同様です。これは bool_expression で評価された場合、TRUE を返さない行をフィルタで除去します。

WHERE 句の場合、bool_expression はブール値を返す任意の式で、複数の従属条件を含むことができます。

HAVING 句は次の点で、WHERE 句と異なります。

  • HAVING 句では、GROUP BY または集計がクエリに存在しなければなりません。
  • HAVING 句は GROUP BY と集計の後で、ORDER BY の前に置かれます。つまり、HAVING 句は結果セットの集計行ごとに一度評価されることを意味します。このことは、GROUP BY と集計の前に評価される WHERE 句と異なります。

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 }] [, ...]

ORDER BY 句は結果セットのソート基準として列または式を指定します。ORDER BY 句が存在しない場合、クエリの結果は定義されません。デフォルトのソート方向は ASC であり、結果は expression 値の昇順でソートされます。DESC は結果を降順でソートされます。FROM 句の列のエイリアスまたは SELECT リストが許可されます。クエリに SELECT 句が含まれる場合、これらのエイリアスは、対応する FROM 句の名前をオーバーライドします。

複数の列により並べ替えることができます。以下の例では、結果セットはまず SchoolID で並べられてから、次に LastName で並べられます。

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

以下のルールは、値を順番に並べるときに適用されます。

  • NULL: ORDER BY 句のコンテキストの場合、NULL が最小の候補値です。つまり、NULL は ASC ソートでは最初になり、DESC ソートでは最後になります。
  • 浮動小数点データ型の場合は、順序とグループ化について、浮動小数点のセマンティクスをご覧ください。

UNION と組み合わせて使用すると、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
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;

UNION

構文

UNION { ALL | DISTINCT }

UNION 演算子は 2 つ以上の入力クエリを各クエリの結果セットの列を組み合わせて、縦方向に連結させることにより結合します。ALL または DISTINCT を指定します。ALL を指定する場合、すべての行が保留されます。DISTINCT が指定される場合、重複する行は廃棄されます。

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

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

次のルールが適用されます。

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

例:

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

無効:

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

LIMIT 句と OFFSET 句

構文

LIMIT count [ OFFSET skip_rows ]

LIMIT はデータ型 INT64 の負ではない count を使用し、count 行までが返されます。LIMIT 0 は 0 行を返します。UNION の演算がある場合、LIMITUNION 演算が評価された後に適用されます。

OFFSET はデータ型 INT64 の負ではない skip_rows を指定し、テーブルのオフセットの行のみが対象となります。

これらの句はリテラルまたはパラメータ値のみを受け付けます。

LIMITOFFSET により返される行は、これらの演算子が ORDER BY の後に使用されない限り、未指定になります。

WITH 句

WITH 句には 1 つ以上の名前付きサブクエリが含まれています。このサブクエリの出力は、後続の SELECT ステートメントが任意の句またはサブクエリで参照できる一時表として機能します。UNION などの集合演算子の両側に、任意の SELECT ステートメントが含まれます。

例:

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 句に分割するという有用な役割もあります。ネストテーブルのサブクエリを記述する方法は望ましい代替方法ではありません。WITH 句に複数のサブクエリが含まれている場合、サブクエリ名を繰り返すことはできません。

BigQuery は、テーブルのサブクエリ、式のサブクエリなどのサブクエリで WITH 句をサポートします。

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

WITH 句のスコープルールは以下のとおりです。

  • エイリアスは、WITH 句に導入されたエイリアスが、同じ WITH 句の後のサブクエリと、WITH 句の下のクエリにのみ表示されるように範囲を指定します。
  • 同じ WITH 句に導入されたエイリアスは一意にする必要がありますが、同じクエリの複数の WITH 句で同じエイリアスを使用できます。ローカル エイリアスは、このエイリアスが表示される外側のエイリアスをすべてオーバーライドします。
  • WITH 句でエイリアスを設定したサブクエリに相関関係はありません。クエリ外部からの列は表示されません。外部からのもので表示される名前は、同じ WITH 句で先に導入された他の WITH エイリアスのみです。

以下に、WITH サブクエリでエイリアスを使用するステートメントの例を示します。

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.

BigQuery は WITH RECURSIVE をサポートしません。

エイリアス

エイリアスはクエリで示されるテーブル、列、または式に対して与えられる一時的な名前です。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;

明示的なエイリアスの可視性

クエリに明示的エイリアスを導入した場合、そのエイリアスを参照できるクエリの他の場所について制限があります。エイリアスの可視性に対するこれらの制限は、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 BYORDER BYHAVING の 3 つの句は、以下の値のみが参照できます。

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

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

曖昧なエイリアス

BigQuery は、名前が曖昧である場合(複数の固有のオブジェクトに解決できる場合)にエラーとなります。

例:

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

SELECT SingerID
FROM Singers, Songs;

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

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

次では、FROM 句の列名と SELECT 内の GROUP BY リストのエイリアスが曖昧です。

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;

上記のクエリは曖昧なためエラーが発生します。これは、GROUP BY 句の LastName が、Singers の元の列 LastName を参照する可能性もあれば、エイリアス AS LastName(値が UPPER(LastName))を参照する可能性もあるためです。

曖昧さに対する同じルールがパス式に適用されます。次のクエリを考えてみます。table に列 xy があり、列 z はデータ型 STRUCT で、フィールド vwx が含まれます。

例:

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

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

ある名前が列名と SELECT リスト エイリアスの両方として指定されているとき、それが GROUP BYORDER BYHAVING において曖昧にならないのは、同じ基礎オブジェクトに解決される場合です。

例:

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

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

暗黙のエイリアス

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

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

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

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

  • 明示的エイリアスが指定されていない式では、BigQuery は以下の場合に暗黙的エイリアスを割り当てます。
    • 識別子の場合、エイリアスは識別子です。たとえば、FROM abcAS abc を暗示します。
    • パス式の場合、エイリアスはパスの最後の標識です。たとえば、FROM abc.def.ghiAS ghi を暗示します。
    • WITH OFFSET を使用する生成された列には、暗黙の offset があります。
  • テーブルのサブクエリには、暗黙のエイリアスはありません。
  • FROM UNNEST(x) には暗黙のエイリアスはありません。

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

サンプル テーブル

次の 3 つのテーブルは、運動選手、その学校、シーズン中の獲得点数に関するサンプルデータを含みます。これらのテーブルは、異なるクエリ句の動作を説明するために使用されます。

テーブル Roster:

LastName SchoolID
Adams 50
Buchanan 52
Coolidge 52
Davis 51
Eisenhower 77

Roster テーブルには、プレイヤーの名前(LastName)と学校に割り当てられた固有の ID(SchoolID)が含まれます。

テーブル PlayerStats:

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

PlayerStats テーブルには、指定した試合(OpponentID)でプレイした敵側のチームに割り当てられたプレイヤーの名前(LastName)と固有の ID、その試合で選手がスコアしたポイント数(PointsScored)のリストが含まれます。

テーブル TeamMascot:

SchoolId Mascot
50 Jaguars
51 Knights
52 Lakers
53 Mustangs

TeamMascot テーブルは固有の学校 ID(SchoolID)とその学校のマスコット(Mascot)のリストを含みます。

JOIN のデータ型

1)[INNER] JOIN

例:

SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

結果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights

2)CROSS JOIN

例:

SELECT * FROM Roster CROSS JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

結果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Adams 50 51 Knights
Adams 50 52 Lakers
Adams 50 53 Mustangs
Buchanan 52 50 Jaguars
Buchanan 52 51 Knights
Buchanan 52 52 Lakers
Buchanan 52 53 Mustangs
Coolidge 52 50 Jaguars
Coolidge 52 51 Knights
Coolidge 52 52 Lakers
Coolidge 52 53 Mustangs
Davis 51 50 Jaguars
Davis 51 51 Knights
Davis 51 52 Lakers
Davis 51 53 Mustangs
Eisenhower 77 50 Jaguars
Eisenhower 77 51 Knights
Eisenhower 77 52 Lakers
Eisenhower 77 53 Mustangs

3)FULL [OUTER] JOIN

例:

SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL
NULL NULL 53 Mustangs

4)LEFT [OUTER] JOIN

例:

SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

結果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL

5)RIGHT [OUTER] JOIN

例:

SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

結果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Davis 51 51 Knights
Coolidge 52 52 Lakers
Buchanan 52 52 Lakers
NULL NULL 53 Mustangs

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;

結果:

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

フィードバックを送信...