Google BigQuery
クエリーリファレンス

このページは、2015 年 3 月 1日現在の https://cloud.google.com/bigquery/query-reference の翻訳です。最新の情報は、こちらの英語のページもご確認ください。 修正等のフィードバックがあれば、こちらからお寄せください

BigQuery のクエリーは、標準 SQL の SELECT 文にアレンジを加えたものを使って書きます。BigQuery は、COUNT、算術演算、文字列操作など、さまざまな関数をサポートしています。このドキュメントでは、BigQuery クエリーの構文と関数の詳細を説明します。

目次

クエリーの構文

BigQuery のすべてのクエリーは、次の形式の SELECT 文です。

SELECT <式 1> [[AS] <別名 1>] [, <式 2> [[AS] <別名 2>], ...]
    [<集計関数>(<式 3>) WITHIN <式 4>]
    [FROM [(FLATTEN(<テーブル名 1>|(<サブクエリー 1>)] [, <テーブル名 2>|(<サブクエリー 2>), ...)]
    [[INNER|LEFT OUTER|CROSS] JOIN [EACH] <テーブル 2>|(<サブクエリー 2>) [[AS] <テーブル別名 2>]
      ON <結合条件 1> [... AND <結合条件 N> ...]]+
    [WHERE <条件>]
    [GROUP [EACH] BY <フィールド 1>|<別名 1> [, <フィールド 2>|<別名 2>, ...]]
    [HAVING <条件>]
    [ORDER BY <フィールド 1>|<別名 1> [DESC|ASC] [, <フィールド 2>|<別名 2> [DESC|ASC], ...]]
    [LIMIT <数値>]
    ;

注意: キーワードは大文字と小文字を区別しませんが、このドキュメントでは、わかりやすくするために SELECT などのキーワードは大文字で示します。

このページの先頭へ

SELECT 節

SELECT <式 1>1 [[AS] <別名 1>], <式 2> [[AS] <別名 2>], ...

SELECT 節は、クエリーが返す値の集合を規定します。SELECT 節に含まれる式( <式 1> など)は、フィールド名、リテラル、これらを操作する関数式のどれでもかまいません。複数の式は、カンマで区切る必要があります。

SELECT 節は、フィールド、リテラル、関数式の別名を定義する AS 節をサポートします。別名を参照できるのは、GROUP BY 節や ORDER BY 節のなかだけです。

注意:

  • 結果に対して COUNT などの集計関数を使う場合、集計されないフィールドをグループにまとめるためには、たとえば次のように GROUP BY 節を使わなければなりません。
    SELECT word, corpus, COUNT(word)
      FROM publicdata:samples.shakespeare
      WHERE word CONTAINS "th" GROUP BY word, corpus; // 成功
    
    SELECT word, corpus, COUNT(word)
      FROM publicdata:samples.shakespeare
      WHERE word CONTAINS "th" GROUP BY word; // 無効! corpus でグループ分けしていない
    


この例は、ソートのために別名を定義しています。

SELECT word, LENGTH(word) AS len
  FROM publicdata:samples.shakespeare
  WHERE word CONTAINS 'th' ORDER BY len;

このページの先頭へ

WITHIN 節

WITHIN 節は、次の構文で SELECT フィールドとして使うことができます。

SELECT <式 1> [WITHIN RECORD|<ノード名>] [[AS] <別名 1>], <式 2> [WITHIN RECORD|<ノード名>] [[AS] <別名 2>], ... 

WITHIN キーワードは、レコード内の子、複数値フィールド、ネストされたフィールドを対象とする集計関数と併用します。

WITHIN キーワードを指定するときには、集計したい範囲を指定しなければなりません。

  • WITHIN RECORD : レコード内の複数の値のデータを集計します。レコードとは、子、複数値フィールドを含む一意なプロトコルバッファメッセージ全体のことです。
  • WITHIN <ノード名> : 指定されたノード内の複数の値に含まれるデータを集計します。<ノード名> とは、集計関数の対象となるフィールドの親ノードです。

より詳しい情報とサンプルについては、デベロッパーズガイドの WITHIN を参照してください。

FROM 節

...
FROM
  [<プロジェクト名>:]<データセット ID>.<テーブル ID> |
  (<サブクエリー>) |
  <テーブルワイルドカード関数>
[[AS] <別名>]
...

<プロジェクト名> は、データセットを含むプロジェクトの名前でオプションです。指定しなければ、現在のプロジェクトが使われます。

注意: プロジェクト名に - が含まれる場合、[my-dashed-project:dataset1.tableName] のようにテーブル参照全体を [] で囲まなければなりません。

<サブクエリー> は、ネストされた SELECT 節です。サブクエリーの結果は、外側の SELECT 文で必要とされる適切な列とデータを含むものでなければなりません。クエリーが複数のサブクエリーやサブクエリーとテーブルの組み合わせを含む場合、テーブルとサブクエリー全体で、メインクエリーの SELECT 節のすべてのフィールドを含んでいなければなりません。クエリーに対するルールは、サブクエリーの末尾がセミコロンにならないことを除けば、サブクエリーにも当てはまります。

<テーブルワイルドカード関数> は、特定の日次テーブルセットだけを対象としてクエリーを実行する TABLE_DATE_RANGE などのテーブルワイルド関数のことです。

<別名> は、主としてサブクエリーに名前を与えなければならない JOIN 文で使われます。フィールドを <別名>.field という形で参照するのは、JOIN の対象となっている 2 つのテーブルを参照するときの曖昧さを取り除くためだけにしてください。テーブルワイルドカード関数とともに <別名> を使ってはなりません。

クエリー要求の一部としてデフォルトデータセットやデフォルトプロジェクト IDを指定していない限り、テーブル名は、データセットとプロジェクト IDで修飾しなければなりません。

SELECT COUNT(*)
  FROM publicdata:samples.shakespeare
  WHERE word CONTAINS "th";

注意: SQL ベースのほかの多くのシステムとは異なり、BigQuery はカンマ構文でテーブルの JOIN ではなく UNION を示します。そのため、次のように互換スキーマを持つ複数のテーブルを対象としてクエリーを実行できます。

// この数日間の疑わしいアクティビティを見つける
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;

多数のテーブルに対して UNION を実行するクエリーは、同じ量のデータを持つ単一のテーブルに対する同じクエリーよりも遅くなります。テーブルが 1 つ増えるごとに、50m 秒くらいまでのパフォーマンスの違いが出ます。UNION を実行できるテーブルの数の上限は 1,000 です。

このページの先頭へ

FLATTEN 節

...(FLATTEN ([<プロジェクト名>:]<データセット ID>.<テーブル ID>, <対象フィールド>))..
...(FLATTEN (<サブクエリー>, <対象フィールド>))..

FLATTEN は、複数値フィールドをオプションフィールドに変換します。多くの値を持つ複数値フィールドが含まれた 1 個のレコードがある場合、FLATTEN はそれを多数のレコードに展開し、もとの複数値フィールドの個々の値に対して 1 つのレコードを作ります。複数値フィールド以外のフィールドは、個々の新レコードを完全な形にするために重複して書き込まれます。FLATTEN は、ネストレベルを 1 つ取り除きます。

より詳しい情報とサンプルについては、デベロッパーズガイドの FLATTEN を参照してください。

JOIN 節

SELECT <フィールド 1> [..., <フィールド N>] FROM
   <テーブル 1> [[AS] <別名 1>]
 [[INNER|LEFT OUTER|CROSS] JOIN [EACH]
   <テーブル 2> [[AS] <別名 2>]
  [ON <JOIN 条件式 1> [... AND <JOIN 条件式 N>]]
 ]+

BigQuery は、1 つの SELECT 文のなかで複数の JOIN 操作をサポートします。BigQuery は、FROM キーワードのあとに現れる最初の入力のペアからスタートして、ペア単位で JOIN 操作を行います。その後の JOIN 操作は、直前の JOIN 操作の結果を左入力として使います。以前の JOIN の入力に含まれるフィールドは、その後の JOIN 操作の ON 節の JOIN キーとして使うことができます。

JOIN タイプ

BigQuery は、INNERLEFT OUTERCROSS JOIN 操作をサポートします。デフォルトは INNER です。

CROSS JOIN 節は、ON 節を含んではなりません。CROSS JOIN 操作は、大量のデータを返すことがあり、遅くて効率の悪いクエリーになる危険があります。可能なら、通常の JOIN を使うようにしてください。

EACH 修飾子

通常の JOIN 操作は、右側のテーブルが持つ圧縮データが 8MB 未満でなければなりません。EACH 修飾子は、JOIN が 2 つの大きなテーブルを参照することになるかもしれないことをクエリー実行エンジンにヒントとして知らせるものです。EACH 修飾子は、CROSS JOIN 節では使えません。

可能なら、最大限のパフォーマンスを確保するために、EACH 修飾子を付けない JOIN を使うようにしてください。JOIN EACH は、テーブルのサイズが大きすぎて普通の JOIN が使えないときに使います。

このページの先頭へ

WHERE 節

... WHERE <条件式> ...

WHERE 節は、クエリーが満たさなければならない条件を指定するもので、述語と呼ばれることもあります。論理 ANDOR 節を使って複数の条件を結合することができます(オプションでかっこを使ってグループにまとめることができます)。WHERE 節に含まれるフィールドは、SELECT 節に含まれている必要はありません。

注意: WHERE 節のなかで集計関数を使うことはできません。集計フィールドを使わなければならないときには HAVING を使ってください。


次の例には、OR で結合された 2 つの節が含まれています。どちらかの条件を満たさなければ、行は結果のなかに含まれません。

SELECT word FROM publicdata:samples.shakespeare
  WHERE
    (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
    (word CONTAINS 'laugh' AND word CONTAINS 'ed');

このページの先頭へ

GROUP BY 節

... GROUP [EACH] BY <フィールド 1>|<別名 1>alias1, <フィールド 2>|<別名 2> ...

GROUP BY 節は、指定されたフィールドの値が同じ行をグループにまとめた上で、グループごとに集計関数を実行します。グループ分けは、SELECT 節の選択や集計が終わったあとで行われます。

たとえば、f1 フィールドの値が同じ行をグループにまとめ、各グループについて f2 の値の SUM を計算するときには、次のようにします。

SELECT f1, SUM(f2)
  FROM ds.Table
  GROUP BY f1;

この種の集計は、グループ集計と呼ばれます。スコープ集計とは異なり、グループ集計は従来のリレーショナルデータベースでもサポートされています。

データセットにグループキーの値が何種類も含まれる場合には、EACH パラメータを使うことができます。しかし、可能な場合には、EACH パラメータのない GROUP BY を使った方がクエリーのパフォーマンスが上がります。

注意:

  • SELECT 節のなかの非集計フィールドは、GROUP BY 節のなかで指定しなければなりません。
    SELECT f1, COUNT(f2), f3 FROM ds.Table GROUP BY f1; // 無効! f3 が GROUP BY 節に含まれていない
    
  • 集計値でグループ分けすることはできません。
    SELECT f1, COUNT(f2) as count FROM ds.Table GROUP BY count; // 無効! 集計フィールドでグループ分けすることはできない
  • floatdouble の等値関数の定義が明確でないので、これらの値によるグループ分けはサポートされていません。
  • システムが対話的なので、大量のグループを作るクエリーは失敗することがあります。この問題は、GROUP BY ではなく TOP 関数を使えば解決できることがあります。

このページの先頭へ

HAVING 節

... HAVING <条件式> ...

HAVING 節は、クエリーが満たさなければならない条件を指定します。論理 ANDOR 節を使って複数の条件を結合することができます(オプションでかっこを使ってグループにまとめることができます)。HAVINGWHERE と似ていますが、集計フィールドをサポートする点が異なります。

HAVING 節は SELECT 節で定義されているフィールドしか参照できないことに注意してください(フィールドが別名を持つ場合には、それを使わなければなりません。別名を持たない場合には、代わりに集計フィールド名を使ってください)。


SELECT keyword, SUM(clicks)/SUM(impressions) ctr FROM ads
  WHERE impressions > 100
  GROUP BY keyword
  HAVING ctr > 0.1;

SELECT foo, SUM(bar) as boo FROM myTable GROUP BY foo HAVING boo>0;

このページの先頭へ

ORDER BY 節

... ORDER BY <フィールド 1>|<別名 1> [DESC|ASC], <フィールド 2>|<別名 2> [DESC|ASC] ...

ORDER BY 節は、1 つ以上のフィールドの昇順、または降順でクエリーの結果をソートします。ソートの向きは DESC(降順)または ASC(昇順)で指定します。デフォルトは ASC です。

SELECT 節のフィールド名か別名を使ってソートできます。複数のフィールド、別名でソートするときには、それらをカンマ区切りリストで指定します。結果は、フィールドが並べられた順序でソートされます。

このページの先頭へ

LIMIT 節

... LIMIT <行数> ...

LIMIT 節は、結果セットの行数を制限します。たとえば、次のクエリーは 5 行の結果しか返しません。

SELECT COUNT(*), word FROM ds.Table WHERE word CONTAINS 'th' GROUP_BY word LIMIT 5;

クエリーは非常に多くの行数を処理することがあるので、代表的なデータを返せば十分なクエリーの場合は、実行時間が長くならないようにするための方法として LIMIT が有用です。

注意:

  • LIMIT 節は、要求を満たしたところで処理を中止し、結果を返します。こうすると、クエリーによっては処理時間を短縮できます。しかし、COUNT などの集計関数や ORDER BY を指定しているときには、結果を返す前に完全な結果セットを生成しなければなりません。
  • 完全な結果セットを作る前に処理を中止する場合、LIMIT 節から返される結果が異なることがあります。これは、クエリーが並列処理されており、並列ジョブが結果を返す順序は保証されていないからです。
  • LIMIT 節で関数を指定することはできません。引数とすることができるのは、数値定数だけです。

このページの先頭へ

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

SELECT 文のほとんどの節は関数をサポートします。関数内で参照されるフィールドとして、どの SELECT 節にも含まれていないものも使えます。そのため、SELECT country, SUM(clicks) FROM table GROUP BY country; というクエリーは、clicks フィールドを直接表示しないものの、有効です。

集計関数

集計関数は、大きなデータセット全体を表現する値を返すため、特にログの分析などで役に立ちます。集計関数は、値のコレクションを対象として動作し、テーブル、グループ、スコープごとに 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 節で指定されます。

    SELECT corpus, count_corpus_words
    FROM
     
    (SELECT corpus, count(word) AS count_corpus_words
      FROM publicdata
    :samples.shakespeare
      GROUP BY corpus
    ) AS sub_shakespeare
    WHERE count_corpus_words
    > 4000
     
  • HAVINGのなかの別名。

    SELECT corpus, count(word) AS count_corpus_words
    FROM publicdata
    :samples.shakespeare
    GROUP BY corpus
    HAVING count_corpus_words
    > 4000;

GROUP BYORDER BY 節でも別名を使えます。

構文

関数 説明
AVG(<数値を返す式>) すべての行について <数値を返す式> で計算された値の平均を返します。値が NULL の行は、計算には含まれません。
BIT_AND(<数値を返す式>) すべての行について <数値を返す式> をビット単位 AND した結果を返します。NULL 値は無視されます。この関数は、すべての <数値を返す式>NULL と評価される場合は NULL を返します。
BIT_OR(<数値を返す式>) すべての行について <数値を返す式> をビット単位 OR した結果を返します。NULL 値は無視されます。この関数は、すべての <数値を返す式>NULL と評価される場合は NULL を返します。
BIT_XOR(<数値を返す式>) すべての行について <数値を返す式> をビット単位 XOR した結果を返します。NULL 値は無視されます。この関数は、すべての <数値を返す式>NULL と評価される場合は NULL を返します。
CORR(<数値を返す式>, <数値を返す式>) 一連の数値ペアのピアソン相関係数を返します。
COUNT(*) 関数のスコープ内にある値( NULL と非 NULL )の個数を返します。TOP 関数のもとで COUNT(*) を使う場合を除き、数えるフィールドは明示的に指定した方がよいでしょう。
COUNT([DISTINCT] <フィールド> [, <n>]) 関数のスコープ内の非 NULL 値の数を返します。

DISTINCT キーワードを使うと、指定されたフィールドの異なる値の数を返します。ただし、DISTINCT を指定したときに返される値は統計的近似値であり、正確性は保証されていないので注意してください。

COUNT(DISTINCT) から返される値の精度を上げなければならない場合には、第 2 引数として <n> という数値を指定します。この数値未満の値なら正確であることが保証されます。デフォルトの <n> は 1000 ですが、それよりも大きい <n> を指定すれば、その値までの COUNT(DISTINCT) の戻り値は正確なものになります。しかし、<n> の値を大きくすると、この演算子のスケーラビリティが下がり、クエリーの実行時間が大幅に延びたり、クエリーがエラーを起こしたりすることがあります。

スケーラブルな形で異なる値の正確な個数を計算したい場合には、関連するフィールドで GROUP EACH BY を使ってから COUNT(*) を実行する方法もあります。この方法は、COUNT(DISTINCT) よりもスケーラビリティが高くなりますが、最初のパフォーマンスがわずかに損なわれることがあります。

COVAR_POP(<数値を返す式 1>, <数値を返す式 2>) <数値を返す式 1><数値を返す式 2> で計算された値の母共分散を計算します。
COVAR_SAMP(<数値を返す式 1>, <数値を返す式 2>) <数値を返す式 1><数値を返す式 2> で計算された値の標本共分散を計算します。
FIRST(<式>) 関数のスコープ内にある最初の連続値を返します。
GROUP_CONCAT('<文字列>' [, <区切り子>]) 複数の文字列を連結して 1 つの文字列にします。このとき、個々の値はオプションの <区切り子> 引数によって区切られます。<区切り子> を省略した場合、BigQuery はカンマ区切りの文字列を返します。

ソースデータの文字列がダブルクォート文字を含む場合、GROUP_CONCAT はダブルクォートを追加した文字列を返します。たとえば、a"b という文字列は "a""b" という形で返されます。ダブルクォートを追加していない文字列を返してもらいたい場合には、GROUP_CONCAT_UNQUOTED を使ってください。

例: SELECT GROUP_CONCAT(x) FROM (SELECT 'a"b' AS x), (SELECT 'cd' AS x);

GROUP_CONCAT_UNQUOTED('<文字列>' [, <区切り子>]) 複数の文字列を連結して 1 つの文字列にします。このとき、個々の値はオプションの <区切り子> 引数によって区切られます。<区切り子> を省略した場合、BigQuery はカンマ区切りの文字列を返します。

GROUP_CONCAT とは異なり、この関数は、ダブルクォート文字を含む値を返すときでもダブルクォートを追加したりはしません。たとえば、a"b という文字列は、a"b という形で返されます。

例: SELECT GROUP_CONCAT_UNQUOTED(x) FROM (SELECT 'a"b' AS x), (SELECT 'cd' AS x);

LAST(<フィールド>) 関数のスコープ内にある最後の連続値を返します。
MAX(<フィールド>) 関数のスコープ内の最大値を返します。
MIN(<フィールド>) 関数のスコープ内の最小値を返します。
NEST(<式>) 現在の集計スコープ内のすべての値を複数値フィールドにまとめます。たとえば、SELECT x, NEST(y) FROM ... GROUP BY x というクエリーは、異なる x の値ごとに 1 つの出力レコードを返し、そのなかにはクエリー入力内で x とペアになっていたすべての y をまとめた複数値フィールドが含まれます。NEST 関数を使うときには、GROUP BY 節が必須になります。

BigQuery はクエリーの結果を自動的に平坦化するので、トップレベルクエリーで NEST 関数を使っても、その結果には複数値フィールドは含まれません。NEST 関数は、同じクエリーがすぐに使う中間結果を生成するサブクエリーを呼び出すときに使ってください。

NTH(<n>, <フィールド>) 関数のスコープ内にある <n> 番目の連続値を返します。ここで、<n> は定数です。NTH 関数は数を 1 から数えるので、0 番目の連続値はありません。関数のスコープのなかの値が <n> よりも少なければ、NULL を返します。
QUANTILES(<式>[, <バケット>]) 入力式の近似変位値を比較します。計算される変位値の数は、オプションの <バケット> 引数によって加減されます。<バケット> のデフォルト値は 100 です。明示的に指定する場合、<バケット> は 2 以上でなければなりません。変位値あたりの分数誤差は、epsilon = 1 / <バケット> です。
STDDEV(<数値を返す式>) <数値を返す式> によって計算された値の標準偏差を返します。NULL 値の行は計算には含まれません。STDDEV 関数は、STDDEV_SAMP の別名です。
STDDEV_POP(<数値を返す式>) <数値を返す式> によって計算された値の母標準偏差を返します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項を参照してください。
STDDEV_SAMP(<数値を返す式>) <数値を返す式> によって計算された値の標本標準偏差を返します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項を参照してください。
SUM(<フィールド>) 関数のスコープ内にある値の合計を返します。数値データ型専用です。
TOP(<フィールド>, [<レコード数上限>], [<乗数>]) 頻度の高い <レコード数上限> 個のレコードを返します。詳細は、TOP の説明を参照してください。
VARIANCE(<数値を返す式>) <数値を返す式> によって計算された値の分散を返します。NULL 値の行は計算には含まれません。VARIANCE 関数は、VAR_SAMP の別名です。
VAR_POP(<数値を返す式>) <数値を返す式> によって計算された値の母分散を返します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項を参照してください。
VAR_SAMP(<数値を返す式>) <数値を返す式> によって計算された値の標本分散を返します。母標準偏差と標本標準偏差の詳細については、Wikipedia の標準偏差の項を参照してください。

TOP() 関数

TOP は、GROUP BY 節の代わりとして使える関数です。GROUP BY ... ORDER BY ... LIMIT .... を単純化した構文として使われ、本格的な GROUP BY ... ORDER BY ... LIMIT .... クエリーよりも一般に高速に実行されますが、近似的な結果しか返せないことがあります。TOP 関数の構文は次の通りです。

TOP(<フィールド>|<別名>[, <レコード数上限>][,<乗数>]) ... COUNT(*)

SELECT 節で TOP を使うときには、フィールドの 1 つとして COUNT(*) を入れなければなりません。

TOP() 関数を使うクエリーは、TOP フィールドと COUNT(*) 値の 2 つのフィールドしか返すことができません。

<フィールド>|<別名>
返すべきフィールドまたは別名。
<レコード数上限>
[オプション] 結果として返す値の個数の上限。デフォルトは 20。
<乗数>
何個の結果を返すかを指定するために、<レコード数上限> に掛けられる定数、式、フィールドのどれか。

SELECT TOP(word, 10), COUNT(*)
  FROM ds.Table WHERE word CONTAINS "th";

SELECT word, cnt, cnt * 2, left(word, 3)
  FROM (SELECT TOP(word, 10) AS word, COUNT(*) AS cnt
  FROM ds.Table WHERE word CONTAINS "th");

次のクエリーは、TOP()GROUP BY...ORDER BY...LIMIT を比較するものです。このクエリーは、"th" を含む単語のなかでもっとも頻度の高い 10 個と、その単語が使われている文書の数を頻度順に返します。TOP を使ったクエリーの方が、かなり先に結果を返します。

SELECT word, COUNT(*) AS cnt FROM ds.Table
  WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;

SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';

注意: TOP を使うためには、SELECT 節に COUNT(*) を入れなければなりません。

高度な例

シナリオ 説明
条件に基づいて分けられたグループ内での平均と標準偏差 次のクエリーは、オハイオ州で 2003 年に生まれた赤ん坊の体重の平均と標準偏差を、母親が喫煙者か否かに基づいてグループ分けして計算します。
SELECT
  cigarette_use
,
 
/* 平均と標準偏差を計算 */
  AVG
(weight_pounds) baby_weight,
  STDDEV
(weight_pounds) baby_weight_stdev,
  AVG
(mother_age) mother_age
FROM
 
[publicdata:samples.natality]
WHERE
  year
=2003 AND state='OH'
/* 喫煙者と非喫煙者によって */
/* 結果をグループ分けする */
GROUP BY
  cigarette_use
;
集計値を使ったクエリーの結果のフィルタリング 集計値を使ってクエリーの結果をフィルタリングする(たとえば、SUM の値によるフィルタリング)ためには、HAVING 関数を使います。HAVING は、集計を行う前に各行を操作する WHERE とは異なり、集計関数の実行結果と値を比較します。
SELECT
  state
,
 
/* is_male が True なら 'Male' を返す */
 
/* でなければ 'Female' を返す */
  IF
(is_male, 'Male', 'Female') AS sex,
 
/* カウントには 'cnt' という別名が与えられ、 */
 
/* 下の HAVING 節で使われる */
  COUNT
(*) AS cnt
FROM
 
[publicdata: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 を返します。

構文

名前 説明
+ 加算 SELECT 6 + (5 - 1);

戻り値: 10

- 減算 SELECT 6 - (4 + 1);

戻り値: 1

* 乗算 SELECT 6 * (5 - 1);

戻り値: 24

/ 除算 SELECT 6 / (2 + 2);

戻り値: 1.5

% 剰余 SELECT 6 % (2 + 2);

戻り値: 2

BIT_COUNT(<数値を返す式>) <数値を返す式> でセットされているビット数を返します SELECT BIT_COUNT(29);

戻り値: 4

このページの先頭へ

ビット単位操作関数

ビット単位操作関数は、個々のビットのレベルで操作を行い、引数は数値でなければなりません。ビット単位操作関数の詳細については、ビット単位演算を参照してください。

ここで示した以外に、集計関数の節に BIT_ANDBIT_OR BIT_XOR の 3 つのビット単位操作関数の説明があります。

構文

演算子 説明
& ビット単位の AND SELECT (1 + 3) & 1

戻り値: 0

| ビット単位の OR SELECT 24 | 12

戻り値: 28

^ ビット単位の XOR SELECT 1 ^ 0

戻り値: 1

<< ビット単位の左シフト SELECT 1 << (2 + 2)

戻り値: 16

>> ビット単位の右シフト SELECT (6 + 2) >> 2

戻り値: 2

~ ビット単位の NOT SELECT ~2

戻り値: -3

このページの先頭へ

キャスト関数

キャスト関数は、数値式のデータ型を変換します。キャスト関数は、比較関数の引数を同じデータ型にするために役立ちます。

構文

関数 説明
BOOLEAN(<数値を返す式>)
  • <数値を返す式> が 0 でなく、NULL でもなければ true を返します。
  • <数値を返す式> が 0 なら、false を返します。
  • <数値を返す式>NULL なら NULL を返します。
CAST(<式> AS <型>) <式><型> というデータ型の変数に変換します。
FLOAT(<式>) <式>double に変換して返します。<式>'45.78' のような文字列にすることができますが、数値以外のものを渡すと、FLOATNULL を返します。
HEX_STRING(<数値を返す式>) <数値を返す式> を 16 進文字列に変換して返します。
INTEGER(<式>) <式>double に変換して返します。この関数は '45' のような整数を表す文字列を受け付け、整数以外の値が渡されたときには NULL を返します。
STRING(<数値を返す式>) <数値を返す式> を文字列に変換して返します。

このページの先頭へ

比較関数

比較関数は、次の 2 つのタイプの比較に基づいて truefalse を返します。

  • 2 つの式の比較
  • 指定されたリストに含まれている、NULL である、デフォルトではないオプション値になっている、などの基準を 1 つまたは複数の式が満たしているかどうかの比較

比較関数の引数としては、数値か文字列を返す式を使えます(文字列定数は、シングル/ダブルクォートで囲まなければなりません)。式は、リテラルでも、クエリーでフェッチした値でもかまいません。比較関数は、主として WHERE 節のなかのフィルタリングの条件式として使われますが、ほかの節で使うこともできます。

構文

関数 説明
<式 1> = <式 2> 2 つの式が等しければ true を返します。
<式 1> != <式 2>
expr1 <> expr2
2 つの式が等しくなければ true を返します。
<式 1> > <式 2> <式 1><式 2> よりも大きければ true を返します。
<式 1> < <式 2> <式 1><式 2> よりも小さければ true を返します。
<式 1> >= <式 2> <式 1><式 2> 以上なら true を返します。
<式 1> <= <式 2> <式 1><式 2> 以下なら true を返します。
<式 1> BETWEEN <式 2> AND <式 3> <式 1> の値が <式 2> の値以上で, <式 3> の値以下なら true を返します。
<式> IS NULL <式>NULL なら true を返します。
<式> IN(<式 1>, <式 2>, ...) <式><式 1><式 2>、その他かっこ内の値と一致するなら true を返します。IN キーワードは、(<式> = <式 1> || <式> = <式 2> || ...) の略記法です。IN キーワードとともに使う式は、<式> と同じデータ型の定数でなければなりません。
GREATEST(<数値を返す式 1>, <数値を返す式 2>, ...) <数値を返す式> 引数のなかでもっとも大きいものを返します。すべての引数が同じデータ型の数値でなければなりません。引数のなかに 1 つでも NULL があれば、この関数は NULL を返します。

IFNULL 関数で NULL を比較に影響を及ぼさない値に書き換えれば、NULL を無視できます。次のコード例では、IFULL 関数を使って正数の比較に影響を及ぼさない -1 に NULL を変換しています。

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(<式>, <NULLのためのデフォルト値>) <式>NULL でなければ <式>、そうでなければ <NULLのためのデフォルト値> を返します。
IS_INF(<数値を返す式>) <数値を返す式> が正または負の無限大なら true を返します。
IS_NAN(<数値を返す式>) <数値を返す式> が 非数なら true を返します。
IS_EXPLICITLY_DEFINED(<式>) この関数は使わないようにすべきとされています。代わりに <式> IS NOT NULL を使ってください。
LEAST(<数値を返す式 1>, <数値を返す式 2>, ...) <数値を返す式> 引数のなかでもっとも小さいものを返します。すべての引数が同じデータ型の数値でなければなりません。引数のなかに 1 つでも NULL があれば、この関数は NULL を返します。
NVL(<式>, <NULLのためのデフォルト値>) <式>NULL でなければ <式>、そうでなければ <NULLのためのデフォルト値> を返します。NVL 関数は、IFNULL 関数の別名です。

このページの先頭へ

日付/時刻関数

日付/時刻関数は、UNIX タイムスタンプ、日付を表す文字列、TIMESTAMP 型を対象として、日付、時刻の操作をします。TIMESTAMP 型の操作方法の詳細については、TIMESTAMP の使い方を参照してください。

UNIX タイムスタンプを操作する日付/時刻関数は、UNIX 時間を操作します。日付/時刻関数は、UTC タイムゾーンに基づいて値を返します。

構文

関数 説明
CURRENT_DATE() %Y-%m-%d という人間が読める形式の文字列で現在の日付を返します。 SELECT CURRENT_DATE();

戻り値: 2013-02-01

CURRENT_TIME() %H:%M:%S という人間が読める形式の文字列で現在の時刻を返します。 SELECT CURRENT_TIME();

戻り値: 01:32:56

CURRENT_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>,<時間>,<時間の単位>) 指定された <時間><TIMESTAMP> に加えます。指定できる <時間の単位> は、YEARMONTHDAYHOURMINUTESECOND です。<時間> が負数なら、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 型データの間の日数を返します。 SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

戻り値: 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 までの整数で返します。 SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

戻り値: 275

FORMAT_UTC_USEC(<UNIX タイムスタンプ>) <UNIX タイムスタンプ>YYYY-MM-DD HH:MM:SS.uuuuuu という人間が読める形式の文字列に変換して返します。 SELECT FORMAT_UTC_USEC(1274259481071200);

戻り値: 2010-05-19 08:58:01.071200

HOUR(<TIMESTAMP>) <TIMESTAMP> が表している時刻が何時なのかを 0 から 24 までの整数で返します。 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(<式>) m 秒単位の 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(<日時文字列>) <日時文字列> を μ 秒単位の UNIX タイムスタンプに変換します。<日時文字列> は、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(<式>) 秒単位の 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タイムスタンプ>,<日時書式指定>) <日時書式指定> の指示に従い、人間が読める書式の文字列を返します。<日時書式指定> には、日時を書くときに使われる記号( /- )や、C++ の strftime 関数が受け付ける変換指定子(年月日の日を表す %d など)を入れられます。

特定の月に含まれるすべてのデータを取り出すなど、期間に基づいてデータをグループ分けしたい場合には、これよりも効率のよい UTC_USEC_TO_<期間名> 関数を使ってください。

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(<日時文字列>) 日時文字列を TIMESTAMP 型データに変換します。 SELECT TIMESTAMP("2012-10-01 01:02:03");

戻り値: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<TIMESTAMP>) <TIMESTAMP> を m 秒単位の 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_UTC_USEC は、TIMESTAMP 型のデータではなく、日時文字列を使うことを除けば同じ関数です。

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

戻り値: 1349053323000000

USEC_TO_TIMESTAMP(<式>) μ 秒単位の 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 タイムスタンプ>) μ 秒単位の UNIX タイムスタンプをシフトして、その日の 0 時 0 分 0 秒に変換します。

たとえば、UNIX タイムスタンプが 5 月 19 日 8 時 58 分を表すものなら、この関数は、5 月 19 日 0 時 0 分(午前 0 時)を表す UNIX タイムスタンプを返します。

SELECT UTC_USEC_TO_DAY(1274259481071200);

戻り値: 1274227200000000

UTC_USEC_TO_HOUR(<UNIX タイムスタンプ>) μ 秒単位の UNIX タイムスタンプをシフトして、その時間の 0 分 0 秒に変換します。

たとえば、UNIX タイムスタンプ が 8 時 58 分を表すものなら、この関数は、同じ日の 8 時 0 分を表す UNIX タイムスタンプを返します。

SELECT UTC_USEC_TO_HOUR(1274259481071200);

戻り値: 1274256000000000

UTC_USEC_TO_MONTH(<UNIX タイムスタンプ>) μ 秒単位の UNIX タイムスタンプをシフトして、その日が含まれる月の最初に変換します。

たとえば、UNIX タイムスタンプが 5 月 19 日を表すものなら、この関数は、5 月 1 日を表す UNIX タイムスタンプを返します。

SELECT UTC_USEC_TO_MONTH(1274259481071200);

戻り値: 1272672000000000

UTC_USEC_TO_WEEK(<UNIX タイムスタンプ>,<曜日を表す数字>) μ 秒単位の UNIX タイムスタンプをシフトして、その日が含まれる週の指定された日に変換します。この関数は、μ 秒単位の UNIX タイムスタンプと 0(日曜)から 6(土曜)までの <曜日を表す数字> の2つの引数を取ります。

たとえば、UNIX タイムスタンプが 2008 年 4 月 11 日を表すもので、<曜日を表す数字> が 2(火曜日)なら、この関数は、2008 年 4 月 8 日を表す UNIX タイムスタンプを返します。

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

戻り値: 1207612800000000

UTC_USEC_TO_YEAR(<UNIX タイムスタンプ>) μ 秒単位の UNIX タイムスタンプをシフトして、その日が含まれる年の最初に変換します。

たとえば、UNIX タイムスタンプが 2010 年のいつかを表すものなら、この関数は、2010-01-01 00:00 を表す 1274259481071200 という UNIX タイムスタンプを返します。

SELECT UTC_USEC_TO_YEAR(1274259481071200);

戻り値: 1262304000000000

YEAR(<timestamp>) <TIMESTAMP> が表している日が何年なのかを返します。 SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

戻り値: 2012

高度な例

シナリオ 説明
整数のタイムスタンプを、人間が読める形式の日時に変換します 次のクエリーは、Wikipedia の改訂件数が多かった上位 5 つの時刻を計算します。結果を人間が読める形式で表示するために、BigQuery の FORMAT_UTC_USEC() 関数を使っています。この関数は、入力が μ 秒単位の UNIX タイムスタンプになっていることを前提としているので、Wikipedia の POSIX 形式のタイムスタンプ(秒単位)に 1000000 を掛けて μ 秒単位に変換しています。
SELECT
 
/* タイムスタンプに 1000000 を掛けて μ 秒単位にし、 */
 
/* さらに人間が読みやすい形式に変換する */
  TOP
(FORMAT_UTC_USEC(timestamp * 1000000), 5)
    AS top_revision_time
,
  COUNT
(*) AS revision_count
FROM
 
[publicdata:samples.wikipedia];

戻り値:

+----------------------------+----------------+
|     top_revision_time      | revision_count |
+----------------------------+----------------+
| 2002-02-25 15:51:15.000000 |          20971 |
| 2002-02-25 15:43:11.000000 |          15955 |
| 2010-01-14 15:52:34.000000 |              3 |
| 2009-12-31 19:29:19.000000 |              3 |
| 2009-12-28 18:55:12.000000 |              3 |
+----------------------------+----------------+
タイムスタンプにより、検索結果をグループ分けします クエリーの結果を特定の年、月、日などに分類してまとめるときに日付/時刻関数が役立ちます。次の例では、UTC_USEC_TO_MONTH() を使って、個々の Wikipedia コントリビュータたちが毎月改訂コメントのために何字ずつ使っているかを表示します。
SELECT
  contributor_username
,
 
/* 人間が読める書式に変換された
   *月の始めのタイムスタンプを返す。
   *整形されたタイムスタンプの
   *先頭 7 文字だけが返されるように
   *文字列関数 LEFT() を使っている。*/
   */

  LEFT
(FORMAT_UTC_USEC(
    UTC_USEC_TO_MONTH
(timestamp * 1000000)),7)
    AS month
,
  SUM
(LENGTH(comment)) as total_chars_used
FROM
 
[publicdata: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 アドレスと人間が読める形式の間で相互変換を行います。

構文

関数 説明
FORMAT_IP(<整数値>) <整数値> の下位 32 ビットを人間が読める IPv4 アドレス文字列に変換します。たとえば、FORMAT_IP(1) は、'0.0.0.1' という文字列を返します。
PARSE_IP(<人間が読める IP アドレス>) IPv4 アドレスを表す文字列を符号なし整数値に変換します。たとえば、PARSE_IP('0.0.0.1') は、1 を返します。文字列が有効な IPv4 アドレスでなければ、NULL を返します。

IP アドレスは、4 バイトまたは 16 バイトのネットワークバイトオーダーのバイナリデータという形で IPv4、IPv6 アドレスを表現するパック形式をサポートしています。下記の関数は、パック形式と人間が読める形式との間の変換をサポートします。これらの関数は、IP アドレス文字列を含む文字列フィールドだけで動作します。

関数 説明
FORMAT_PACKED_IP(<パック形式の IP>) 10.1.5.23 または 2620:0:1009:1:216:36ff:feef:3f という形の人間が読める形式の IP アドレスを返します。

例:

  • FORMAT_PACKED_IP('0123456789@ABCDE')'3031:3233:3435:3637:3839:4041:4243:4445' を返します。
  • FORMAT_PACKED_IP('0123')'48.49.50.51' を返します
PARSE_PACKED_IP(<人間が読める形式の IP>) パック形式の IP アドレスを返します。入力が有効な IPv4、IPv6 アドレスでなければ、NULL を返します。

例:

  • PARSE_PACKED_IP('48.49.50.51')'0123' を返します。
  • PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')'0123456789@ABCDE' を返します。

このページの先頭へ

JSON 関数

BigQuery の JSON 関数は、JSONPath 風の式を使って JSON データ内の値を取り出します。

JSON データを格納すると、テーブルスキーマ内ですべての個別フィールドを宣言するよりも柔軟になりますが、コストが高くなる場合もあります。JSON 文字列のデータを選択するときには、文字列全体をスキャンするための料金がかかりますが、これは個々のフィールドが別々の列になっているときよりも高コストになります。また、クエリーの時点で文字列全体を解析しなければならないので、クエリーのスピードも遅くなります。しかし、変化が激しいスキーマやその場限りのスキーマでは、余分にコストをかけても JSON の柔軟性がそれ以上の意味を持つ場合があります。

構造化されたデータを操作するときには、BigQuery の正規表現関数よりも使いやすくなるので、JSON 関数を使うとよいでしょう。

関数 説明
JSON_EXTRACT(<JSON>,<JSONPath 式>) <JSONPath 式> に従って <JSON> 内の値を選択します。<JSONPath 式> は文字列定数でなければなりません。JSON 文字列形式の値を返します。 SELECT JSON_EXTRACT('{"a": 1, "b": [4, 5]}', '$.b') AS str;
JSON_EXTRACT_SCALAR(<JSON>,<JSONPath 式>) <JSONPath 式> に従って <JSON> 内の値を選択します。<JSONPath 式> は文字列定数でなければなりません。スカラーの JSON 値を返します。 SELECT JSON_EXTRACT_SCALAR('{"a": ["x", {"b":3}]}', '$.a[1].b') AS str;

このページの先頭へ

論理演算子

論理演算子は、2 値または 3 値の論理式を実行します。2 値論理は truefalse を返し、3 値論理は truefalseNULL のどれかを返します。

構文

演算子 説明
<式> AND <式>
  • 両方の式が真なら、true を返します。
  • どちらかの式、または両方の式が偽なら、false を返します。
  • どちらかの式、または両方の式が NULL なら、NULL を返します。
<式> OR <式>
  • どちらかの式、または両方の式が真なら、true を返します。
  • 両方の式が偽なら、false を返します。
  • どちらかの式、または両方の式が NULL なら、NULL を返します。
NOT <式>
  • 式が真なら、false を返します。
  • 式が偽なら、true を返します。
  • 式が NULL なら、NULL を返します。

たとえば NOT IN(expr1, expr2)IS NOT NULL のように、NOT は否定演算子としてほかの関数とともに使うことができます。

このページの先頭へ

数学関数

数学関数は、数値の引数を取り、数値の結果を返します。引数は、リテラルでもクエリーが返した数値でもかまいません。数学関数の評価結果が未定義になった場合、戻り値は NULL になります。

構文

関数 説明
ABS(<数値を返す式>) 引数の絶対値を返します。
ACOS(<数値を返す式>) 引数の逆余弦を返します。
ACOSH(<数値を返す式>) 引数の逆双曲線余弦を返します。
ASIN(<数値を返す式>) 引数の逆正弦を返します。
ASINH(<数値を返す式>) 引数の逆双曲線正弦を返します。
ATAN(<数値を返す式>) 引数の逆正接を返します。
ATANH(<数値を返す式>) 引数の逆双曲線正接を返します。
ATAN2(<数値を返す式 1>, <数値を返す式 2>) 2 つの引数の 4 象限逆正接を返します。
CEIL(<数値を返す式>) 引数よりも大きく、かつ引数にもっとも近い整数を返します。
COS(<数値を返す式>) 引数の余弦を返します。
COSH(<数値を返す式>) 引数の双曲線余弦を返します。
DEGREES(<数値を返す式>) <数値を返す式> が表すラジアンを度数に変換して返します。
FLOOR(<数値を返す式>) 引数よりも小さく、かつ引数にもっとも近い整数を返します。
LN(<数値を返す式>)
LOG(<数値を返す式>)
引数の自然対数を返します。
LOG2(<数値を返す式>) 引数の 2 を底とする対数を返します。
LOG10(<数値を返す式>) 引数の 10 を底とする対数を返します。
PI() 定数 π を返します。PI() 関数は、関数であることを示すためにかっこを必要としますが、引数は取りません。数学、算術演算関数では、PI() は定数のように使えます。
POW(<数値を返す式 1>, <数値を返す式 2>) <数値を返す式 1><数値を返す式 2> 乗を返します。
RADIANS(<数値を返す式>) <数値を返す式> が表す度数をラジアンに変換して返します( π ラジアンが 180 度です)。
RAND([<32 ビット整数の種>]) 0.0 <= value < 1.0 の範囲のランダムな浮動小数点数値を返します。LIMIT を使わない限り、個々の <32 ビット整数の種> は、同じクエリーからいつも同じ乱数シーケンスを生成します。<32 ビット整数の種> を指定しなければ、BigQuery は種として現在のタイムスタンプを使います。
ROUND(<数値を返す式> [, <桁数>]) 引数を四捨五入してもっとも近い整数( <桁数> を指定した場合は、その桁数までの整数)を返します。
SIN(<数値を返す式>) 引数の正弦を返します。
SINH(<数値を返す式>) 引数の双曲線正弦を返します。
SQRT(<数値を返す式>) 引数の平方根を返します。
TAN(<数値を返す式>) 引数の正接を返します。
TANH(<数値を返す式>) 引数の双曲線正接を返します。

高度な例

シナリオ 説明
境界ボックスのクエリー 次のクエリーは、サンフランシスコ(北緯 37.46 度、西経 122.50 度)を中心とする境界矩形内の点のコレクションを返します。
SELECT
  year
, month,
  AVG
(mean_temp) avg_temp,
  MIN
(min_temperature) min_temp,
  MAX
(max_temperature) max_temp
FROM
 
[weather_geo.table]
WHERE
 
/* 2 つの緯度、経度の間にある */
 
/* 値を返す */
  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 の数学関数を使っています。

地球は完全な平面ではなく、緯度と経度は極で収束するため、このクエリーは多くのタイプのデータで役に立つ近似値を返します。

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 ライブラリを使って正規表現をサポートしています。正規表現の構文については、re2 のドキュメントを参照してください。

正規表現はグローバルマッチだということに注意してください。単語の冒頭からマッチを開始したければ、^ 文字を使わなければなりません。

構文

関数 説明
REGEXP_MATCH('<文字列>', '<正規表現>') str が正規表現にマッチするなら true を返します。正規表現を使わない文字列のマッチングには、REGEXP_MATCH ではなく、CONTAINS を使ってください。 SELECT
   word,
   COUNT(word) AS count
FROM
   publicdata: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('<文字列>', '<正規表現>') 正規表現内のキャプチャグループにマッチする <文字列> の部分を返します。 SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   publicdata:samples.shakespeare
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

戻り値:

fragment
null
Al'ce
As'es
REGEXP_REPLACE('<元の文字列>', '<正規表現>', '<置換文字列>') <元の文字列> のなかで <正規表現> にマッチするすべての部分文字列を <置換文字列> に置き換えた文字列を返します。たとえば、REGEXP_REPLACE ('Hello', 'lo', 'p')Help を返します。

高度な例

シナリオ 説明
正規表現による結果セットのフィルタリング BigQuery の正規表現関数は、WHERE 節で使えば結果セットをフィルタリングすることができ、SELECT 節で使えば結果を表示できます。次の例は、1 つのクエリーで、この 2 つのユースケースを組み合わせて使っています。
SELECT
 
/* タイトル内のアンダースコアをスペースに置換 */
  REGEXP_REPLACE
(title, r'_', ' ') AS regexp_title, views
  FROM
   
(SELECT title, SUM(views) as views
    FROM
[bigquery-samples:wikimedia_pageviews.201201]
  WHERE
    NOT title CONTAINS
':'
    AND wikimedia_project
='wp'
    AND language
='en'
   
/* 先頭が 'G'、末尾が 'e' で、2 個の 'o' */
   
/* を含むタイトルにマッチ */
    AND REGEXP_MATCH
(title, r'^G.*o.*o.*e$')
  GROUP BY
    title
  ORDER BY
    views DESC
  LIMIT
100)
整数や浮動小数点数に対して正規表現を使う BigQuery の正規表現関数は文字列データだけを対象とするものですが、整数や浮動小数点数でも STRING() 関数を使えば文字列形式にキャストできます。この例では、STRING() 関数を使って整数値の corpus_date を文字列に変換し、それを REGEXP_REPLACE で書き換えます。
SELECT
  corpus_date
,
 
/* corpus_date を文字列にキャスト */
  REGEXP_REPLACE
(STRING(corpus_date),
   
'^16',
   
'Written in the sixteen hundreds, in the year \''
   
) AS date_string
FROM
[publicdata:samples.shakespeare]
/* corpus_date を文字列にキャスト */
/* 先頭が '16' のデータにマッチ */
WHERE
  REGEXP_MATCH
(STRING(corpus_date), '^16')
GROUP BY
  corpus_date
, date_string
ORDER BY
  date_string DESC
LIMIT
5;

このページの先頭へ

文字列関数

文字列関数は、文字列データを操作します。文字列定数はシングルクォートかダブルクォートで囲まなければなりません。文字列関数は、デフォルトで大文字と小文字を区別し、LATIN-1 エンコーディングだけを使っていなければなりません(必要なら UTF-8 エンコーディングを使ってください)。クエリーの末尾に IGNORE CASE を追加すれば、大文字と小文字を区別しないマッチングを実行できます。IGNORE CASE を使えるのは LATIN-1 文字列だけです。

これらの関数では、ワイルドカードはサポートされていません。正規表現機能が必要なら、正規表現関数を使ってください。

構文

関数 説明
CONCAT('<文字列 1>', '<文字列 2>', '...')
<文字列 1> + <文字列 2> + ...
複数の文字列を連結したものを返します。ただし、値のなかに NULL が含まれている場合には NULL を返します。

例: <文字列 1>Java<文字列 2>Script なら、CONCATJavaScript を返します。

<式> CONTAINS '<文字列>' <式> が指定された文字列引数を含んでいる場合には true を返します。比較では大文字と小文字を区別します。
LEFT('<文字列>', <数値を返す式>) <文字列> の左から <数値を返す式> の長さの文字列を返します。数値が <文字列> よりも大きければ、文字列全体が返されます。

例: LEFT('seattle', 3)sea を返します。

LENGTH('<文字列>') <文字列> の長さを数値で返します。

例: <文字列>'123456' なら、LENGTH6 を返します。

LOWER('<文字列>') <文字列> のすべての文字を小文字にして返します。動作するのは LATIN-1 文字だけです。
LPAD('<文字列 1>', <数値を返す式>, '<文字列 2>') 長さが <数値を返す式> になるまで、<文字列 1> の左に <文字列 2> を繰り返し追加します。

例: LPAD('1', 7, '?')??????1 を返します。

RIGHT('<文字列>', <数値を返す式>) <文字列> の右から <数値を返す式> の長さの文字列を返します。数値が <文字列> よりも大きければ、文字列全体が返されます。

例: RIGHT('kirkland', 4)land を返します。

RPAD('<文字列 1>', <数値を返す式>, '<文字列 2>') 長さが <数値を返す式> になるまで、<文字列 1> の右に <文字列 2> を繰り返し追加します。

例: RPAD('1', 7, '?')1?????? を返します。

SPLIT('<文字列>' [, '<区切り子>']) 複数の文字列という形で一連の部分文字列を返します。<区切り子> が指定されている場合、<区切り子> を使って <文字列> を部分文字列に分割します。
SUBSTR('<文字列>', <インデックス> [, <長さの上限>]) <インデックス> を先頭とする <文字列> の部分文字列を返します。オプションの <長さの上限> 引数を指定すると、返される部分文字列の長さがその値以下に制限されます。インデックスは 1 から数えるので、文字列の先頭文字の位置は 0 ではなく 1 になります。<インデックス>5 なら、部分文字列の先頭は <文字列> の 5 文字目になります。<インデックス>-4 なら、部分文字列の先頭は <文字列> の右から 4 文字目になります。

例: SUBSTR('awesome', -4, 4) は、some という部分文字列を返します。

UPPER('<文字列>') <文字列> のすべての文字を大文字にして返します。動作するのは LATIN-1 文字だけです。

文字列のなかの特殊文字のエスケープ

特殊文字をエスケープするには、次の方法のどれかを使います。

  • '\xDD' 記法。'\x' の後ろに文字を表す 2 桁の 16 進数を続ける方法
  • スラッシュ、シングルクォート、ダブルクォートの前にエスケープ用のスラッシュを追加する方法
  • その他の文字に対する C スタイルのシーケンス( '\a'、'\b'、'\f'、'\n'、'\r'、'\t'、'\v'

エスケープの例をいくつか示しておきます。

'これはスペース: \x20'
'この文字列のなかには\'シングルクォート\'が含まれています'
'第1行\n第2行'
"ダブルクォートも使えます"
'\070' -> エラー: 8 進エスケープはサポートされていません

このページの先頭へ

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

テーブルワイルドカード関数は、コスト的に効率よく特定のテーブルセットからデータを検索することができます。テーブルワイルドカード関数を使うと、BigQuery は、ワイルドカードにマッチするテーブルにだけアクセス、課金します。テーブルワイルドカード関数は、クエリーの FROM 節で指定されます。

クエリー内でテーブルワイルドカード関数を使う場合、以下のいくつかの例に示すように、関数はかっこで囲まれていなければなりません。

関数 説明
TABLE_DATE_RANGE(<プレフィックス>, <TIMESTAMP 1>, <TIMESTAMP 2>) <TIMESTAMP 1><TIMESTAMP 2> の間の期間に重なりあう日次テーブルだけにクエリーを送ります。

テーブル名は、<プレフィックス><日> という形式になっていなければなりません。ここで、<日> は、YYYYMMDD という形式になっていなければなりません。

TIMESTAMP 型の引数を生成するために、日付/時間関数を使うことができます。たとえば、次のようなものです。

  • TIMESTAMP('2012-10-01 02:03:04')
  • DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
例: 2 つの日の間のテーブルを取得する

このサンプルは、次のようなテーブルがあることを前提としています。

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327
SELECT 
  name
FROM 
  (TABLE_DATE_RANGE(mydata.people, 
                    TIMESTAMP('2014-03-25'), 
                    TIMESTAMP('2014-03-27'))) 
WHERE 
  age >= 35

以下の表にマッチします。

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327

例: 今日までの 2 日間のテーブルを取得する

このサンプルは、次のようなテーブルがあることを前提としています。

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
SELECT
  name
FROM 
  (TABLE_DATE_RANGE(mydata.people,
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE 
  age >= 35

以下の表にマッチします。

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
TABLE_DATE_RANGE_STRICT(<プレフィックス>, <TIMESTAMP 1>, <TIMESTAMP 2>) この関数は TABLE_DATE_RANGE とほとんど同じですが、シーケンスのなかで欠けている日次テーブルがあるときに、TABLE_DATE_RANGE_STRICT は処理を失敗させ、Not Found: Table <table_name> エラーを返す点だけが異なります。 例: 欠けているテーブルがあるときのエラー

このサンプルは、次のようなテーブルがあることを前提としています。

  • people20140325
  • people20140327
SELECT 
  name
FROM 
  (TABLE_DATE_RANGE_STRICT(people, 
                    TIMESTAMP('2014-03-25'), 
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

上の例は、people20130326 テーブルに対して Not Found エラーを返します。

TABLE_QUERY(<データセット>, <式>) 名前が指定された <式> を満たすテーブルにクエリーを送ります。<式> パラメータは、たとえば 'length(table_id) < 3' のように文字列を使ったもので、評価すべき式を含むものでなければなりません。 例: 名前に "oo" が含まれ、長さが 4 字以上のテーブルにマッチする

このサンプルは、次のようなテーブルがあることを前提としています。

  • mydata.boo
  • mydata.fork
  • mydata.ooze
  • mydata.spoon
SELECT 
  speed 
FROM (TABLE_QUERY(mydata,
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

次のテーブルにマッチします。

  • mydata.ooze
  • mydata.spoon

例: 名前の先頭が "boo" で、その後ろに 3 個から 5 個の数字の連続が含まれているテーブルにマッチする

このサンプルは、次のようなテーブルがあることを前提としています。

  • mydata.book4
  • mydata.book418
  • mydata.boom12345
  • mydata.boom123456789
  • mydata.taboo999
SELECT 
  speed 
FROM 
  (TABLE_QUERY(mydata,
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")'))

次のテーブルにマッチします。

  • mydata.book418
  • mydata.boom12345

このページの先頭へ

URL 関数

構文

関数 説明
HOST('<URL 文字列>') URL を渡すと、ホスト名を文字列の形で返します。

例: HOST('http://www.google.com:80/index.html') は、'www.google.com' を返します。

DOMAIN('<URL 文字列>') URL を渡すと、ドメイン名を文字列の形で返します。

例: DOMAIN('http://www.google.com:80/index.html') は、'google.com' を返します。

TLD('<URL 文字列>') URL を渡すと、URL のなかのトップレベルドメインと国別ドメインを加えたもの(ある場合)を文字列の形で返します。

例: TLD('http://www.google.com:80/index.html')'.com'TLD('http://www.google.co.uk:80/index.html')'.co.uk' を返します。

高度な例

シナリオ 説明
URL データからドメイン名を読み取る このクエリーは、DOMAIN() 関数を使って、GitHub でリポジトリホームページとして掲載されているもののなかでもっとも多いドメインを返します。HAVING を使って、DOMAIN() 関数の結果に基づきレコードをフィルタリングしていることに注意してください。これは、URL データから参照者情報を判断する便利な関数です。
SELECT
  DOMAIN
(repository_homepage) AS user_domain,
  COUNT
(*) AS activity_count
FROM
 
[publicdata: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 リストに含まれていないトップ TLD を表示します。

SELECT
  TLD
(repository_homepage) AS user_tld,
  COUNT
(*) AS activity_count
FROM
 
[publicdata:samples.github_timeline]
GROUP BY
  user_tld
HAVING
 
/* NULL でなく、主要 TLD リストにも含まれて
 
/* いない TLD だけを対象とする */
  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 (
     
[PARTITION BY <式>]
     
[ORDER BY <式>]
     
[ROWS <式> | RANGE <式>]
     
)

PARTITION BY は常にオプションです。ORDER BY はオプションになる場合もありますが、RANK()DENSE_RANK() では必須になります。

JOIN EACHGROUP EACH BY 節はウィンドウ関数の出力に対して使うことはできません。ウィンドウ関数を使うときに大きな結果セットを生成したい場合には、PARTITION BY を使わなければなりません。

構文

関数 説明
CUME_DIST() <現在の行以前の行の数> / <すべての行> という式で計算された一連の値のなかでの特定の値の累積分布を示す double を返します。

このウィンドウ関数は、OVER 節内に ORDER BY 節を入れることを必須とします。

SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [publicdata: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 節を入れることを必須とします。

SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [publicdata: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

LAG(<式>, <オフセット>, <デフォルト値>) 現在の行よりも <オフセット> 行だけ手前の行に対する <式> の値を返します。行がなければ、<デフォルト値> を返します。 SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [publicdata: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

LEAD(<式>, <オフセット>, <デフォルト値>) 現在の行よりも <オフセット> 行だけ後ろの行に対する <式> の値を返します。行がなければ、<デフォルト値> を返します。 SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [publicdata: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(<式>, <n>) ウィンドウフレームの位置 <n><式> の値を返します。<n> は、1 から始まるインデックスです。
NTILE(<バケット数>) 行シーケンスを <バケット数> 個のバケットに分割し、各行に対応する整数のバケット番号を与えます。NTILE 関数は、できる限り等しくバケット番号を割り振り、1 から<バケット数> までの値を返します。 SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [publicdata: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 節を入れることを必須とします。

SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [publicdata: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(<パーセンタイル>) ORDER BY 節で順番に並べたあと、グループ内の値の間で線形補間によって得られた値を返します。

<パーセンタイル> は 0 と 1 の間の値でなければなりません。

このウィンドウ関数は、OVER 節内に ORDER BY 節を入れることを必須とします。

SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [publicdata: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(<パーセンタイル>) 累積分布が <パーセンタイル> 以上でもっとも小さい値を返します。

<パーセンタイル> は 0 と 1 の間の値でなければなりません。

このウィンドウ関数は、OVER 節内に ORDER BY 節を入れることを必須とします。

SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [publicdata: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 節を入れることを必須とします。

SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [publicdata: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(<列>) 値の合計に対する個々の値の割合を表す 0 から 1 までの double を返します。 SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [publicdata: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 です。 SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [publicdata: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 <WHEN 式 1> THEN <THEN 式 1>
     WHEN <WHEN 式 2> THEN <THEN 式 2> ...
     ELSE <ELSE 式> END
クエリーのなかで複数の式からどれかを選べるようにします。<WHEN 式> は論理式でなければなりません。また、THENELSE 節のなかのすべての式は互換型でなければなりません。
EVERY(<条件式>) すべての入力に対して <条件式> が真なら true を返します。反復値フィールドを対象とするクエリーで OMIT IF 節と併用すると、とても役に立ちます。
HASH(<式>) CityHash ライブラリが定義する方法で <式> のバイトから 64 ビット符号付きハッシュ値を計算して返します。文字列、整数のあらゆる式がサポートされます。また、文字列の場合、IGNORE CASE を尊重し、大文字と小文字で差のない値を返します。
IF(<条件式>, <真のときの戻り値>, <偽のときの戻り値>) <条件式> が真か偽かに基づいて、<真のときの戻り値><偽のときの戻り値> を返します。戻り値はリテラルでも、フィールドから作った値でもかまいませんが、同じデータ型でなければなりません。フィールドから値を作る場合、そのフィールドは SELECT 節に含まれていなくてもかまいません。
OMIT <RECORD|フィールド> IF <条件式> WHERE 節とは対照的に、要素は条件を満たすと取り除かれます。さらに重要なのは、WHERE 節はトップレベルレコードだけをフィルタリングするのに対し、OMIT IF 節は複数値フィールドの個別の要素を取り除くことができ、条件式には取り除かれる要素の下にあるフィールドを対象とする集計関数を含められることです。フィルタリングされるフィールドよりも下のスコープの参照される複数値フィールドは、集計関数内に含まれていなければなりません。
POSITION(<フィールド>) 一連の複数値フィールドのなかでの <フィールド> の位置を、1 を先頭とする数値で返します。
SOME(<条件式>) 少なくとも 1 つの入力に対して <条件式> が真なら true を返します。反復値フィールドを対象とするクエリーで OMIT IF 節と併用するととても役に立ちます。

高度な例

シナリオ 説明
条件式を使って結果をバケットに分類する 次のクエリーは、CASE/WHEN ブロックで州のリストを処理して、結果を "region" に分類します。州が WHEN 文のどれかにオプションとして含まれていない場合、デフォルトで "region" は "None" になります。
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
     publicdata
: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 |
+--------+--------------------+--------------------+-------+------+
ピボットテーブルのシミュレーション 条件文を使ってサブクエリーの結果を行、列にまとめていきます。次の例では、もっともよく改訂されている Wikipedia 記事で、タイトルの先頭が 'Google' になっているものを取り出し、条件が合った列に改訂数を表示します。
SELECT
  page_title
,
 
/* 条件に合う列に */
 
/* 改訂数を表示する */
  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
 
/* タイトルの先頭が 'Google' で続きがある Wikipedia 記事のなかで */
 
/* もっともよく改訂されているものを選ぶサブクエリー */
 
(SELECT
    TOP
(title, 5) as page_title,
    COUNT
(*) as total
   FROM
     
[publicdata: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 が負数を返すことがあり、負数に対して剰余演算子を使うと負の値が返されるからです。

SELECT
  title
,
  HASH
(title) AS hash_value,
  IF
(ABS(HASH(title)) % 2 == 1, 'True', 'False')
    AS included_in_sample
FROM
 
[publicdata:samples.wikipedia]
WHERE
  wp_namespace
= 0
LIMIT
5;

このページの先頭へ