レガシー 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