レガシー SQL の構文、関数、演算子
このドキュメントでは、レガシー SQL のクエリ構文、関数、演算子について詳しく説明します。BigQuery で推奨されるクエリ構文は GoogleSQL です。GoogleSQL の詳細については、GoogleSQL のクエリ構文をご覧ください。
クエリ構文
注: キーワードでは、大文字と小文字は区別されません。ただしこのドキュメントでは、明確にする目的で 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 ] |JOINclause |FLATTENclause | 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;
整数範囲パーティション分割テーブル
レガシー SQL では、テーブル デコレータを使用して、整数範囲パーティション分割テーブルの特定のパーティションを扱えます。範囲パーティションを処理する際、範囲の開始値が重要となります。
次の例では、30 で始まる範囲パーティションをクエリします。
#legacySQL SELECT * FROM dataset.table$30;
レガシー SQL を使用して、整数範囲パーティション分割テーブル全体をクエリすることはできません。そうしたクエリを実行すると、次のようなエラーが返されます。
Querying tables partitioned on a field is not supported in Legacy SQL
サブクエリの使用
サブクエリとは、ネストされた 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>,」というエラーが表示された場合は、GoogleSQL に切り替えることをおすすめします。OMIT...IF ステートメントの GoogleSQL への移行については、GoogleSQL への移行をご覧ください。
例
前述の 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 BYclause. */ - 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 を表します。
- 角かっこ内でカンマやキーワードの後に省略記号が続いている場合（[, ... ]）、その前にある項目は、指定された区切り記号を使ってリスト内で繰り返すことができることを意味します。
- 丸括弧「( )」はリテラルの括弧を表します。
サポートされる関数と演算子
ほとんどの 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 != expr2expr1 <> 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 でエンコードされた文字列に変換します。 |
集計関数
集計関数は、大きなデータセットの概要を表す値を返すため、特にログの分析などで役立ちます。集計関数は値のコレクションを対象として動作し、テーブル、グループ、スコープごとに 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値は無視されます。この関数は、numeric_exprのすべてのインスタンスがNULLに評価された場合にNULLを返します。 BIT_XOR(numeric_expr)- すべての行について、
numeric_exprの各インスタンス間でのビットXOR演算の結果を返します。NULL値は無視されます。この関数は、numeric_exprのすべてのインスタンスがNULLに評価された場合にNULLを返します。 CORR(numeric_expr, numeric_expr)- 一連の数値ペアのピアソン相関係数を返します。
COUNT(*)- 関数のスコープ内の値（NULL と非 NULL）の合計数を返します。
TOP関数とともにCOUNT(*)を使用している場合を除き、カウントするフィールドを明示的に指定することをおすすめします。 COUNT([DISTINCT] field [, n])- 関数のスコープ内の非 NULL 値の合計数を返します。
DISTINCTキーワードを使用した場合、この関数は指定したフィールドの一意の値の数を返します。ただし、DISTINCTを指定したときに返される値は統計的近似値であり、正確な値であるとは限りません。正確な値を得るために、
EXACT_COUNT_DISTINCT()を使用してください。より高い精度を
COUNT(DISTINCT)nを指定できます。このパラメータにより、正確な結果が確保されるようにしきい値が設定されます。デフォルトではnは 1000 ですが、それよりも大きいnを指定すれば、nのその値までのCOUNT(DISTINCT)の戻り値が正確なものになります。ただし、nの値を大きくすると、この演算子のスケーラビリティが下がり、クエリの実行時間が大幅に延びることや、クエリがエラーを起こすことがあります。一意の値の正確な数を計算するには、EXACT_COUNT_DISTINCT を使用します。よりスケーラブルなアプローチとして、該当するフィールドに対して
GROUP EACH BYを使ってからCOUNT(*)を適用する方法もあります。GROUP EACH BYを使用するアプローチの方がスケーラビリティが高くなりますが、最初のパフォーマンスがわずかに損なわれることがあります。 COVAR_POP(numeric_expr1, numeric_expr2)numeric_expr1とnumeric_expr2で計算された値の母共分散を計算します。COVAR_SAMP(numeric_expr1, numeric_expr2)numeric_expr1とnumeric_expr2で計算された値の標本共分散を計算します。EXACT_COUNT_DISTINCT(field)- 非 NULL 値の正確な値を返します。これは指定したフィールドの一意の値です。スケーラビリティとパフォーマンスを高めるには、COUNT(DISTINCT field) を使用します。
FIRST(expr)- 関数のスコープ内で最初の連続値を返します。
GROUP_CONCAT('str' [, separator])-
複数の文字列を連結して 1 つの文字列にします。このとき、個々の値は
separatorパラメータ（省略可）で区切られます。separatorを省略した場合は、カンマ区切りの文字列が返されます。ソースデータの文字列に二重引用符が含まれる場合、
GROUP_CONCATでは二重引用符を追加した文字列が返されます。たとえば、a"bという文字列は、"a""b"という形で返されます。二重引用符を追加していない文字列を返す場合には、GROUP_CONCAT_UNQUOTEDを使用します。例:
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])-
複数の文字列を連結して 1 つの文字列にします。このとき、個々の値は
separatorパラメータ（省略可）で区切られます。separatorを省略した場合は、カンマ区切りの文字列が返されます。GROUP_CONCATとは異なり、二重引用符を含む値を返す場合に、二重引用符を追加しません。たとえば、a"bという文字列は、a"bという形で返されます。例:
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)- 関数のスコープ内で最後の連続値を返します。
MAX(field)- 関数のスコープ内の最大値を返します。
MIN(field)- 関数のスコープ内の最小値を返します。
NEST(expr)-
現在の集計スコープ内のすべての値を繰り返しフィールドにまとめます。たとえば、
"SELECT x, NEST(y) FROM ... GROUP BY x"というクエリは、一意のx値ごとに 1 つの出力レコードを返し、その中にはクエリ入力内でxとペアになっていたすべてのy値をまとめた繰り返しフィールドが含まれます。NEST関数にはGROUP BY句が必要です。BigQuery はクエリの結果を自動的にフラット化するので、トップレベル クエリで
NEST関数を使っても、その結果には繰り返しフィールドは含まれません。NEST関数は、同じクエリがそのまま使用できる中間結果を生成するサブセレクトを呼び出す場合に使います。 NTH(n, field)- 関数のスコープ内の
n番目の連続値を返します。nは定数です。NTH関数は 1 からカウントを開始するので、0 番目の項目はありません。関数のスコープがn値よりも少ない場合、NULLを返します。 QUANTILES(expr[, buckets])-
入力式のおおよその最小値、最大値、変位値を計算します。
NULL入力値は無視されます。空白または排他的なNULLが入力されると、NULLが出力されます。計算される変位値の数は、オプションのbucketsパラメータで制御されます。カウントには最小値と最大値も含まれます。おおよその N タイルを計算するには、N+1bucketsを使用します。bucketsのデフォルト値は 100 です（注: デフォルト値の 100 では、パーセンタイルを推定できません。パーセンタイルを推定するには、101buckets以上を使う必要があります）。明示的に指定する場合、bucketsは少なくとも 2 にしてください。変位値あたりの小数誤差は epsilon = 1 /
bucketsとなります。つまり、バケット数が増えれば誤差が減るということです。次に例を示します。QUANTILES(<expr>, 2) # computes min and max with 50% error. QUANTILES(<expr>, 3) # computes min, median, and max with 33% error. QUANTILES(<expr>, 5) # computes quartiles with 25% error. QUANTILES(<expr>, 11) # computes deciles with 10% error. QUANTILES(<expr>, 21) # computes vigintiles with 5% error. QUANTILES(<expr>, 101) # computes percentiles with 1% error.
NTH関数は特定の変位値を選ぶ目的で使用できます。ただし、NTHは 1 を基準としていて、QUANTILESは最初の位置で最小値（0 番目の変位値）、最後の位置で最大値（100 番目のパーセンタイルまたは N 番目の N タイル）を返す点に注意してください。たとえば、NTH(11, QUANTILES(expr, 21))はexprの中央値を推定しますが、NTH(20, QUANTILES(expr, 21))はexprの 19 番目の二十分位値（95 番目のパーセンタイル）を推定します。どちらの推定値も、5% の誤差を見込んでいます。精度を高めるには、バケットを増やします。たとえば、前述の計算の誤差範囲を 5% から 0.1% に減らすには、バケット数を 21 から 1,001 に変更し、それに応じて
NTH関数の引数を調整します。0.1% の誤差で中央値を計算するには、NTH(501, QUANTILES(expr, 1001))を使用します。0.1% の誤差で 95 番目のパーセンタイルを計算するには、NTH(951, QUANTILES(expr, 1001))を使用します。 STDDEV(numeric_expr)numeric_exprによって計算された値の標準偏差を返します。NULL 値を持つ行は計算に含まれません。STDDEV関数はSTDDEV_SAMPの別名です。STDDEV_POP(numeric_expr)numeric_exprにより算出された値の母標準偏差を計算します。対象となる母集団全体を含むデータセットの標準偏差を計算するには、STDDEV_POP()を使用します。データセットに母集団の代表的な標本しか含まれていない場合は、代わりにSTDDEV_SAMP()を使用します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項をご覧ください。STDDEV_SAMP(numeric_expr)numeric_exprにより算出された値の標本標準偏差を計算します。母集団の代表的な標本に基づいて母集団全体の標準偏差を計算するには、STDDEV_SAMP()を使用します。データセットが母集団全体を構成する場合は、代わりにSTDDEV_POP()を使用します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項をご覧ください。SUM(field)- 関数のスコープ内の値の合計値を返します。数値データ型のみで使用します。
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)- 頻度別の上位の max_records レコードを返します。詳細については、下の TOP の説明を参照してください。
UNIQUE(expr)- 関数のスコープ内にある一意の非 NULL 値のセットを、定義されていない順序で返します。
EACHキーワードを付けていない大規模なGROUP BY句と同様に、一意の値が多すぎる場合は「リソース超過」エラーが発生してクエリは失敗します。ただし、GROUP BY句と異なり、UNIQUE関数はスコープ集計に適用でき、値の数に制限があるネスト化されたフィールドでも効率的に動作します。 VARIANCE(numeric_expr)numeric_exprによって計算された値の分散を計算します。NULL 値を持つ行は計算に含まれません。VARIANCE関数はVAR_SAMPの別名です。VAR_POP(numeric_expr)numeric_exprによって計算された値の母分散を計算します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項をご覧ください。VAR_SAMP(numeric_expr)numeric_exprによって計算された値の標本分散を計算します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項をご覧ください。
TOP() 関数
TOP は、GROUP BY 句に代わる関数です。これは、GROUP BY ... ORDER BY ... LIMIT ... の簡略化された構文として使用されます。通常、TOP 関数は完全な ... GROUP BY ... ORDER BY ... LIMIT ... クエリよりも処理が速くなりますが、概算値しか返されない場合があります。TOP 関数の構文は次のとおりです。
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
SELECT 句で TOP を使用するときは、フィールドの 1 つとして COUNT(*) を含める必要があります。
TOP() 関数を使用するクエリは、2 つのフィールド（TOP フィールドと COUNT(*) 値）のみを返すことができます。
field|alias- 返すフィールドまたは別名。
max_values- （オプション）返される結果の最大件数。デフォルト値は 20 です。
multiplierCOUNT(*)によって返された値に掛けられる正の整数値。
TOP() の例
-
TOP()を使用する基本的なクエリの例次のクエリは
TOP()を使って 10 行を返します。例 1:
#legacySQL SELECT TOP(word, 10) as word, COUNT(*) as cnt FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th";
例 2:
#legacySQL SELECT word, left(word, 3) FROM (SELECT TOP(word, 10) AS word, COUNT(*) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th");
-
TOP()とGROUP BY...ORDER BY...LIMITを比較するこのクエリは、「th」を含む単語の中で最も頻度の高い 10 個と、その単語が使われているドキュメントの数を頻度順に返します。
TOPクエリのほうが、はるかに高速に実行されます。TOP()を使用しない例:#legacySQL SELECT word, COUNT(*) AS cnt FROM ds.Table WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;
TOP()を使用する例:#legacySQL SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';
-
multiplierパラメータを使用します。次のクエリは、
multiplierパラメータがクエリの結果に与える影響を示したものです。最初のクエリは、ワイオミング州の毎月の出生数を返します。2 つ目のクエリは、multiplierパラメータを使ってcnt値に 100 を掛けます。multiplierパラメータを使用しない場合の例:#legacySQL SELECT TOP(month,3) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
戻り値:
+-------+-------+ | month | cnt | +-------+-------+ | 7 | 19594 | | 5 | 19038 | | 8 | 19030 | +-------+-------+
multiplierパラメータを使用する場合の例:#legacySQL SELECT TOP(month,3,100) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
戻り値:
+-------+---------+ | month | cnt | +-------+---------+ | 7 | 1959400 | | 5 | 1903800 | | 8 | 1903000 | +-------+---------+
注: TOP を使用するには、SELECT 句に COUNT(*) を含める必要があります。
高度な例
-
条件によってグループ化された平均値と標準偏差
次のクエリは、2003 年にオハイオ州で生まれた子供の出生時体重の平均と標準偏差を、母親の喫煙の有無によってグループ化して返します。
例:
#legacySQL SELECT cigarette_use, /* Finds average and standard deviation */ AVG(weight_pounds) baby_weight, STDDEV(weight_pounds) baby_weight_stdev, AVG(mother_age) mother_age FROM [bigquery-public-data:samples.natality] WHERE year=2003 AND state='OH' /* Group the result values by those */ /* who smoked and those who didn't. */ GROUP BY cigarette_use;
-
集計値を使用したクエリ結果のフィルタリング
集計値を使ってクエリの結果をフィルタリングするためには（たとえば
SUMの値によるフィルタリング）、HAVING関数を使います。HAVINGは、集計を行う前に各行を操作するWHEREとは異なり、集計関数のオペレーション結果と値を比較します。例:
#legacySQL SELECT state, /* If 'is_male' is True, return 'Male', */ /* otherwise return 'Female' */ IF (is_male, 'Male', 'Female') AS sex, /* The count value is aliased as 'cnt' */ /* and used in the HAVING clause below. */ COUNT(*) AS cnt FROM [bigquery-public-data:samples.natality] WHERE state != '' GROUP BY state, sex HAVING cnt > 3000000 ORDER BY cnt DESC
戻り値:
+-------+--------+---------+ | state | sex | cnt | +-------+--------+---------+ | CA | Male | 7060826 | | CA | Female | 6733288 | | TX | Male | 5107542 | | TX | Female | 4879247 | | NY | Male | 4442246 | | NY | Female | 4227891 | | IL | Male | 3089555 | +-------+--------+---------+
算術演算子
算術演算子は、引数として数値を取り、数値の結果を返します。引数は、数値リテラルでもクエリから返される数値でもかまいません。算術演算を評価した結果が未定義になった場合、その演算からは NULL が返されます。
構文
| 演算子 | 説明 | 例 |
|---|---|---|
| + | 加算 |
戻り値: 10 |
| - | 減算 |
戻り値: 1 |
| * | 乗算 |
戻り値: 24 |
| / | 除算 |
戻り値: 1.5 |
| % | 剰余 |
戻り値: 2 |
ビット演算関数
ビット演算関数は個々のビットレベルで動作し、引数は数値でなければなりません。ビット演算関数について詳しくは、ビット演算をご覧ください。
集計関数では、ここで説明しているビット演算関数以外に、BIT_AND、BIT_OR、BIT_XOR の 3 つのビット演算関数について説明しています。
構文
| 演算子 | 説明 | 例 |
|---|---|---|
| & | ビット演算 AND |
戻り値: 0 |
| | | ビット演算 OR |
戻り値: 28 |
| ^ | ビット演算 XOR |
戻り値: 1 |
| << | ビット演算左シフト |
戻り値: 16 |
| >> | ビット演算右シフト |
戻り値: 2 |
| ~ | ビット演算 NOT |
戻り値: -3 |
BIT_COUNT(<numeric_expr>) |
|
戻り値: 4 |
キャスト関数
キャスト関数は、数値式のデータ型を変換します。キャスト関数は、比較関数の引数が同じデータ型になるようにする場合に特に役立ちます。
構文
| キャスト関数 | |
|---|---|
BOOLEAN() |
ブール値にキャストします。 |
BYTES() |
bytes 値にキャストします。 |
CAST(expr AS type) |
expr を type 型の変数に変換します。 |
FLOAT() |
double 値にキャストします。 |
HEX_STRING() |
16 進数の文字列にキャストします。 |
INTEGER() |
整数にキャストします。 |
STRING() |
文字列にキャストします。 |
BOOLEAN(<numeric_expr>)-
<numeric_expr>が 0 と NULL のいずれでもない場合、trueを返します。<numeric_expr>が 0 の場合、falseを返します。<numeric_expr>が NULL の場合、NULLを返します。
BYTES(string_expr)bytes型の値として、string_exprを返します。CAST(expr AS type)exprをtype型の変数に変換します。FLOAT(expr)exprを double として返します。exprは'45.78'のような文字列にできますが、この関数は数値以外の値に対してNULLを返します。HEX_STRING(numeric_expr)numeric_exprを 16 進数の文字列として返します。INTEGER(expr)exprを 64 ビットの整数にキャストします。exprが整数値に対応しない文字列の場合、NULL を返します。exprがタイムスタンプの場合、UNIX エポックからの時間をマイクロ秒単位で返します。
STRING(numeric_expr)numeric_exprを文字列として返します。
比較関数
比較関数は、次のタイプの比較に基づいて true または false を返します。
- 2 つの式の比較
- 1 つまたは複数の式が一定の条件（指定されたリスト内にある、NULL である、デフォルト以外のオプションの値であるなど）を満たしているかどうかの比較
以下に示す関数の一部は true または false 以外の値を返しますが、返される値は比較オペレーションに基づくものです。
比較関数の引数として、数値式または文字列式のいずれかの式を使用できます （文字列定数は、単一引用符または二重引用符で囲む必要があります）。式は、クエリによってフェッチされたリテラルまたは値とすることができます。比較関数は、ほとんどの場合、WHERE 句でフィルタ条件として使用されますが、他の句で使用することもできます。
構文
| 比較関数 | |
|---|---|
expr1 = expr2 |
式が等しい場合、true を返します。 |
expr1 != expr2expr1 <> 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 を返します。 |
expr1 = expr2- 式が等しい場合、
trueを返します。 expr1 != expr2
expr1 <> expr2- 式が等しくない場合、
trueを返します。 expr1 > expr2expr1がexpr2よりも大きい場合、trueを返します。expr1 < expr2expr1がexpr2よりも小さい場合、trueを返します。expr1 >= expr2expr1がexpr2以上の場合、trueを返します。expr1 <= expr2expr1がexpr2以下の場合、trueを返します。expr1 BETWEEN expr2 AND expr3-
expr1の値がexpr2以上であり、かつ、expr3以下である場合、trueを返します。 expr IS NULLexprが NULL の場合、trueを返します。expr IN(expr1, expr2, ...)exprがexpr1、expr2、またはかっこ内の任意の値に一致する場合、trueを返します。INキーワードは、(expr = expr1 || expr = expr2 || ...)を効率的に簡略化したものです。INキーワードで使用する式は定数で、exprのデータ型と一致している必要があります。IN句はセミ結合やアンチ結合の作成にも使用できます。詳しくは、セミ結合とアンチ結合をご覧ください。COALESCE(<expr1>, <expr2>, ...)- NULL ではない最初の引数を返します。
GREATEST(numeric_expr1, numeric_expr2, ...)-
最大の
numeric_exprパラメータを返します。すべてのパラメータは、同じデータ型の数値でなければなりません。パラメータの中に 1 つでもNULLがある場合、この関数はNULLを返します。NULL値を無視するには、IFNULL関数を使ってNULL値を比較に影響を及ぼさない値に書き換えます。次のコード例では、IFNULL関数を使ってNULLを正数の比較に影響を及ぼさない値（-1）に変更しています。SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)exprが NULL ではない場合はexprを返し、それ以外の場合はnull_defaultを返します。IS_INF(numeric_expr)numeric_exprが正または負の無限大である場合、trueを返します。IS_NAN(numeric_expr)numeric_exprが特殊なNaN数値である場合、trueを返します。IS_EXPLICITLY_DEFINED(expr)-
この関数は非推奨となりました。
expr IS NOT NULLを代わりに使用してください。 LEAST(numeric_expr1, numeric_expr2, ...)-
最小の
numeric_exprパラメータを返します。すべてのパラメータは、同じデータ型の数値でなければなりません。パラメータの中に 1 つでもNULLがある場合、この関数はNULLを返します。 NVL(expr, null_default)exprが NULL ではない場合はexprを返し、それ以外の場合はnull_defaultを返します。NVL関数はIFNULLの別名です。
日付と時刻の関数
次に挙げる関数は、UNIX タイムスタンプ、日付を表す文字列、TIMESTAMP 型を対象として、日付と時刻の操作を行います。TIMESTAMP 型の操作方法について詳しくは、TIMESTAMP の使い方をご覧ください。
UNIX タイムスタンプを操作する日付と時刻の関数は、UNIX 時間に基づいて動作します。また、日付と時刻の関数は UTC タイムゾーンに基づいて値を返します。
構文
| 日付と時刻の関数 | |
|---|---|
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 が表す年を返します。 |
CURRENT_DATE()現在の日付を、人が読める形式の文字列として
%Y-%m-%dの形式で返します。例:
SELECT CURRENT_DATE();戻り値: 2013-02-01
CURRENT_TIME()サーバーの現在の時刻を、人が読める文字列として
%H:%M:%Sの形式で返します。例:
SELECT CURRENT_TIME();戻り値: 01:32:56
CURRENT_TIMESTAMP()サーバーの現在の時刻を、TIMESTAMP データ型として
%Y-%m-%d %H:%M:%Sの形式で返します。例:
SELECT CURRENT_TIMESTAMP();戻り値: 2013-02-01 01:33:35 UTC
DATE(<timestamp>)TIMESTAMP データ型を、人が読める形式の文字列として
%Y-%m-%dの形式で返します。例:
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));戻り値: 2012-10-01
DATE_ADD(<timestamp>,<interval>,
<interval_units>)指定した長さの時間を TIMESTAMP データ型の値に加算します。
interval_unitsの可能な値は、YEAR、MONTH、DAY、HOUR、MINUTE、SECONDなどです。intervalが負の数である場合、TIMESTAMP データ型からその時間が減算されます。例:
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");戻り値: 2017-10-01 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");戻り値: 2007-10-01 02:03:04 UTC
DATEDIFF(<timestamp1>,<timestamp2>)2 つの TIMESTAMP データ型値の間の日数を返します。最初の TIMESTAMP データ型が 2 番目の TIMESTAMP データ型よりも後にある場合、結果は正になり、それ以外の場合は負になります。
例:
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));戻り値: 466
例:
SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));戻り値: -466
DAY(<timestamp>)TIMESTAMP データ型の値が月の何日目なのかを 1～31 の整数で返します。
例:
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 2
DAYOFWEEK(<timestamp>)TIMESTAMP データ型の値が週の何日目なのかを 1（日曜日）から 7（土曜日）までの整数で返します。
例:
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));戻り値: 2
DAYOFYEAR(<timestamp>)TIMESTAMP データ型の値が年の何日目なのかを 1～366 の整数で返します。整数 1 は 1 月 1 日を表します。
例:
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));戻り値: 275
FORMAT_UTC_USEC(<unix_timestamp>)UNIX タイムスタンプを、人が読める形式の文字列として
YYYY-MM-DD HH:MM:SS.uuuuuuの形式で返します。例:
SELECT FORMAT_UTC_USEC(1274259481071200);戻り値: 2010-05-19 08:58:01.071200
HOUR(<timestamp>)TIMESTAMP データ型の値の時間を 0～23 の整数で返します。
例:
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 5
MINUTE(<timestamp>)TIMESTAMP データ型の値の分を 0～59 までの整数で返します。
例:
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 23
MONTH(<timestamp>)TIMESTAMP データ型の値の月を 1～12 までの整数で返します。
例:
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 10
MSEC_TO_TIMESTAMP(<expr>)- ミリ秒単位の UNIX タイムスタンプを TIMESTAMP データ型に変換します。
例:
SELECT MSEC_TO_TIMESTAMP(1349053323000);戻り値: 2012-10-01 01:02:03 UTC
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)戻り値: 2012-10-01 01:02:04 UTC
NOW()現在の UNIX タイムスタンプをマイクロ秒単位で返します。
例:
SELECT NOW();戻り値: 1359685811687920
PARSE_UTC_USEC(<date_string>)-
日付文字列をマイクロ秒単位の UNIX タイムスタンプに変換します。
date_stringはYYYY-MM-DD HH:MM:SS[.uuuuuu]の形式である必要があります。秒の小数部分は最大 6 桁まで指定するか、省略できます。TIMESTAMP_TO_USEC も同じ動作を行う関数ですが、日付文字列ではなく、TIMESTAMP データ型の引数を変換します。
例:
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");戻り値: 1349056984000000
QUARTER(<timestamp>)TIMESTAMP データ型の値が表している四半期がいつなのかを 1～4 の整数で返します。
例:
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));戻り値: 4
SEC_TO_TIMESTAMP(<expr>)秒単位の UNIX タイムスタンプを TIMESTAMP データ型に変換します。
例:
SELECT SEC_TO_TIMESTAMP(1355968987);戻り値: 2012-12-20 02:03:07 UTC
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));戻り値: 2012-12-20 02:03:07 UTC
SECOND(<timestamp>)-
TIMESTAMP データ型が表している時刻の秒を 0～59 の整数で返します。
うるう秒の場合は、0～60 の整数を返します。
例:
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 48
STRFTIME_UTC_USEC(<unix_timestamp>,
<date_format_str>)-
現在の日付を、人が読める形式の文字列として date_format_str の形式で返します。date_format_str には、日時の表現に使われる記号（/、- など）や、strftime function in C++ で使用可能な特殊文字（年月日の日を表す %d など）を含めることができます。
特定の月のデータをすべて取得する場合など、クエリデータを時間間隔別にグループ化することを計画している場合は、
UTC_USEC_TO_<function_name>関数を使用します。この関数の方が効率的です。例:
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");戻り値: 2010-05-19
TIME(<timestamp>)TIMESTAMP データ型を、人が読める形式の文字列として
%H:%M:%Sの形式で返します。例:
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));戻り値: 02:03:04
TIMESTAMP(<date_string>)日付文字列を TIMESTAMP データ型に変換します。
例:
SELECT TIMESTAMP("2012-10-01 01:02:03");戻り値: 2012-10-01 01:02:03 UTC
TIMESTAMP_TO_MSEC(<timestamp>)TIMESTAMP データ型の値をミリ秒単位の UNIX タイムスタンプに変換します。
例:
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));戻り値: 1349053323000
TIMESTAMP_TO_SEC(<timestamp>)- TIMESTAMP データ型の値を秒単位の UNIX タイムスタンプに変換します。
例:
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));戻り値: 1349053323
TIMESTAMP_TO_USEC(<timestamp>)-
TIMESTAMP データ型の値をマイクロ秒単位の UNIX タイムスタンプに変換します。
PARSE_TO_USEC も同じ動作を行う関数ですが、TIMESTAMP データ型ではなくデータ文字列の引数を変換します。
例:
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));戻り値: 1349053323000000
USEC_TO_TIMESTAMP(<expr>)マイクロ秒単位の UNIX タイムスタンプを TIMESTAMP データ型に変換します。
例:
SELECT USEC_TO_TIMESTAMP(1349053323000000);戻り値: 2012-10-01 01:02:03 UTC
SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)戻り値: 2012-10-01 01:02:04 UTC
UTC_USEC_TO_DAY(<unix_timestamp>)-
マイクロ秒単位の UNIX タイムスタンプをシフトして、その日の 0 時 0 分 0 秒に変換します。
たとえば、
unix_timestampが 5 月 19 日 8 時 58 分を表す場合、この関数は 5 月 19 日 0 時 0 分（午前 0 時）を表す UNIX タイムスタンプを返します。例:
SELECT UTC_USEC_TO_DAY(1274259481071200);戻り値: 1274227200000000
UTC_USEC_TO_HOUR(<unix_timestamp>)-
マイクロ秒単位の UNIX タイムスタンプをシフトして、その時間の 0 分 0 秒に変換します。
たとえば、
unix_timestampが 8 時 58 分を表す場合、この関数は同じ日の 8 時 0 分を表す UNIX タイムスタンプを返します。例:
SELECT UTC_USEC_TO_HOUR(1274259481071200);戻り値: 1274256000000000
UTC_USEC_TO_MONTH(<unix_timestamp>)-
マイクロ秒単位の UNIX タイムスタンプをシフトして、その日が含まれる月の初日に変換します。
たとえば、
unix_timestampが 3 月 19 日を表す場合、この関数は同じ年の 3 月 1 日を表す UNIX タイムスタンプを返します。例:
SELECT UTC_USEC_TO_MONTH(1274259481071200);戻り値: 1272672000000000
UTC_USEC_TO_WEEK(<unix_timestamp>,
<day_of_week>)-
unix_timestamp引数に含まれる曜日を表す、マイクロ秒単位の UNIX タイムスタンプを返します。この関数はマイクロ秒単位の UNIX タイムスタンプと 0（日曜）から 6（土曜）までの 2 つの引数を取ります。たとえば、
unix_timestampが 2008 年 4 月 11 日（金曜日）を表す場合に、day_of_weekを 2（火曜日）に設定すると、この関数は、2008 年 4 月 8 日（火曜日）を表す UNIX タイムスタンプを返します。例:
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;戻り値: 1207612800000000
UTC_USEC_TO_YEAR(<unix_timestamp>)-
unix_timestamp引数に含まれる年を表す、マイクロ秒単位の UNIX タイムスタンプを返します。たとえば、
unix_timestampが 2010 年の任意の日時を表す場合も、この関数は2010-01-01 00:00をマイクロ秒単位で表した1274259481071200を返します。例:
SELECT UTC_USEC_TO_YEAR(1274259481071200);戻り値: 1262304000000000
WEEK(<timestamp>)TIMESTAMP データ型の週を 1～53 の整数で返します。
1 週間は日曜日に始まるので、1 月 1 日が日曜日以外であれば第 1 週目は 7 日未満になり、最初の日曜日は第 2 週目の初日になります。
例:
SELECT WEEK(TIMESTAMP('2014-12-31'));戻り値: 53
YEAR(<timestamp>)- TIMESTAMP データ型の年を返します。
例:
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));戻り値: 2012
高度な例
-
整数のタイムスタンプを人が読める形式の日時に変換する
次のクエリは、Wikipedia の改訂件数が多かった上位 5 つの日時を検索します。人が読める形式で結果を表示するために、BigQuery の
FORMAT_UTC_USEC()関数を使用しています。この関数は、入力としてマイクロ秒単位のタイムスタンプを取るので、Wikipedia の POSIX 形式のタイムスタンプ（秒単位）に 1,000,000 を掛けてマイクロ秒単位に変換しています。例:
#legacySQL SELECT /* Multiply timestamp by 1000000 and convert */ /* into a more human-readable format. */ TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5) AS top_revision_time, COUNT (*) AS revision_count FROM [bigquery-public-data:samples.wikipedia];
戻り値:
+----------------------------+----------------+ | top_revision_time | revision_count | +----------------------------+----------------+ | 2002-02-25 15:51:15.000000 | 20976 | | 2002-02-25 15:43:11.000000 | 15974 | | 2010-02-02 03:34:51.000000 | 3 | | 2010-02-02 01:04:59.000000 | 3 | | 2010-02-01 23:55:05.000000 | 3 | +----------------------------+----------------+
-
タイムスタンプを基準として結果をグループ分けする
クエリの結果を特定の年、月、日などに分類してまとめる場合は、日付 / 時刻関数が役立ちます。次の例では、
UTC_USEC_TO_MONTH()関数を使って、個々の Wikipedia 参加者が改訂コメントに使う月あたりの文字数を表示します。例:
#legacySQL SELECT contributor_username, /* Return the timestamp shifted to the * start of the month, formatted in * a human-readable format. Uses the * 'LEFT()' string function to return only * the first 7 characters of the formatted timestamp. */ LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(timestamp * 1000000)),7) AS month, SUM(LENGTH(comment)) as total_chars_used FROM [bigquery-public-data:samples.wikipedia] WHERE (contributor_username != '' AND contributor_username IS NOT NULL) AND timestamp > 1133395200 AND timestamp < 1157068800 GROUP BY contributor_username, month ORDER BY total_chars_used DESC;
戻り値（抄出）:
+--------------------------------+---------+-----------------------+ | contributor_username | month | total_chars_used | +--------------------------------+---------+-----------------------+ | Kingbotk | 2006-08 | 18015066 | | SmackBot | 2006-03 | 7838365 | | SmackBot | 2006-05 | 5148863 | | Tawkerbot2 | 2006-05 | 4434348 | | Cydebot | 2006-06 | 3380577 | etc ...
IP 関数
IP 関数は、IP アドレスと人が読める形式を相互に変換します。
構文
| 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 アドレスを返します。 |
FORMAT_IP(integer_value)integer_valueの下位の 32 ビットを、人が読める形式の IPv4 アドレスの文字列に変換します。たとえば、FORMAT_IP(1)では'0.0.0.1'という文字列が返されます。PARSE_IP(readable_ip)- IPv4 アドレスを表す文字列を、符号なし整数値に変換します。たとえば、
PARSE_IP('0.0.0.1')では1が返されます。文字列が有効な IPv4 アドレスでない場合、PARSE_IPはNULLを返します。
BigQuery は、パックされた文字列の IPv4 アドレスと IPv6 アドレスの、ネットワーク バイト順の 4 バイトまたは 16 バイトのバイナリデータでの記述をサポートしています。以下に示す関数は、人が読める形式のアドレスの変換をサポートしています。これらの関数は、IP アドレス文字列を含む文字列フィールドだけで動作します。
構文
FORMAT_PACKED_IP(packed_ip)人が読める形式の IP アドレスを
10.1.5.23または2620:0:1009:1:216:36ff:feef:3fの形式で返します。例:FORMAT_PACKED_IP('0123456789@ABCDE')は'3031:3233:3435:3637:3839:4041:4243:4445'を返します。FORMAT_PACKED_IP('0123')は'48.49.50.51'を返します。
PARSE_PACKED_IP(readable_ip)IP アドレスを BYTES 値で返します。入力文字列が有効な IPv4 または IPv6 アドレスでない場合、
PARSE_PACKED_IPはNULLを返します。次に例を示します。PARSE_PACKED_IP('48.49.50.51')は'MDEyMw=='を返します。PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')は'MDEyMzQ1Njc4OUBBQkNERQ=='を返します。
JSON 関数
BigQuery の JSON 関数を使用すると、格納されている JSON データから、JSONPath のような式を使って値を取り出すことができます。
テーブル スキーマ内ですべての個別フィールドを宣言するよりも、JSON データを格納したほうが柔軟に運用できますが、コストが高くなる場合もあります。JSON 文字列からデータを選択すると、文字列全体のスキャンに対するコストがかかるため、個々のフィールドが別々の列になっているときよりも高コストになります。また、クエリの時点で文字列全体を解析しなければならないので、クエリのスピードも遅くなります。ただし、変化が激しいスキーマやその場限りのスキーマでは、余分にコストをかけても JSON の柔軟性がそれ以上の意味を持つ場合があります。
構造化されたデータを操作する場合は、JSON 関数を使ったほうが BigQuery の正規表現関数を使った場合よりも使いやすくなります。
構文
| JSON 関数 | |
|---|---|
JSON_EXTRACT() |
JSONPath 式に従って値を選択し、JSON 文字列を返します。 |
JSON_EXTRACT_SCALAR() |
JSONPath 式に従って値を選択し、JSON スカラーを返します。 |
JSON_EXTRACT(json, json_path)-
JSONPath 式
json_pathに従って、jsonの値を選択します。json_pathは文字列定数である必要があります。JSON 文字列形式の値を返します。 JSON_EXTRACT_SCALAR(json, json_path)-
JSONPath 式
json_pathに従って、jsonの値を選択します。json_pathは文字列定数である必要があります。スカラー JSON 値を返します。
論理演算子
論理演算子は、2 値または 3 値の論理式を実行します。2 値論理は true または false を返します。3 値論理は NULL 値に対応しており、true、false、または NULL を返します。
構文
| 論理演算子 | |
|---|---|
expr AND expr |
両方の式が true の場合、true を返します。 |
expr OR expr |
1 つまたは両方の式が true の場合、true を返します。 |
NOT expr |
式が false の場合、true を返します。 |
expr AND expr- 両方の式が true の場合、
trueを返します。 - いずれかまたは両方が false の場合
falseを返します。 - 両方の式が NULL であるか、1 つの式が true で他方が NULL である場合、
NULLを返します。
- 両方の式が true の場合、
expr OR expr- 1 つまたは両方の式が true の場合、
trueを返します。 - 両方の式が false の場合、
falseを返します。 - 両方の式が NULL であるか、1 つの式が false で他方が NULL である場合、
NULLを返します。
- 1 つまたは両方の式が true の場合、
NOT expr- 式が false の場合、
trueを返します。 - 式が true の場合
falseを返します。 - 式が NULL の場合は
NULLを返します。
NOTを否定演算子として他の機能とともに使用できます。 たとえば、NOT IN(expr1, expr2)やIS NOT NULLです。- 式が false の場合、
数学関数
数学関数は数値の引数を取り、数値の結果を返します。引数は、数値リテラルでもクエリから返される数値でもかまいません。数学関数の評価結果が未定義になった場合、戻り値は NULL になります。
構文
| 数学関数 | |
|---|---|
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() |
引数の双曲線正接を返します。 |
ABS(numeric_expr)- 引数の絶対値を返します。
ACOS(numeric_expr)- 引数の逆余弦を返します。
ACOSH(numeric_expr)- 引数の逆双曲線余弦を返します。
ASIN(numeric_expr)- 引数の逆正弦を返します。
ASINH(numeric_expr)- 引数の逆双曲線正弦を返します。
ATAN(numeric_expr)- 引数の逆正接を返します。
ATANH(numeric_expr)- 引数の逆双曲線正接を返します。
ATAN2(numeric_expr1, numeric_expr2)- 2 つの引数の逆正接を返します。
CEIL(numeric_expr)- 引数を最も近い整数に切り上げ、丸めた値を返します。
COS(numeric_expr)- 引数の余弦を返します。
COSH(numeric_expr)- 引数の双曲線余弦を返します。
DEGREES(numeric_expr)- ラジアンから度数に変換された
numeric_exprを返します。 EXP(numeric_expr)- 定数 e（自然対数の底）を numeric_expr で累乗した結果を返します。
FLOOR(numeric_expr)- 引数を最も近い整数に切り捨て、丸めた値を返します。
LN(numeric_expr)
LOG(numeric_expr)- 引数の自然対数を返します。
LOG2(numeric_expr)- 引数の 2 を底とする対数を返します。
LOG10(numeric_expr)- 引数の 10 を底とする対数を返します。
PI()- 定数 π を返します。
PI()関数は、それが関数であることを示すためにかっこを必要としますが、かっこ内では引数を取りません。数学関数や算術関数では、PI()を定数のように使用できます。 POW(numeric_expr1, numeric_expr2)numeric_expr1をnumeric_expr2に累乗した結果を返します。RADIANS(numeric_expr)- 度数からラジアンに変換された
numeric_exprを返します。（π ラジアンは 180 度）。 RAND([int32_seed])- 0.0 以上 1.0 未満の範囲のランダムな浮動小数点数値を返します。
LIMIT句を使用しない限り、各int32_seed値は常に、指定されたクエリ内で同じ乱数列を生成します。int32_seedを指定しない場合、BigQuery はシード値として現在のタイムスタンプを使用します。 ROUND(numeric_expr [, digits])- 引数を最も近い整数に四捨五入して（桁数が指定されている場合はそれに合わせる）、丸めた値を返します。
SIN(numeric_expr)- 引数の正弦を返します。
SINH(numeric_expr)- 引数の双曲線正弦を返します。
SQRT(numeric_expr)- 式の平方根を返します。
TAN(numeric_expr)- 引数の正接を返します。
TANH(numeric_expr)- 引数の双曲線正接を返します。
高度な例
-
境界ボックスのクエリ
次のクエリは、サンフランシスコ（北緯 37.46 度、西経 122.50 度）を中心とする境界矩形内の点のコレクションを返します。
例:
#legacySQL SELECT year, month, AVG(mean_temp) avg_temp, MIN(min_temperature) min_temp, MAX(max_temperature) max_temp FROM [weather_geo.table] WHERE /* Return values between a pair of */ /* latitude and longitude coordinates */ lat / 1000 > 37.46 AND lat / 1000 < 37.65 AND long / 1000 > -122.50 AND long / 1000 < -122.30 GROUP BY year, month ORDER BY year, month ASC;
-
近似的境界円のクエリ
球面三角法の余弦定理を使って、コロラド州デンバー（北緯 39.73 度、西経 104.98 度）を中心とする近似的な円に含まれる 100 までの地点のコレクションを返します。このクエリでは、
PI()、SIN()、COS()など、BigQuery の数学関数や三角関数が使用されています。地球は完全な平面ではなく、緯度と経度は極で収束するため、このクエリは多くのタイプのデータで役に立つ近似値を返します。
例:
#legacySQL SELECT distance, lat, long, temp FROM (SELECT ((ACOS(SIN(39.73756700 * PI() / 180) * SIN((lat/1000) * PI() / 180) + COS(39.73756700 * PI() / 180) * COS((lat/1000) * PI() / 180) * COS((-104.98471790 - (long/1000)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, AVG(mean_temp) AS temp, AVG(lat/1000) lat, AVG(long/1000) long FROM [weather_geo.table] WHERE month=1 GROUP BY distance) WHERE distance < 100 ORDER BY distance ASC LIMIT 100;
正規表現関数
BigQuery は、re2 ライブラリを使用した正規表現をサポートしています。正規表現の構文については、ドキュメントをご覧ください。
正規表現はグローバルな一致です。単語の先頭で一致を開始するには、^ 文字を使用する必要があります。
構文
| 正規表現関数 | |
|---|---|
REGEXP_MATCH() |
引数が正規表現に一致する場合に true を返します。 |
REGEXP_EXTRACT() |
正規表現内のキャプチャ グループに一致する引数の一部を返します。 |
REGEXP_REPLACE() |
正規表現に一致する部分文字列を置き換えます。 |
REGEXP_MATCH('str', 'reg_exp')str が正規表現に一致する場合に true を返します。正規表現を使用せずに文字列を一致させる場合は、REGEXP_MATCH の代わりに CONTAINS を使用します。
例:
#legacySQL SELECT word, COUNT(word) AS count FROM [bigquery-public-data:samples.shakespeare] WHERE (REGEXP_MATCH(word,r'\w\w\'\w\w')) GROUP BY word ORDER BY count DESC LIMIT 3;
戻り値:
+-------+-------+ | word | count | +-------+-------+ | ne'er | 42 | | we'll | 35 | | We'll | 33 | +-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')正規表現内のキャプチャ グループに一致する str の一部を返します。
例:
#legacySQL SELECT REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment FROM [bigquery-public-data:samples.shakespeare] GROUP BY fragment ORDER BY fragment LIMIT 3;
戻り値:
+----------+ | fragment | +----------+ | NULL | | Al'ce | | As'es | +----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')reg_exp に一致する orig_str の任意の部分文字列が replace_str に置き換えられた文字列を返します。たとえば、REGEXP_REPLACE ('Hello', 'lo', 'p') は Help を返します。
例:
#legacySQL SELECT REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word FROM [bigquery-public-data:samples.shakespeare] WHERE REGEXP_MATCH(word, r'ne\'er') GROUP BY expanded_word ORDER BY expanded_word LIMIT 5;
戻り値:
+---------------+ | expanded_word | +---------------+ | Whenever | | never | | nevertheless | | whenever | +---------------+
高度な例
-
正規表現の一致による結果セットのフィルタリング
BigQuery の正規表現関数は、
WHERE句で使えば結果セットをフィルタリングでき、SELECT句で使えば結果を表示できます。次の例では、1 つのクエリでこの 2 つのユースケースを組み合わせて使っています。例:
#legacySQL SELECT /* Replace white spaces in the title with underscores. */ REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions FROM (SELECT title, COUNT(revision_id) as revisions FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace=0 /* Match titles that start with 'G', end with * 'e', and contain at least two 'o's. */ AND REGEXP_MATCH(title, r'^G.*o.*o.*e$') GROUP BY title ORDER BY revisions DESC LIMIT 100);
-
整数または浮動小数点データに対して正規表現を使う
BigQuery の正規表現関数は文字列データだけを対象とするものですが、整数や浮動小数点数でも
STRING()関数を使えば文字列形式にキャストできます。この例では、STRING()を使用して整数値corpus_dateを文字列にキャストし、後でそれをREGEXP_REPLACEで変更します。例:
#legacySQL SELECT corpus_date, /* Cast the corpus_date to a string value */ REGEXP_REPLACE(STRING(corpus_date), '^16', 'Written in the sixteen hundreds, in the year \'' ) AS date_string FROM [bigquery-public-data:samples.shakespeare] /* Cast the corpus_date to string, */ /* match values that begin with '16' */ WHERE REGEXP_MATCH(STRING(corpus_date), '^16') GROUP BY corpus_date, date_string ORDER BY date_string DESC LIMIT 5;
文字列関数
文字列関数は、文字列データを処理します。文字列定数は、単一引用符または二重引用符で囲む必要があります。文字列関数はデフォルトで大文字と小文字を区別します。クエリの末尾に IGNORE CASE を追加することで、大文字と小文字を区別しない一致を有効化できます。IGNORE CASE を使えるのは ASCII 文字列のみであり、クエリの上位レベルに限定されています。
これらの関数でワイルドカードはサポートされていません。正規表現の機能が必要な場合は、正規表現関数を使用します。
構文
| 文字列関数 | |
|---|---|
CONCAT() |
複数の文字列を連結した値を返します。ただし、いずれかの値が NULL であれば NULL を返します。 |
expr CONTAINS 'str' |
expr に指定された文字列引数が含まれている場合、true を返します。 |
INSTR() |
文字列の初出を 1 としたインデックスを返します。 |
LEFT() |
文字列の左端の文字を返します。 |
LENGTH() |
文字列の長さを返します。 |
LOWER() |
元の文字列をすべて小文字にして返します。 |
LPAD() |
文字列の左側に文字を挿入します。 |
LTRIM() |
文字列の左側から文字を削除します。 |
REPLACE() |
部分文字列のすべての出現を置き換えます。 |
RIGHT() |
文字列の右端の文字を返します。 |
RPAD() |
文字列の右側に文字を挿入します。 |
RTRIM() |
文字列の右側にある後続文字を削除します。 |
SPLIT() |
文字列を反復部分文字列に分割します。 |
SUBSTR() |
部分文字列を返します ... |
UPPER() |
元の文字列をすべて大文字にして返します。 |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- 複数の文字列を連結した値を返します。ただし、いずれかの値が NULL であれば NULL を返します。例:
str1がJavaであり、str2がScriptである場合、CONCATによってJavaScriptが返されます。 expr CONTAINS 'str'exprに指定された文字列引数が含まれている場合、trueを返します。これは、大文字と小文字を区別する比較です。INSTR('str1', 'str2')- str1 内の str2 の初出を 1 としたインデックスを返します。str2 が str1 に出現しない場合は 0 を返します。
LEFT('str', numeric_expr)strの左端から numeric_expr 個の文字を返します。文字数が str より長い場合、文字列全体が返されます。例:LEFT('seattle', 3)はseaを返します。LENGTH('str')- 文字列の長さの数値を返します。例:
strが'123456'の場合、LENGTHは6を返します。 LOWER('str')- 元の文字列をすべて小文字にして返します。
LPAD('str1', numeric_expr, 'str2')str2の左側にstr1をパディングします。結果の文字列がちょうどnumeric_expr文字になるまで、str2を繰り返します。例:LPAD('1', 7, '?')は??????1を返します。LTRIM('str1' [, str2])-
str1 の左側から文字を削除します。str2 が省略されている場合、
LTRIMは str1 の左側から空白文字を削除します。省略されていない場合、LTRIMは str1 の左側から str2 内のすべての文字を削除します（大文字と小文字が区別されます）。例:
SELECT LTRIM("Say hello", "yaS")は" hello"を返します。SELECT LTRIM("Say hello", " ySa")は"hello"を返します。 REPLACE('str1', 'str2', 'str3')-
str1 内の str2 のすべてのインスタンスを str3 に置換します。
RIGHT('str', numeric_expr)strの右端から numeric_expr 個の文字を返します。この数が文字列より長い場合は、文字列全体を返します。例:RIGHT('kirkland', 4)はlandを返します。RPAD('str1', numeric_expr, 'str2')str2の右側にstr1をパディングします。結果の文字列がちょうどnumeric_expr文字になるまで、str2を繰り返します。例:RPAD('1', 7, '?')は1??????を返します。RTRIM('str1' [, str2])-
str1 の右側にある後続文字を削除します。str2 が省略されている場合、
RTRIMは str1 の後続スペースを削除します。省略されていない場合、RTRIMは str1 の右端から str2 内のすべての文字を削除します（大文字と小文字が区別されます）。例:
SELECT RTRIM("Say hello", "leo")は"Say h"を返します。SELECT RTRIM("Say hello ", " hloe")は"Say"を返します。 SPLIT('str' [, 'delimiter'])- 文字列を反復部分文字列に分割します。
delimiterを指定すると、SPLIT関数は区切り文字としてdelimiterを使用してstrを部分文字列に分割します。 SUBSTR('str', index [, max_len])strの部分文字列をindexから返します。オプションのmax_lenパラメータを使用すると、返される文字列は最大でmax_len文字になります。カウントは 1 から始まるので、文字列の最初の文字の位置は 1 になります（ゼロではありません）。indexが5の場合、部分文字列はstrの左から 5 つ目の文字で始まります。indexが-4の場合、部分文字列はstrの右から 4 つ目の文字で始まります。例:SUBSTR('awesome', -4, 4)は部分文字列someを返します。UPPER('str')- 元の文字列をすべて大文字にして返します。
文字列内の特殊文字のエスケープ
特殊文字をエスケープするには、次の方法のいずれかを使用します:
'\xDD'表記を使用します。'\x'の後に、文字の 2 桁の 16 進数表現を続けます。- エスケープのスラッシュは、スラッシュ、単一引用符、二重引用符の前で使用します。
- その他の文字については、C スタイルの配列（
'\a', '\b', '\f', '\n', '\r', '\t',および'\v'）を使用します。
エスケープの例をいくつか示します:
'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported
テーブル ワイルドカード関数
テーブル ワイルドカード関数は、特定のテーブルセットからデータを検索するのに便利な方法です。テーブル ワイルドカード関数は、ワイルドカード関数で検索したすべてのテーブルをカンマ区切りで結合したものに相当します。テーブル ワイルドカード関数を使うと、BigQuery はワイルドカードに一致するテーブルにだけアクセスし、課金します。テーブル ワイルドカード関数は、クエリの FROM 句で指定します。
クエリ内でテーブル ワイルドカード関数を使用する場合、関数をかっこで囲む必要はありません。たとえば、次の例ではかっこを使うものもありますが、使わないものもあります。
キャッシュされた結果は、ワイルドカード関数を使用した複数のテーブルに対するクエリに対してサポートされません（[キャッシュされた結果を使用] オプションがオンになっている場合でも）。同じワイルドカード クエリを複数回実行した場合は、クエリごとに課金されます。
構文
| テーブル ワイルドカード関数 | |
|---|---|
TABLE_DATE_RANGE() |
期間に対応する複数の日次テーブルにクエリを実行します。 |
TABLE_DATE_RANGE_STRICT() |
期間に対応する複数の日次テーブルに日付の欠落なくクエリを実行します。 |
TABLE_QUERY() |
指定した述語に一致する名前のテーブルにクエリを実行します。 |
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)-
<timestamp1>と<timestamp2>の間の時間範囲と重なる日次テーブルに対してクエリを実行します。テーブル名は
<prefix><day>の形式である必要があります。ここで、<day>はYYYYMMDDの形式になります。タイムスタンプのパラメータを生成するために、日付と時刻の関数を使用できます。次に例を示します。
TIMESTAMP('2012-10-01 02:03:04')DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
例: 2 つの日の間のテーブルを取得する
このサンプルは、次のようなテーブルがあることを前提としています。
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
#legacySQL SELECT name FROM TABLE_DATE_RANGE([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27')) WHERE age >= 35
次のテーブルに一致します。
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
例: 今日までの 2 日間のテーブルを取得する
この例では、
myproject-1234という名前のプロジェクトに次のテーブルが存在することが想定されています。- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
#legacySQL SELECT name FROM (TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), CURRENT_TIMESTAMP())) WHERE age >= 35
次のテーブルに一致します。
- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)-
この関数は、
TABLE_DATE_RANGEと同等です。唯一の相違点は、一連の日次テーブルのいずれかが見つからない場合、TABLE_DATE_RANGE_STRICTは失敗し、Not Found: Table <table_name>エラーが返されることです。例: テーブルが存在しない場合のエラー
このサンプルは、次のようなテーブルがあることを前提としています。
- people20140325
- people20140327
#legacySQL SELECT name FROM (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))) WHERE age >= 35
上の例は、「people20140326」テーブルに対して「Not Found」エラーを返します。
TABLE_QUERY(dataset, expr)-
指定された
exprと一致する名前のテーブルにクエリを実行します。exprパラメータは文字列で表現され、かつ評価する式が含まれている必要があります。例:'length(table_id) < 3'例: 名前に「oo」が含まれ、長さが 4 字以上のテーブルに一致する
このサンプルは、次のようなテーブルがあることを前提としています。
- mydata.boo
- mydata.fork
- mydata.ooze
- mydata.spoon
#legacySQL SELECT speed FROM (TABLE_QUERY([myproject-1234:mydata], 'table_id CONTAINS "oo" AND length(table_id) >= 4'))
次のテーブルに一致します。
- mydata.ooze
- mydata.spoon
例: 名前の先頭が「boo」で、その後ろに 3～5 個の数字の連続が含まれているテーブルに一致する
この例では、
myproject-1234という名前のプロジェクトに次のテーブルが存在することが想定されています。- mydata.book4
- mydata.book418
- mydata.boom12345
- mydata.boom123456789
- mydata.taboo999
#legacySQL SELECT speed FROM TABLE_QUERY([myproject-1234:mydata], 'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')
次のテーブルに一致します。
- mydata.book418
- mydata.boom12345
URL 関数
構文
| URL 関数 | |
|---|---|
HOST() |
URL を指定すると、ホスト名の文字列を返します。 |
DOMAIN() |
URL を指定すると、ドメインの文字列を返します。 |
TLD() |
URL を指定すると、トップレベル ドメインと、URL 内の任意の国ドメインを返します。 |
HOST('url_str')- URL を指定すると、文字列としてホスト名を返します。例: HOST('http://www.google.com:80/index.html') は 'www.google.com' を返します。
DOMAIN('url_str')- URL を指定すると、文字列としてドメインを返します。例: DOMAIN('http://www.google.com:80/index.html') は 'google.com' を返します。
TLD('url_str')- URL を指定すると、トップレベル ドメインと、URL 内の任意の国ドメインを返します。例: TLD('http://www.google.com:80/index.html') returns '.com'.TLD('http://www.google.co.uk:80/index.html') は '.co.uk' を返します。
注:
- これらの関数は DNS の逆引きを行わないため、IP アドレスを使ってこれらの関数を呼び出すと、ホスト名のセグメントではなく IP アドレスのセグメントが返されます。
- すべての URL 解析関数において、小文字を使う必要があります。URL 内の大文字は NULL になるか、そうでない場合は不正な結果が引き起こされます。データに大文字と小文字が混在している場合は、この関数への入力を LOWER() を介して渡すことを検討してください。
高度な例
URL データからドメイン名を解析する
このクエリは DOMAIN() 関数を使用して、GitHub にリポジトリ ホームページとして掲載されているドメインの中で最も多いドメインを返します。DOMAIN() 関数の結果を使用してレコードをフィルタリングする HAVING の使用法に注目してください。これは、URL データから参照者情報を判断するうえで便利な関数です。
例:
#legacySQL SELECT DOMAIN(repository_homepage) AS user_domain, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_domain HAVING user_domain IS NOT NULL AND user_domain != '' ORDER BY activity_count DESC LIMIT 5;
戻り値:
+-----------------+----------------+ | user_domain | activity_count | +-----------------+----------------+ | github.com | 281879 | | google.com | 34769 | | khanacademy.org | 17316 | | sourceforge.net | 15103 | | mozilla.org | 14091 | +-----------------+----------------+
特に TLD 情報を調査する場合は、TLD() 関数を使用します。次の例では、一般的な例のリストに含まれていない上位の TLD を表示します。
#legacySQL SELECT TLD(repository_homepage) AS user_tld, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_tld HAVING /* Only consider TLDs that are NOT NULL */ /* or in our list of common TLDs */ user_tld IS NOT NULL AND NOT user_tld IN ('','.com','.net','.org','.info','.edu') ORDER BY activity_count DESC LIMIT 5;
戻り値:
+----------+----------------+ | user_tld | activity_count | +----------+----------------+ | .de | 22934 | | .io | 17528 | | .me | 13652 | | .fr | 12895 | | .co.uk | 9135 | +----------+----------------+
ウィンドウ関数
ウィンドウ関数は分析関数とも呼ばれ、結果セットの中の特定の一部分（これを「ウィンドウ」と呼びます）を対象として計算を実行することができます。ウィンドウ関数を使うと、移動末尾平均や累積合計などの複雑な解析が含まれたレポートを簡単に作成できます。
各ウィンドウ関数には、ウィンドウの最上部と最下部を指定する OVER 句が必要です。また、OVER 句の 3 つの要素（パーティション、オーダー、フレーム）により、ウィンドウをさらに細かくコントロールできます。パーティションを使うと、共通の特長を持つ論理グループに入力データを分割できます。オーダーを使うと、パーティション内の結果に順序を付けることができます。また、フレームを使うと、現在の行に対して相対的に移動するスライディング ウィンドウ フレームをパーティション内で指定することができます。移動するウィンドウ フレームのサイズは、行の数や値の範囲（期間など）に基づいて構成できます。
#legacySQL SELECT <window_function> OVER ( [PARTITION BY <expr>] [ORDER BY <expr> [ASC | DESC]] [<window-frame-clause>] )
PARTITION BY- この関数の操作対象となる、ベース パーティションを定義します。1 つ以上の列名をカンマ区切りで指定します。
GROUP BY句と同じように、列の値セットごとに、パーティションが 1 つずつ作成されます。PARTITION BYが省略されている場合、ベース パーティションはウィンドウ関数に入力されるすべての行となります。 PARTITION BY句を使うことで、ウィンドウ関数でデータをパーティション化し、実行を並列化できるようになります。ウィンドウ関数をallowLargeResultsと組み合わせて使う場合、またはウィンドウ関数の出力に対してさらに結合や集計を適用する場合は、PARTITION BYを指定して実行を並列化します。JOIN EACHやGROUP EACH BY句は、ウィンドウ関数の出力に対して使用できません。ウィンドウ関数の使用時に大規模なクエリ結果を生成するには、PARTITION BYを使用する必要があります。ORDER BY- パーティションを並べ替えます。
ORDER BYが存在しない場合、デフォルトの並べ替え順序は保証されません。並べ替えは、ウィンドウ フレーム句の適用前にパーティション単位で行われます。RANGEウィンドウを指定する場合は、ORDER BY句を付加する必要があります。デフォルトの並び順はASCです。 ORDER BYは省略可能な場合もありますが、rank() や dense_rank() などの一部のウィンドウ関数では必須です。ROWSやRANGEを指定せずにORDER BYを使うと、ORDER BYはパーティションの最初から現在の行までにわたることになります。ORDER BY句がない場合、ウィンドウはパーティション全体になります。<window-frame-clause>-
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>} - オペレーションの対象となるパーティションのサブセット。パーティションと同じサイズか、パーティションよりも小さくなります。
window-frame-clauseを指定せずにORDER BYを使用すると、デフォルトのウィンドウ フレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWになります。ORDER BYとwindow-frame-clauseを両方とも省略すると、デフォルトのウィンドウ フレームはパーティション全体になります。ROWS- 現在の行に対する相対的な行の位置を基準としてウィンドウを定義します。たとえば、前の 5 行の給料の合計を表示する列を追加するには、SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)に対してクエリを実行します。一連の行には、通常は現在の行も含まれますが、これは必須ではありません。RANGE- 指定した列の値の範囲（現在の行内の、列の値との相対）を基準としてウィンドウを定義します。オペレーションの対象となるのは数値と日付のみで、日付の値はシンプルな整数となります（エポック時刻からマイクロ秒単位）。同じ値を持つ隣接行は、「ピア」行と呼ばれます。CURRENT ROWのピア行は、CURRENT ROWを指定するウィンドウ フレーム内に含められます。たとえば、ウィンドウの終わりをCURRENT ROWに指定し、ウィンドウの次の行が同じ値を持つ場合は、その行も関数の計算に含められます。BETWEEN <start> AND <end>- 範囲を表します（最初と最後の行も含む）。範囲には現在の行を含める必要はありませんが、<start>は<end>よりも前、または同じでなければなりません。<start>- ウィンドウの開始オフセットを指定します（現在の行に対して相対的）。次のオプションがサポートされています。{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}<expr>は正の整数、PRECEDINGは先行する行番号または範囲の値、FOLLOWINGは後続の行番号または範囲の値を表します。UNBOUNDED PRECEDINGとは、パーティションの最初の行を指します。<start> がウィンドウよりも前の場合は、パーティションの最初の行に設定されます。<end>- ウィンドウの終了オフセットを指定します（現在の行に対して相対的）。次のオプションがサポートされています。{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}<expr>は正の整数、PRECEDINGは先行する行番号または範囲の値、FOLLOWINGは後続の行番号または範囲の値を表します。UNBOUNDED FOLLOWINGは、パーティションの最終行を指します。<end> がウィンドウの末尾以降の場合は、それがパーティションの最後行に設定されます。
複数の入力行を 1 つの出力行にまとめられる集計関数とは異なり、ウィンドウ関数は 1 つの入力行に対して 1 つの出力行を返します。このような特長から、累積合計や移動平均などを計算するクエリを作成しやすくなります。たとえば、次のクエリは SELECT ステートメントで定義した 5 つの行で構成される小規模なデータセットの累積合計を返します。
#legacySQL SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
戻り値:
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
次の例では、現在の行と、その前の行の値の移動平均を計算します。ウィンドウ フレームは、現在の行に伴って移動する 2 行で構成されます。
#legacySQL SELECT name, value, AVG(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAverage FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
戻り値:
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
構文
| ウィンドウ関数 | |
|---|---|
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() |
ウィンドウ内のクエリ結果の現在の行番号を返します。 |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
- これらのウィンドウ関数は、対応する集計関数と同じ演算を実行しますが、OVER 句で定義されたウィンドウが計算の対象となります。
もう 1 つの重要な違いは、
COUNT([DISTINCT] field)関数をウィンドウ関数として使用すると、EXACT_COUNT_DISTINCT()集計関数と同様の動作で、正確な結果を生成することです。次のクエリ例では、
ORDER BY句によって、ウィンドウの計算対象はパーティションの最初から現在の行までとなるため、その年の累積合計が生成されます。#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count
戻り値:
corpus_date corpus word_count annual_total 0 various 37 37 0 sonnets 157 194 1,590 2kinghenryvi 18 18 1,590 1kinghenryvi 24 42 1,590 3kinghenryvi 40 82 CUME_DIST()-
<number of rows preceding or tied with the current row> / <total rows>という式で計算された一連の値の中で、特定の値の累積分布を示す double 型の値を返します。同点の値は、同じ累積分布値を返します。このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
戻り値:
word word_count cume_dist handkerchief 29 0.2 satisfaction 5 0.4 displeasure 4 0.8 instruments 4 0.8 circumstance 3 1.0 DENSE_RANK()-
一連の値の中における特定の値の順位を表す整数を返します。順位は、グループ内の他の値との比較によって計算されます。
同点の値は同じ順位として表示されます。次の値の順位は、1 つ大きい値になります。たとえば、2 つの値が 2 位になっている場合、次の値は 3 位です。このようなときに、次の値を 4 にする場合は rank() を使います。
このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count dense_rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 4 FIRST_VALUE(<field_name>)-
ウィンドウ内の
<field_name>の最初の値を返します。#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
word word_count fv imperfectly 1 imperfectly LAG(<expr>[, <offset>[, <default_value>]])-
ウィンドウ内の前の行からデータを読み取ります。具体的には、
LAG()は、現在の行よりも<offset>行分前の行に対応する<expr>の値を返します。該当する行がない場合は<default_value>を返します。#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
戻り値:
word word_count lag handkerchief 29 Null satisfaction 5 handkerchief displeasure 4 satisfaction instruments 4 displeasure circumstance 3 instruments LAST_VALUE(<field_name>)-
ウィンドウ内の
<field_name>の最後の値を返します。#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
戻り値:
word word_count lv imperfectly 1 imperfectly LEAD(<expr>[, <offset>[, <default_value>]])-
ウィンドウ内の次の行からデータを読み取ります。具体的には、
LEAD()は、現在の行よりも<offset>行分後の行に対応する<expr>の値を返します。該当する行がない場合は<default_value>を返します。#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count lead handkerchief 29 satisfaction satisfaction 5 displeasure displeasure 4 instruments instruments 4 circumstance circumstance 3 null NTH_VALUE(<expr>, <n>)-
ウィンドウ フレームの
<n>の位置にある<expr>の値を返します。<n>は、1 から始まるインデックスです。 NTILE(<num_buckets>)-
行シーケンスを
<num_buckets>個のバケットに分割し、各行に対応する整数のバケット番号を割り当てます。ntile()関数は、できる限り均等にバケット番号を割り当て、各行に対応する 1 から<num_buckets>までの値を返します。#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count ntile handkerchief 29 1 satisfaction 5 1 displeasure 4 1 instruments 4 2 circumstance 3 2 PERCENT_RANK()-
パーティション内の他の行との比較によって計算した現在の行のランクを返します。戻り値は 0 以上 1 以下です。返される最初の値は 0.0 です。
このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_rank handkerchief 29 0.0 satisfaction 5 0.25 displeasure 4 0.5 instruments 4 0.5 circumstance 3 1.0 PERCENTILE_CONT(<percentile>)-
ウィンドウに関連するパーセンタイル引数にマップされる補完値を
ORDER BY句ごとに順序付けた後に返します。<percentile>は、0～1 の値にする必要があります。このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_cont handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 PERCENTILE_DISC(<percentile>)-
ウィンドウ内の引数のパーセンタイルに最も近い値を返します。
<percentile>は、0～1 の値にする必要があります。このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_disc handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 RANK()-
一連の値の中における特定の値の順位を表す整数を返します。順位は、グループ内の他の値との比較によって計算されます。
同点の値は同じ順位として表示されます。次の値の順位は、その前に同点の値が何個あるかによって決まります。たとえば、2 位が 2 個あった場合、次の順位は 3 ではなく 4 になります。このようなときに次の値を 3 位とする場合は dense_rank() を使います。
このウィンドウ関数では、
OVER句の中にORDER BYを入れる必要があります。#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 5 RATIO_TO_REPORT(<column>)-
値の合計に対する個々の値の割合を表す 0～1 の double 型の値を返します。
#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count r_to_r handkerchief 29 0.6444444444444445 satisfaction 5 0.1111111111111111 displeasure 4 0.08888888888888889 instruments 4 0.08888888888888889 circumstance 3 0.06666666666666667 ROW_NUMBER()-
ウィンドウ内のクエリ結果の現在の行番号を返します。番号の先頭は 1 です。
#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count row_num handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 4 circumstance 3 5
その他の関数
構文
| その他の関数 | |
|---|---|
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 でエンコードされた文字列に変換します。 |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- クエリの中で複数の式からいずれかを選択できるようにするには、CASE を使用します。WHEN 式はブール値である必要があり、THEN 句と ELSE 句のすべての式は互換型でなければなりません。
CURRENT_USER()- クエリを実行しているユーザーのメールアドレスを返します。
EVERY(<condition>)- そのすべての入力に対して
conditionが true の場合、trueを返します。繰り返しフィールドを対象とするクエリでOMIT IF句と併用すると便利です。 FROM_BASE64(<str>)- base64 でエンコードされた入力文字列
strを BYTES 形式に変換します。BYTES を base64 でエンコードされた文字列に変換するには、TO_BASE64() を使用します。 HASH(expr)- CityHash ライブラリ（バージョン 1.0.3）で定義されている方法で、
exprのバイトから 64 ビット符号付きハッシュ値を計算して返します。任意の文字列または整数式がサポートされており、文字列の場合はIGNORE CASEを遵守して、大文字と小文字を区別せずに値を返します。 FARM_FINGERPRINT(expr)- open-source FarmHash library の
Fingerprint64関数を使用して、STRINGまたはBYTES入力の 64 ビット符号付きフィンガープリント値を計算して返します。特定の入力に対して、この関数の出力が変わることはなく、GoogleSQL を使用した場合のFARM_FINGERPRINT関数の出力と一致します。文字列に対してIGNORE CASEを考慮し、大文字と小文字の影響を受けない値を返します。 IF(condition, true_return, false_return)conditionが true または false のいずれであるかに応じて、true_returnまたはfalse_returnを返します。戻り値はリテラルまたはフィールドから派生した値とすることができますが、同じデータ型である必要があります。フィールドから派生した値は、SELECT句に含める必要はありません。POSITION(field)- 一連の反復値フィールドにおける field の位置を、1 を先頭とする数値で返します。
SHA1(<str>)- 入力文字列
strの SHA1 ハッシュを BYTES 形式で返します。TO_BASE64() を使用して、結果を base64 に変換できます。次に例を示します。#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)- その入力の少なくとも 1 つに対して
conditionが true である場合、trueを返します。繰り返しフィールドを対象とするクエリでOMIT IF句と併用すると便利です。 TO_BASE64(<bin_data>)- BYTES 型の入力
bin_dataを base64 でエンコードされた文字列に変換します。次のような例になります。#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
高度な例
-
条件式を使って結果をバケットに分類する
次のクエリは、
CASE/WHENブロックで州のリストに基づいて、結果を「region」に分類します。州がWHENステートメントのいずれかにオプションとして含まれていない場合、state の値はデフォルトで「None」になります。例:
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;
戻り値:
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
ピボット テーブルのシミュレーション
サブセレクト クエリの結果を行と列に整理するには、条件ステートメントを使用します。以下の例では、値「Google」で始まる最もよく改訂されている Wikipedia の記事の検索結果が列に整理され、各条件を満たしている場合は変更カウントが表示されます。
例:
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );
戻り値:
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
HASH を使用した、データのランダムなサンプルの選択
クエリによっては、結果セットのランダムなサブサンプリングを使用して有用な結果を得ることができます。ランダムな値をサンプリングするには、
HASH関数を使います。つまり、ハッシュの "n" による剰余が 0 になる結果を返すわけです。たとえば、次のクエリは、「title」を引数として
HASH()を実行し、値の剰余「2」がゼロであるかどうかを確認します。こうすると、約 50% の値に「サンプリングされた」というラベルが付けられます。サンプル数を減らすには、剰余計算に使う値を "2" よりも大きな値にします。このクエリでは、HASHと組み合わせてABS関数を使用しています。HASHが負の値を返すことがあり、負の値に対して剰余演算子を使用すると負の値が返されるためです。例:
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;