レガシー SQL 関数と演算子

このドキュメントでは、レガシー SQL 関数と演算子について詳しく説明します。BigQuery の優先クエリ構文は GoogleSQL です。GoogleSQL の詳細については、Google SQL の関数と演算子をご覧ください。

サポートされる関数と演算子

ほとんどの SELECT ステートメント句は関数をサポートしています。関数の中で参照されるフィールドは、SELECT 句でリストされている必要はありません。したがって、次のクエリは clicks フィールドが直接表示されていなくても有効です。

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;

集計関数
`AVG()`	ある行のグループの値の平均値を返します ...
`BIT_AND()`	ビット演算 AND を実行した結果を返します ...
`BIT_OR()`	ビット演算 OR を実行した結果を返します ...
`BIT_XOR()`	ビット演算 XOR を実行した結果を返します ...
`CORR()`	一連の数値ペアのピアソン相関係数を返します。
`COUNT()`	値の合計数を返します ...
`COUNT([DISTINCT])`	非 NULL 値の合計数を返します ...
`COVAR_POP()`	値の母共分散を計算します ...
`COVAR_SAMP()`	値の標本共分散を計算します ...
`EXACT_COUNT_DISTINCT()`	非 NULL 値の正確な値を返します。これは指定したフィールドの一意の値です。
`FIRST()`	関数のスコープ内で最初の連続値を返します。
`GROUP_CONCAT()`	複数の文字列を連結して 1 つの文字列にします ...
`GROUP_CONCAT_UNQUOTED()`	複数の文字列を連結して 1 つの文字列にします ... 二重引用符を追加しません ...
`LAST()`	最後の連続値を返します ...
`MAX()`	最大値を返します ...
`MIN()`	最小値を返します ...
`NEST()`	現在の集計スコープ内のすべての値を繰り返しフィールドにまとめます。
`NTH()`	n 番目の連続値を返します ...
`QUANTILES()`	おおよその最小値、最大値、変位値を計算します ...
`STDDEV()`	標準偏差を返します ...
`STDDEV_POP()`	母標準偏差を返します ...
`STDDEV_SAMP()`	標本標準偏差を返します ...
`SUM()`	値の合計値を返します ...
`TOP() ... COUNT(*)`	頻度別の上位の max_records レコードを返します。
`UNIQUE()`	一意の非 NULL 値のセットを返します ...
`VARIANCE()`	値の分散を計算します ...
`VAR_POP()`	値の母分散を計算します ...
`VAR_SAMP()`	値の標本分散を計算します ...

算術演算子
`+`	加算
`-`	減算
`*`	乗算
`/`	除算
`%`	剰余

ビット演算関数
`&`	ビット演算 AND
`\|`	ビット演算 OR
`^`	ビット演算 XOR
`<<`	ビット演算左シフト
`>>`	ビット演算右シフト
`~`	ビット演算 NOT
`BIT_COUNT()`	ビット数を返します ...

キャスト関数
`BOOLEAN()`	ブール値にキャストします。
`BYTES()`	bytes 値にキャストします。
`CAST(expr AS type)`	`expr` を `type` 型の変数に変換します。
`FLOAT()`	double 値にキャストします。
`HEX_STRING()`	16 進数の文字列にキャストします。
`INTEGER()`	整数にキャストします。
`STRING()`	文字列にキャストします。

比較関数
`expr1 = expr2`	式が等しい場合、`true` を返します。
`expr1 != expr2` `expr1 <> expr2`	式が等しくない場合、`true` を返します。
`expr1 > expr2`	`expr1` が `expr2` よりも大きい場合、`true` を返します。
`expr1 < expr2`	`expr1` が `expr2` よりも小さい場合、`true` を返します。
`expr1 >= expr2`	`expr1` が `expr2` 以上の場合、`true` を返します。
`expr1 <= expr2`	`expr1` が `expr2` 以下の場合、`true` を返します。
`expr1 BETWEEN expr2 AND expr3`	`expr1` の値が `expr2` と `expr3` の間（両端の値を含む）の場合、`true` を返します。
`expr IS NULL`	`expr` が NULL の場合、`true` を返します。
`expr IN()`	`expr` が `expr1`、`expr2`、またはかっこ内の任意の値に一致する場合、`true` を返します。
`COALESCE()`	NULL ではない最初の引数を返します。
`GREATEST()`	最大の `numeric_expr` パラメータを返します。
`IFNULL()`	引数が NULL でない場合、引数を返します。
`IS_INF()`	正または負の無限大である場合、`true` を返します。
`IS_NAN()`	引数が `NaN` である場合、`true` を返します。
`IS_EXPLICITLY_DEFINED()`	非推奨: 代わりに、`expr IS NOT NULL` を使用してください。
`LEAST()`	最小の引数 `numeric_expr` パラメータを返します。
`NVL()`	`expr` が NULL ではない場合は `expr` を返し、それ以外の場合は `null_default` を返します。

日付と時刻の関数
`CURRENT_DATE()`	現在の日付を `%Y-%m-%d` の形式で返します。
`CURRENT_TIME()`	サーバーの現在の時刻を `%H:%M:%S` の形式で返します。
`CURRENT_TIMESTAMP()`	サーバーの現在の時刻を `%Y-%m-%d %H:%M:%S` の形式で返します。
`DATE()`	日付を `%Y-%m-%d` の形式で返します。
`DATE_ADD()`	指定した長さの時間を TIMESTAMP データ型の値に加算します。
`DATEDIFF()`	2 つの TIMESTAMP データ型値の間の日数を返します。
`DAY()`	月の何日目なのかを 1～31 の整数で返します。
`DAYOFWEEK()`	週の何日目なのかを 1（日曜日）から 7（土曜日）までの整数で返します。
`DAYOFYEAR()`	年の何日目なのかを 1～366 の整数で返します。
`FORMAT_UTC_USEC()`	UNIX タイムスタンプを `YYYY-MM-DD HH:MM:SS.uuuuuu` の形式で返します。
`HOUR()`	TIMESTAMP の時間を 0～23 の整数で返します。
`MINUTE()`	TIMESTAMP の分を 0～59 までの整数で返します。
`MONTH()`	TIMESTAMP の月を 1～12 までの整数で返します。
`MSEC_TO_TIMESTAMP()`	ミリ秒単位の UNIX タイムスタンプを TIMESTAMP に変換します。
`NOW()`	現在の UNIX タイムスタンプをマイクロ秒単位で返します。
`PARSE_UTC_USEC()`	日付文字列をマイクロ秒単位の UNIX タイムスタンプに変換します。
`QUARTER()`	TIMESTAMP が表している四半期がいつなのかを 1～4 の整数で返します。
`SEC_TO_TIMESTAMP()`	秒単位の UNIX タイムスタンプを TIMESTAMP に変換します。
`SECOND()`	TIMESTAMP が表している時刻の秒を 0～59 の整数で返します。
`STRFTIME_UTC_USEC()`	日付文字列を date_format_str の形式で返します。
`TIME()`	TIMESTAMP を `%H:%M:%S` の形式で返します。
`TIMESTAMP()`	日付文字列を TIMESTAMP に変換します。
`TIMESTAMP_TO_MSEC()`	TIMESTAMP をミリ秒単位の UNIX タイムスタンプに変換します。
`TIMESTAMP_TO_SEC()`	TIMESTAMP を秒単位の UNIX タイムスタンプに変換します。
`TIMESTAMP_TO_USEC()`	TIMESTAMP をマイクロ秒単位の UNIX タイムスタンプに変換します。
`USEC_TO_TIMESTAMP()`	マイクロ秒単位の UNIX タイムスタンプを TIMESTAMP に変換します。
`UTC_USEC_TO_DAY()`	マイクロ秒単位の UNIX タイムスタンプをシフトして、その日の 0 時 0 分 0 秒に変換します。
`UTC_USEC_TO_HOUR()`	マイクロ秒単位の UNIX タイムスタンプをシフトして、その時間の 0 分 0 秒に変換します。
`UTC_USEC_TO_MONTH()`	マイクロ秒単位の UNIX タイムスタンプをシフトして、その日が含まれる月の初日に変換します。
`UTC_USEC_TO_WEEK()`	週の何曜日なのかを表すマイクロ秒単位の UNIX タイムスタンプを返します。
`UTC_USEC_TO_YEAR()`	何年なのかを表すマイクロ秒単位の UNIX タイムスタンプを返します。
`WEEK()`	TIMESTAMP が表す週を 1～53 の整数で返します。
`YEAR()`	TIMESTAMP が表す年を返します。

IP 関数
`FORMAT_IP()`	`integer_value` の下位の 32 ビットを、人が読める形式の IPv4 アドレスの文字列に変換します。
`PARSE_IP()`	IPv4 アドレスを表す文字列を、符号なし整数値に変換します。
`FORMAT_PACKED_IP()`	人が読める形式の IP アドレスを `10.1.5.23` または `2620:0:1009:1:216:36ff:feef:3f` の形式で返します。
`PARSE_PACKED_IP()`	BYTES 値の IP アドレスを返します。

JSON 関数
`JSON_EXTRACT()`	JSONPath 式に従って値を選択し、JSON 文字列を返します。
`JSON_EXTRACT_SCALAR()`	JSONPath 式に従って値を選択し、JSON スカラーを返します。

論理演算子
`expr AND expr`	両方の式が true の場合、`true` を返します。
`expr OR expr`	1 つまたは両方の式が true の場合、`true` を返します。
`NOT expr`	式が false の場合、`true` を返します。

数学関数
`ABS()`	引数の絶対値を返します。
`ACOS()`	引数の逆余弦を返します。
`ACOSH()`	引数の逆双曲線余弦を返します。
`ASIN()`	引数の逆正弦を返します。
`ASINH()`	引数の逆双曲線正弦を返します。
`ATAN()`	引数の逆正接を返します。
`ATANH()`	引数の逆双曲線正接を返します。
`ATAN2()`	2 つの引数の 4 象限逆正接を返します。
`CEIL()`	引数を最も近い整数に切り上げ、丸めた値を返します。
`COS()`	引数の余弦を返します。
`COSH()`	引数の双曲線余弦を返します。
`DEGREES()`	ラジアンから度数に変換します。
`EXP()`	`e` の（引数）乗を返します。
`FLOOR()`	引数を最も近い整数に切り捨てます。
`LN()` `LOG()`	引数の自然対数を返します。
`LOG2()`	引数の 2 を底とする対数を返します。
`LOG10()`	引数の 10 を底とする対数を返します。
`PI()`	定数 π を返します。
`POW()`	2 番目の引数のべき乗に対する 1 番目の引数を返します。
`RADIANS()`	度数からラジアンに変換します。
`RAND()`	0.0 以上 1.0 未満の範囲のランダムな浮動小数点数値を返します。
`ROUND()`	引数を最も近い整数に四捨五入します。
`SIN()`	引数の正弦を返します。
`SINH()`	引数の双曲線正弦を返します。
`SQRT()`	式の平方根を返します。
`TAN()`	引数の正接を返します。
`TANH()`	引数の双曲線正接を返します。

正規表現関数
`REGEXP_MATCH()`	引数が正規表現に一致する場合に true を返します。
`REGEXP_EXTRACT()`	正規表現内のキャプチャグループに一致する引数の一部を返します。
`REGEXP_REPLACE()`	正規表現に一致する部分文字列を置き換えます。

文字列関数
`CONCAT()`	複数の文字列を連結した値を返します。ただし、いずれかの値が NULL であれば NULL を返します。
`expr CONTAINS 'str'`	`expr` に指定された文字列引数が含まれている場合、`true` を返します。
`INSTR()`	文字列の初出を 1 としたインデックスを返します。
`LEFT()`	文字列の左端の文字を返します。
`LENGTH()`	文字列の長さを返します。
`LOWER()`	元の文字列をすべて小文字にして返します。
`LPAD()`	文字列の左側に文字を挿入します。
`LTRIM()`	文字列の左側から文字を削除します。
`REPLACE()`	部分文字列のすべての出現を置き換えます。
`RIGHT()`	文字列の右端の文字を返します。
`RPAD()`	文字列の右側に文字を挿入します。
`RTRIM()`	文字列の右側にある後続文字を削除します。
`SPLIT()`	文字列を反復部分文字列に分割します。
`SUBSTR()`	部分文字列を返します ...
`UPPER()`	元の文字列をすべて大文字にして返します。

テーブルワイルドカード関数
`TABLE_DATE_RANGE()`	期間に対応する複数の日次テーブルにクエリを実行します。
`TABLE_DATE_RANGE_STRICT()`	期間に対応する複数の日次テーブルに日付の欠落なくクエリを実行します。
`TABLE_QUERY()`	指定した述語に一致する名前のテーブルにクエリを実行します。

URL 関数
`HOST()`	URL を指定すると、ホスト名の文字列を返します。
`DOMAIN()`	URL を指定すると、ドメインの文字列を返します。
`TLD()`	URL を指定すると、トップレベルドメインと、URL 内の任意の国ドメインを返します。

ウィンドウ関数
`AVG()` `COUNT(*)` `COUNT([DISTINCT])` `MAX()` `MIN()` `STDDEV()` `SUM()`	対応する集計関数と同じオペレーションを行いますが、OVER 句で定義されたウィンドウに対して計算を行います。
`CUME_DIST()`	一連の値の中の特定の値の累積分布を示す double 型の値を返します。
`DENSE_RANK()`	一連の値の中の特定の値の順位を表す整数を返します。
`FIRST_VALUE()`	ウィンドウ内の指定したフィールドの最初の値を返します。
`LAG()`	ウィンドウ内の前の行からデータを読み取ります。
`LAST_VALUE()`	ウィンドウ内の指定したフィールドの最後の値を返します。
`LEAD()`	ウィンドウ内の次の行からデータを読み取ります。
`NTH_VALUE()`	ウィンドウフレームの `<n>` の位置にある `<expr>` の値を返します...
`NTILE()`	ウィンドウを指定した数のバケットに分割します。
`PERCENT_RANK()`	パーティション内の他の行との比較によって計算した現在の行のランクを返します。
`PERCENTILE_CONT()`	ウィンドウに関連するパーセンタイル引数にマップされる補完値を返します ...
`PERCENTILE_DISC()`	ウィンドウ内の引数のパーセンタイルに最も近い値を返します。
`RANK()`	一連の値の中の特定の値の順位を表す整数を返します。
`RATIO_TO_REPORT()`	値の合計に対する個々の値の割合を返します。
`ROW_NUMBER()`	ウィンドウ内のクエリ結果の現在の行番号を返します。

その他の関数
`CASE WHEN ... THEN`	クエリの中で複数の式からいずれかを選択できるようにするには、CASE を使用します。
`CURRENT_USER()`	クエリを実行しているユーザーのメールアドレスを返します。
`EVERY()`	引数がすべての入力に対して true であれば、true を返します。
`FROM_BASE64()`	Base64 でエンコードされた入力文字列を BYTES 形式に変換します。
`HASH()`	64 ビット符号付きハッシュ値を計算して返します ...
`FARM_FINGERPRINT()`	64 ビット符号付きフィンガープリント値を計算して返します ...
`IF()`	1 番目の引数が true の場合、2 番目の引数を返します。それ以外の場合、3 番目の引数を返します。
`POSITION()`	1 を先頭とする数値で引数の位置を返します。
`SHA1()`	SHA1 ハッシュを BYTES 形式で返します。
`SOME()`	引数が入力の 1 つ以上に対して true であれば、true を返します。
`TO_BASE64()`	BYTES 引数を Base64 でエンコードされた文字列に変換します。

クエリ構文

注: キーワードでは、大文字と小文字は区別されません。ただしこのドキュメントでは、明確にする目的で SELECT などのキーワードを大文字で表記しています。

SELECT 句

SELECT 句では、計算の対象となる式のリストを指定します。SELECT 句の式には、フィールド名、リテラル、関数呼び出し（集計関数とウィンドウ関数を含む）、またはこれらの 3 つの組み合わせを含めることができます。式のリストはカンマで区切ります。

式に別名を指定するには、式の後ろにスペースを追加し、その後に識別子を追加します。また、式と別名の間に任意で AS キーワードを追加し、可読性を高めることもできます。SELECT 句に定義されている別名はクエリの GROUP BY、HAVING、および ORDER BY 句で参照できますが、FROM、WHERE、または OMIT RECORD IF 句、あるいは同じ SELECT 句内のその他の式で参照することはできません。

注:

SELECT 句で集計関数を使用する場合は、すべての式で集計関数を使用するか、

SELECT

句の中で集計されないすべてのフィールドをグループキーとして含んでいる GROUP BY 句をクエリで使用する必要があります。次に例を示します。

#legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus; /* Succeeds because all non-aggregated fields are group keys. */

#legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word; /* Fails because corpus is not aggregated nor is it a group key. */

予約語は角かっこを使ってエスケープすれば、フィールド名や別名として使用できます。たとえば、BigQuery 構文の予約語である「partition」という名前の列がある場合、その語を角かっこでエスケープしなければ、そのフィールドを参照するクエリは失敗し、不明瞭なエラーメッセージが返されます。
```
SELECT [partition] FROM ...
```

例

この例では、SELECT 句で別名が定義されており、ORDER BY 句でそれらのいずれかが参照されます。WHERE 句では、word_alias を使用して word 列を参照できないため、名前で参照する必要があるので注意してください。別名 len も WHERE 句の中では認識されませんが、HAVING 句では認識されます。

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

集計関数の WITHIN 修飾子

aggregate_function WITHIN RECORD [ [ AS ] alias ]

集計関数で WITHIN キーワードを使用すると、レコード内の繰り返しの値が集計されます。また、入力レコードごとに 1 つの集計出力が生成されます。このような集計を「スコープ集計」と呼びます。スコープ集計ではレコードごとに 1 つの出力が生成されるため、集計されていない式は GROUP BY 句を使用せずにスコープ集計式と並行して選択できます。

一般的に、RECORD スコープはスコープ集計を使用する場合に使用します。複雑にネストされた反復スキーマでは、サブレコードスコープ内で集計を行わなければならないこともあります。その場合は、上記の構文の RECORD キーワードを、集計を行うスキーマのノード名に置換します。これは上級者向けのテクニックです。詳しくは、データの取り扱いをご覧ください。

例

この例では、スコープの COUNT 集計を実行し、レコードを集計値でフィルタリングして、並べ替えています。

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

FROM 句

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

FROM 句では、クエリの対象となるソースデータを指定します。BigQuery のクエリは、テーブルやサブクエリ、結合テーブル、後述する特殊用途の演算子で修飾されたテーブルに対して直接実行できます。また、これらのデータソースの組み合わせに対してクエリを実行するには、BigQuery の UNION ALL 演算子であるカンマを使用します。

テーブルの参照

テーブルを参照する場合は、datasetId と tableId を指定する必要があります。なお、project_name は必須ではありません。project_name を指定していない場合は、デフォルトで現在のプロジェクトが使用されます。プロジェクト名にダッシュが含まれている場合は、テーブル参照全体を角かっこで囲む必要があります。

例

[my-dashed-project:dataset1.tableName]

テーブルに別名を指定するには、テーブルの後ろにスペースを追加し、その後に識別子を追加します。また、tableId と別名の間に任意で AS キーワードを追加して可読性を高めることもできます。

テーブルから列を参照する場合は、単純な列名を使用するか、別名（指定されている場合）を列名の先頭に付けるか、datasetId および tableId を列名の先頭に付けることができます（project_name が指定されていない場合）。フィールド名にはコロンを使用できないため、列の接頭辞には project_name を含めることはできません。

例

この例では、テーブル接頭辞のない列を参照しています。

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

この例では、列名に接頭辞として datasetId および tableId を付けています。この例では project_name を含めることができない点に注意してください。この方法は、データセットが現在のデフォルトプロジェクトにある場合にのみ使用できます。

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

この例では、列名に接頭辞としてテーブルの別名を付けています。

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

サブクエリの使用

サブクエリとは、ネストされた SELECT ステートメントのことであり、かっこで囲まれています。サブクエリの SELECT 句で計算される式は、テーブルの列と同様に、外側のクエリでも使用できます。

サブクエリは、集合などの式を計算する目的で使用できます。SQL 演算子は、すべてサブクエリでも使用可能です。つまり、サブクエリ自体に他のサブクエリを含めることも、結合やグループ集計を行うこともできます。

`UNION ALL` としてのカンマの使用

GoogleSQL とは異なり、レガシー SQL ではカンマを CROSS JOIN 演算子ではなく、UNION ALL 演算子として使用します。これは、従来の BigQuery では CROSS JOIN がサポートされていなかったため、BigQuery ユーザーは UNION ALL クエリを記述しなければならなかったという経緯によるものです。GoogleSQL では、結合を実行するクエリは特に冗長になりがちです。結合演算子としてカンマを使用すると、このようなクエリを効率的に記述できるようになります。たとえば、このクエリを使用すると、複数日のログに対するクエリを 1 回で実行できます。

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

多数のテーブルを結合するクエリは、同じ量のデータを持つ単一のテーブルに対する同じクエリよりも時間がかかるのが一般的です。テーブルが 1 つ増えるごとに最大で 50 ミリ秒くらいのパフォーマンスの違いが出ます。結合を実行できるテーブル数の上限は 1,000 です。

テーブルワイルドカード関数

「テーブルワイルドカード関数」は、BigQuery 固有の特別な関数です。テーブルワイルドカード関数は、複数のタイプのフィルタのうちいずれかを使用して、テーブル名のコレクションを一致させる目的で FROM 句の中で使用します。たとえば、TABLE_DATE_RANGE 関数を使用すると、特定の日次テーブルに対してのみクエリを実行できます。これらの関数について詳しくは、テーブルワイルドカード関数をご覧ください。

FLATTEN 演算子

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

通常の SQL 処理システムとは異なり、BigQuery は繰り返しデータの処理を前提として設計されています。そのため、繰り返しレコードの構造を操作するクエリを記述しなければならないことがあります。その方法の 1 つが、FLATTEN 演算子です。

FLATTEN は、スキーマの中のノードを繰り返しからオプションに変換します。繰り返しフィールドに対して 1 つ以上の値を持つレコードが存在する場合、FLATTEN によって繰り返しフィールドの値ごとに 1 つのレコードが生成されます。レコードから選択されたその他すべてのフィールドは、新たな出力レコードごとに重複して書き込まれます。FLATTEN は、複数レベルの繰り返しを避けるため、繰り返し適用できます。

詳細と例については、データの取り扱いをご覧ください。

JOIN 演算子

BigQuery では、1 つの FROM 句の中で複数の JOIN 演算子を使用できます。それ以降の JOIN オペレーションでは、直前の JOIN オペレーションの結果が JOIN の左入力として使用されます。以前の JOIN 入力に含まれていたフィールドは、その後の JOIN 演算子の ON 句のキーとして使うことができます。

JOIN のタイプ

BigQuery は、INNER、[FULL|RIGHT|LEFT] OUTER、および CROSS JOIN 演算子をサポートしています。指定しない場合は、デフォルトで INNER が使用されます。

CROSS JOIN 演算子では、ON 句を使用できません。CROSS JOIN によって大量のデータが返されることがあるため、遅くて効率の悪いクエリになる可能性や、クエリごとの最大許容リソースを超過するクエリになる可能性があります。このようなクエリはエラーが発生して失敗します。可能であれば、CROSS JOIN を使わないクエリを使用してください。たとえば、CROSS JOIN はウィンドウ関数の方が効率が良い場合に使用するのが一般的です。

EACH 修飾子

EACH 修飾子は、複数のパーティションを使用して JOIN オペレーションを行うことを BigQuery にヒントとして知らせます。JOIN の両側が大きい場合に特に便利です。EACH 修飾子を CROSS JOIN 句で使用することはできません。

これまで EACH は多くのケースで推奨されていましたが、現在は違います。パフォーマンスを向上させるため、可能であれば EACH 修飾子を付けない JOIN を使うようにします。JOIN EACH は、リソース超過のエラーメッセージが表示されてクエリが失敗した場合に使ってください。

セミ結合とアンチ結合

BigQuery では、FROM 句で JOIN をサポートしているだけでなく、WHERE 句でもセミ結合とアンチセミ結合という 2 種類の結合をサポートしています。セミ結合は、サブクエリを持つ IN キーワードを使用して指定し、アンチ結合は NOT IN キーワードで指定します。

例

次のクエリでは、セミ結合を使用し、1 単語目が別の ngram の 2 単語目であり、かつその ngram の 3 単語目が「AND」である ngram を探します。

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

次のクエリでは、セミ結合を使用して、出生率が高い上位 10 州で出産した 51 歳以上の女性の数を返します。

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

残り 40 州の数を見るには、アンチ結合を使用します。次のクエリは前の例とほぼ同じですが、IN ではなく、NOT IN を使用することで、出生率が低い下位 40 州で出産した 50 歳を超える女性の数を返します。

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

注:

BigQuery では、相関関係のあるセミ結合やアンチセミ結合はサポートしていません。サブクエリでは、外側のクエリのフィールドを参照できません。
セミ結合やアンチセミ結合で使用するサブクエリでは、1 つのフィールドしか選択できません。
選択するフィールドのタイプと、WHERE 句の外側のクエリから使用されているフィールドは完全に一致する必要があります。BigQuery は、セミ結合やアンチセミ結合に対する型強制は行いません。

WHERE 句

WHERE 句（述語）では、FROM 句で生成されたレコードをブール式を使用してフィルタリングします。複数の条件は、ブール値 AND 句と OR 句を使って結合できます。これらは、オプションでかっこで囲むことにより、グループ化できます。WHERE 句に記述するフィールドは、対応する SELECT 句で選択されている必要はありません。また、WHERE 句の式は、その WHERE 句が属するクエリの SELECT 句で計算される式を参照できません。

注: WHERE 句で集計関数を使用することはできません。集計関数の出力をフィルタリングする必要がある場合は、HAVING 句と外部クエリを使ってください。

例

次の例では、WHERE 句のブール式（OR 演算子で結合された 2 つの式）を分離しています。いずれかの式が true を返した場合、入力レコードは WHERE フィルタを通過します。

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

OMIT RECORD IF 句

OMIT RECORD IF 句は、BigQuery 固有の構文です。ネスト化された反復スキーマに対処する場合に特に便利です。WHERE 句に似ていますが、2 つの大きな相違点があります。1 つ目は排他的な条件を使用するという点です。つまり、式が true を返した場合、レコードは省略されますが、false や null を返した場合は保持されます。2 つ目は、OMIT RECORD IF 句では条件の中でスコープ集計関数を使用できるという点であり、ほとんどの場合は使用します。

レコード全体をフィルタリングするだけでなく、OMIT...IF では狭いスコープを指定し、レコードの一部だけをフィルタリングすることもできます。これは、OMIT...IF 句の RECORD ではなく、スキーマの非リーフノードの名前を使用して行われます。ただし、BigQuery ユーザーがこの機能を使用することはほとんどありません。これは上級者向けの方法であり、詳細については、前述の WITHIN で紹介しているドキュメントをご覧ください。

OMIT...IF を使用して、繰り返しフィールドのレコード部分を除外し、クエリで他の繰り返しフィールドを個別に選択すると、BigQuery は他の繰り返しレコードの部分をクエリで省略します。Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, というエラーが発生した場合には、Google SQL に切り替えることをおすすめします。OMIT...IF ステートメントを Google SQL に移行する方法について詳しくは、Google SQL への移行をご覧ください。

例

前述の WITHIN 修飾子で紹介した例で OMIT RECORD IF を使用すると、WITHIN と HAVING を使った場合と同じ結果が得られます。

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

GROUP BY 句

GROUP BY 句では、特定のフィールドやフィールドセットに対して同じ値を持つ行をグループ化し、関連するフィールドの集計を計算します。グループ化は、WHERE 句でフィルタリングが実行された後で、SELECT 句の式が計算される前に実行されます。式の結果は、GROUP BY 句のグループキーとして使用できません。

例

このクエリは、trigrams サンプルデータセットで最初の単語としてよく使われている上位 10 個の単語を検索します。この例では、GROUP BY 句の使用方法だけでなく、GROUP BY 句と ORDER BY 句のフィールド名の代わりに位置インデックスを使用する方法も示しています。

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

GROUP BY 句を使用して実行する集計は「グループ集計」と呼ばれます。グループ集計は、スコープ集計とは異なり、ほとんどの SQL 処理システムで一般的に行われています。

`EACH` 修飾子

EACH 修飾子は、複数のパーティションを使用して GROUP BY オペレーションを行うことを BigQuery にヒントとして知らせます。グループキーに対応する個別の値がデータセットに多数含まれている場合は、特に便利です。

これまで EACH は多くのケースで推奨されていましたが、現在は違います。多くの場合、EACH 修飾子を付けずに GROUP BY を使用したほうがパフォーマンスが向上します。GROUP EACH BY は、リソース超過のエラーメッセージが表示されてクエリが失敗した場合に使ってください。

`ROLLUP` 関数

ROLLUP 関数を使用すると、クエリ結果にロールアップ集計の行が追加されます。ROLLUP の後にリストするすべてのフィールドは、かっこで囲む必要があります。ROLLUP 関数の実行によって追加された行では、NULL とは集計がロールアップされた列を指します。

例

このクエリでは、出生率のサンプルデータセットから、男性と女性の 1 年ごとの出生数を生成します。

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

クエリの結果は以下のとおりです。グループキーのいずれか、または両方が NULL の行があります。このような行は、ロールアップされた行です。

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

ROLLUP 関数を使用する場合、GROUPING 関数を使うことで、ROLLUP 関数を実行した結果追加された行と、グループキーが実際に NULL 値になる行を区別できます。

例

このクエリでは、前の例に GROUPING 関数を追加し、ROLLUP 関数を実行した結果追加された行を区別しやすくしています。

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

新しいクエリの結果は次のとおりです。

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

注:

SELECT 句の集計されていないフィールドは、必ず GROUP BY 句でリストする必要があります。

#legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus; /* Succeeds because all non-aggregated fields are group keys. */

#legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word;  /* Fails because corpus is not aggregated nor is it a group key. */

SELECT 句で計算された式は、対応する


    GROUP BY

句では使用できません。

#legacySQL
SELECT
  word,
  corpus,
  COUNT(word) word_count
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus,
  word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */

float 値と double 値によるグループ化は、サポートされていません。これは、これらの型に対応する同等の関数が明確に定義されていないためです。
システムは対話型であるため、多数のグループを生成するクエリは失敗することがあります。GROUP BY の代わりに TOP 関数を使用すると、一部のスケーリングの問題を解決できる可能性があります。

HAVING 句

HAVING 句は、SELECT 句の後に評価されるために計算されるすべての式の結果が HAVING 句で認識可能であることを除き、WHERE 句とまったく同じように動作します。HAVING 句で参照できるのは、対応する SELECT 句の出力だけです。

例

このクエリは、文字「a」を含み、最大で 10,000 回出現する ngram サンプルデータセットの中で、最も頻度の高い最初の単語を計算します。

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

ORDER BY 句

ORDER BY 句では、クエリの結果を 1 つまたは複数のキーフィールドを使用して昇順または降順で並べ替えます。複数のフィールドまたは別名で並べ替える場合は、カンマ区切りのリストとして入力します。結果は、リストされた順序でフィールド上で並べ替えられます。並べ替えの方向は、DESC（降順）または ASC（昇順）を使用して指定します。デフォルトは ASC です。並べ替えキーごとに、異なる並べ替え順序を指定することもできます。

ORDER BY 句は、SELECT で計算された式の出力を参照できるように、SELECT 句の後に評価されます。SELECT でフィールドの別名が指定されている場合は、その別名を ORDER BY 句で使用する必要があります。

LIMIT 句

LIMIT 句は、返される結果セットの行数を制限します。BigQuery のクエリは大量の行を処理するのが一般的なので、LIMIT は行の一部だけを処理し、クエリのオペレーション時間が長くなることを避けるのに有効な方法です。

注:

LIMIT 句は、要件を満たした時点で処理を中止し、結果を返します。これにより、一部のクエリの処理時間を短縮できる場合がありますが、COUNT 句や ORDER BY 句などの集計関数を指定する場合は、結果を返す前に完全な結果セットが処理されている必要があります。LIMIT 句は、最後に評価されます。
クエリに LIMIT 句が含まれていても、そのクエリに結果セットの出力順を保証する演算子がなければ、同じ結果を得られないことがあります。これは、BigQuery が大量の並列ワーカーを使用して実行されるためです。並列ジョブが返される順序は保証されません。
LIMIT 句に関数を含めることはできません。LIMIT 句は数値定数のみを受け取ります。

クエリ文法

BigQuery の SELECT ステートメントの各句については、前述の詳細のとおりです。ここでは、SELECT ステートメントの全文法を簡潔に示します。また、各セクションへのリンクも用意されています。

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

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

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

表記:

角かっこ [ ] はオプションの句です。
波かっこ { } ではオプションのセットを囲みます。
縦線「|」は論理 OR を表します。
角かっこ内でカンマやキーワードの後に省略記号が続いている場合（[, ... ]）、その前にある項目は、指定された区切り記号を使ってリスト内で繰り返すことができることを意味します。
丸括弧「( )」はリテラルの括弧を表します。

集計関数

集計関数は、大きなデータセットの概要を表す値を返すため、特にログの分析などで役立ちます。集計関数は値のコレクションを対象として動作し、テーブル、グループ、スコープごとに 1 つの値を返します。

テーブル集計
集計関数を使用して、テーブルの対象の行をすべて集計します。次に例を示します。

SELECT COUNT(f1) FROM ds.Table;
グループ集計
集計関数と、集計されていないフィールドを指定する GROUP BY 句を使用して、グループ別に行を要約します。次に例を示します。

SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

TOP 関数は、グループ集計の特殊なケースを表します。
スコープ集計
この機能は、ネストされたフィールドがあるテーブルのみに適用されます。
集計関数と WITHIN キーワードを使用して、定義したスコープ内の繰り返しの値を集計します。次に例を示します。

SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

スコープは、行全体またはノード（行の繰り返しフィールド）に対応する RECORD とすることができます。集計関数はスコープ内の値に対して動作し、各レコードまたはノードの集計結果を返します。

集計関数には、次に示すオプションのいずれかを使って制限を加えることができます。

サブセレクトクエリの中の別名。この制限は、外側の WHERE 句で指定します。

#legacySQL
SELECT corpus, count_corpus_words
FROM
  (SELECT corpus, count(word) AS count_corpus_words
  FROM [bigquery-public-data:samples.shakespeare]
  GROUP BY corpus) AS sub_shakespeare
WHERE count_corpus_words > 4000

HAVING 句の中の別名。

#legacySQL
SELECT corpus, count(word) AS count_corpus_words
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY corpus
HAVING count_corpus_words > 4000;

GROUP BY 句または ORDER BY 句の別名も参照できます。

構文

集計関数
`AVG()`	ある行のグループの値の平均値を返します ...
`BIT_AND()`	ビット演算 AND を実行した結果を返します ...
`BIT_OR()`	ビット演算 OR を実行した結果を返します ...
`BIT_XOR()`	ビット演算 XOR を実行した結果を返します ...
`CORR()`	一連の数値ペアのピアソン相関係数を返します。
`COUNT()`	値の合計数を返します ...
`COUNT([DISTINCT])`	非 NULL 値の合計数を返します ...
`COVAR_POP()`	値の母共分散を計算します ...
`COVAR_SAMP()`	値の標本共分散を計算します ...
`EXACT_COUNT_DISTINCT()`	非 NULL 値の正確な値を返します。これは指定したフィールドの一意の値です。
`FIRST()`	関数のスコープ内で最初の連続値を返します。
`GROUP_CONCAT()`	複数の文字列を連結して 1 つの文字列にします ...
`GROUP_CONCAT_UNQUOTED()`	複数の文字列を連結して 1 つの文字列にします ... 二重引用符を追加しません ...
`LAST()`	最後の連続値を返します ...
`MAX()`	最大値を返します ...
`MIN()`	最小値を返します ...
`NEST()`	現在の集計スコープ内のすべての値を繰り返しフィールドにまとめます。
`NTH()`	n 番目の連続値を返します ...
`QUANTILES()`	おおよその最小値、最大値、変位値を計算します ...
`STDDEV()`	標準偏差を返します ...
`STDDEV_POP()`	母標準偏差を返します ...
`STDDEV_SAMP()`	標本標準偏差を返します ...
`SUM()`	値の合計値を返します ...
`TOP() ... COUNT(*)`	頻度別の上位の max_records レコードを返します。
`UNIQUE()`	一意の非 NULL 値のセットを返します ...
`VARIANCE()`	値の分散を計算します ...
`VAR_POP()`	値の母分散を計算します ...
`VAR_SAMP()`	値の標本分散を計算します ...

AVG(numeric_expr): ある行のグループについて、numeric_expr によって計算された値の平均値を返します。NULL 値を持つ行は計算に含まれません。
BIT_AND(numeric_expr): すべての行について、numeric_expr の各インスタンス間でのビット AND 演算の結果を返します。NULL 値は無視されます。この関数は、numeric_expr のすべてのインスタンスが NULL に評価された場合に NULL を返します。
BIT_OR(numeric_expr): すべての行について、numeric_expr の各インスタンス間でのビット OR 演算の結果を返します。NULL 値は無視されます。この関数は、num

レガシー SQL 関数と演算子

サポートされる関数と演算子

クエリ構文

SELECT 句

例

集計関数の WITHIN 修飾子

例

FROM 句

テーブルの参照

例

例

サブクエリの使用

UNION ALL としてのカンマの使用

テーブル ワイルドカード関数

FLATTEN 演算子

JOIN 演算子

JOIN のタイプ

EACH 修飾子

セミ結合とアンチ結合

例

WHERE 句

例

OMIT RECORD IF 句

例

GROUP BY 句

例

EACH 修飾子

ROLLUP 関数

例

例

HAVING 句

例

ORDER BY 句

LIMIT 句

クエリ文法

集計関数

構文

`UNION ALL` としてのカンマの使用

テーブルワイルドカード関数

`EACH` 修飾子

`ROLLUP` 関数