BigQueryでSQLデビュー。

👉よりオフィシャルっぽい日本語版ドキュメントがあります。

---

• 完全にkey-value脳。
• SQLiteのファイルはdumpするときしか開かない。
• Datastoreしか使ってない。
• SQLは情報処理系の資格試験でしか見たことがない。
• 物心ついた時から、何もかもmemcachedに入れるもんだ、と思ってた。
• ORMが吐くSQLはクソである、という言い伝えを聞いたことがある。
• そもそもSQLで何ができるのかよく知らない。
• RDBなオペレーションは、オラクルマスターにお願いしてた。

さあ、SQLを始めよう。
※以下、翻訳の一部抜粋です。

BigQuery Query Reference

BigQuery内のクエリは、標準のSQL SELECTステートメントのバリエーションを使用して書かれています。 BigQuery内には、COUNT、算術式、文字列関数などの多様な機能をサポートしています。このドキュメントでは、BigQuery内のクエリ構文と機能について詳しく説明します。

Query syntax

すべてのBigQuery内のクエリは、このフォームのSELECTステートメントです:

SELECT expr1 [[AS] alias1] [, expr2 [[AS] alias2], ...]
    [agg_function(expr3) WITHIN expr4]
    [FROM [(FLATTEN(table_name1|(subselect1)] [, table_name2|(subselect2), ...)]
    [[INNER|LEFT OUTER|CROSS] JOIN [EACH] table_2|(subselect2) [[AS] tablealias2]
      ON join_condition_1 [... AND join_condition_N ...]]+
    [WHERE condition]
    [GROUP [EACH] BY field1|alias1 [, field2|alias2, ...]]
    [HAVING condition]
    [ORDER BY field1|alias1 [DESC|ASC] [, field2|alias2 [DESC|ASC], ...]]
    [LIMIT n]
    ;

Note:
キーワードは大文字と小文字を区別しません。この文書では、SELECTのようなキーワードは、説明のためにキャピタライズされています。

SELECT句

SELECT expr1 [[AS] alias1], expr2 [[AS] alias2], ...

SELECT句は、クエリによって返される値のセットを指定します。 SELECT句の式（式1、など）は、フィールド名、リテラル、またはフィールドやリテラルを操作する機能表現することができます。式は、カンマで区切る必要があります。

SELECT句は、フィールド、リテラル、または機能的発現のためのエイリアスを定義するASセクションをサポートしています。BY句のみGROUP BYでエイリアスとORDERを参照することができます。

Notes:
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; // INVALID! corpusによるGROUPではありませんでした

Example
この例では、ソートの句のエイリアスを定義しています:

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

WITHIN句

次の構文で、WITHIN句をSELECTフィールドとして使用できます:

SELECT expr1 [WITHIN RECORD|node] [[AS] alias1], expr2 [WITHIN RECORD|node] [[AS] alias2], ...

WITHINキーワードは、特に子供やレコードおよびネストされたフィールド内の繰り返しフィールドを越えて集約する集約関数で動作します。

WITHINキーワードを指定する場合、集約したい範囲を指定する必要があります:

[翻訳中...]

FROM句

...
FROM
  [project_name:]datasetId.tableId |
  (subselect_clause) |
  table wildcard function
[[AS] alias]
...

[翻訳中...]

Example

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

Note:
他の多くのSQLベースのシステムとは異なり、BigQueryではテーブルのUNIONを指示するためにコンマ構文を使用します。（JOINではありません）
次のように、互換性のあるスキーマを持つ複数のテーブルをまたいだクエリを実行できます:

// 数日間にわたる不審なアクティビティを探す
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を実行するクエリは、同じサイズのデータ量を持つ単一のテーブルに対して実行するクエリよりも、実行速度が遅くなります。
テーブルが追加されるごとに、最大50ミリ秒の性能差が発生します。
UNIONできる最大テーブル数は、1,000です。

FLATTEN句

JOIN句

SELECT field_1 [..., field_n] FROM
   table_1 [[AS] alias_1]
 [[INNER|LEFT OUTER|CROSS] JOIN [EACH]
   table_2 [[AS] alias_2]
  [ON join_condition_1 [... AND join_condition_n]]
 ]+

BigQueryは、SELECT構文ごとに複数のJOIN操作をサポートします。

[翻訳中...]

JOIN types

BigQueyrは、INNER、LEFT OUTERとCROSS JOIN操作をサポートします。デフォルトは、INNERです。
CROSS JOIN句には、ON句を含められません。
CROSS JOIN操作は、大量のデータを返したり非効率なクエリを伴うと、遅くなるかもしれません。
代わりに、できる限り通常のJOINを使います。

EACH modifier

通常のJOIN操作では、右辺テーブルが圧縮サイズで8MB未満でなければなりません。
EACH修飾子は、2つの大きなテーブルを参照するJOINを実行する際に、クエリ実行エンジンへのヒントとなります。
EACH修飾子は、CROSS JOIN句では使えません。

最高のパフォーマンスを得るには、できる限りEACH修飾子を除いたJOINを使います。
JOIN操作に対してテーブルサイズが大き過ぎる場合に、JOIN EACHを使います。

WHERE句

... WHERE condition ...

WHERE句は選択条件を指示するため、たびたびクエリの述語と呼ばれます。

[翻訳中...]

Note:
集計関数は、WHERE句で使用できません。
集計フィールドを使う必要がある場合には、HAVINGを使います。

[翻訳中...]

GROUP BY句

... GROUP [EACH] BY field1|alias1, field2|alias2 ...

GROUP BY句は、指定されたフィールドに同じ値を持つ行をグループ化できます。
グループ化後に、グループのそれぞれに集計機能を実行できます。
グループ化は、任意の選択の後、またはSELECT句の中の集合で実行されます。

例えば、f1とf2のSUMのそれぞれのために、同じ値を持つ行をグループ化できます。

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

このタイプの集合は group aggregation と呼ばれます。
scoped aggregationとは異なり、group aggregationは従来のリレーショナル・データベースでサポートされています。

データセットにグループキーの別個の値が多数含まれている場合、EACHパラメータを使用できます。
クエリのパフォーマンスを向上しなければならない時は、EACHパラメータを除いたGROUP BYを使用してください。

Notes:

• SELECT句の非集計フィールドは、GROUP BY句にリストされている必要があります:

  SELECT f1, COUNT(f2), f3 FROM ds.Table GROUP BY f1; // INVALID! GROUP BY句にf3がありません

• 集計値はグループ化できません:

  SELECT f1, COUNT(f2) as count FROM ds.Table GROUP BY count; // INVALID! 集計によるグループ化はできません

• floatとdouble値によるグループ化は、これらのタイプのための平等関数が明確に定義されていないために、サポートされていません。

• このシステムは対話式であるため、多数のグループを生成するクエリは失敗することがあります。
  代わりにGROUP BYのTOP機能で、この問題を解決できるかもしれません。

HAVING句

... HAVING condition ...

HAVING句は、クエリの選択条件を指示します。
真理値・ANDとOR句を、必要に応じて(...)（括弧）でグルービングし、複数の条件として結合できます。
WHEREと似ていますが、HAVINGは集計フィールドをサポートします。

[翻訳中...]

Example

SELECT keyword, SUM(clicks)/SUM(impressions) AS 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句

LIMIT句

... LIMIT num_rows ...

LIMIT句は、返却する結果セットの行数を制限します。
例えば、次のクエリは結果を5行だけ返します。

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

クエリは非常に多数の行に渡って動作することができるので、代表的なデータで十分な場合には、単純なクエリが長時間に渡って実行されるのを避けために、LIMITは良い方法です。

Notes:

• LIMIT句は、要件を満たしたら結果を返し、処理を停止します。 いくつかのクエリの処理時間を減らすことができますが、COUNT句やORDER BY句のような集計関数を指定する場合に完全な結果セットを得るには、集計後にLIMITされなければなりません。
• LIMIT句は、完全な結果セットの処理を省略した場合、異なる結果を返すことがあります。 クエリは並列に処理されるため、並列に実行されるジョブが完了する順序は保証されていません。
• LIMIT句は、関数を含められません。数値のみを指定できます。

Supported functions and operators

Aggregate関数

Arithmeticオペレーター

Bitwise関数

Casting関数

Comparison関数

Date and time関数

IP関数

JSON関数

Logicalオペレーター

Mathematical関数

Regular expression関数

BigQueryは、re2ライブラリを使用して、正規表現のサポートを提供します。
正規表現の構文については、re2のマニュアルを参照してください。
正規表現は、グローバルマッチであることに注意してください。
文字列の先頭にマッチするには、^文字を使用する必要があります。

Syntax

REGEXP_MATCH

関数:

REGEXP_MATCH('str', 'reg_exp')

説明:

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

関数:

REGEXP_EXTRACT('str', 'reg_exp')

説明:
正規表現内のキャプチャグループに一致するstrの一部を返します。

例:

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

[翻訳中...]

String関数

Table wildcard関数

URL関数

Window関数

Window関数は、結果セットの特定パーティションまたはwindow上で計算できます。

[翻訳中...]

Syntax

NTH_VALUE

関数:

NTH_VALUE(<expr>, <n>)

説明:

windowフレーム内の<expr>の<n>に位置する値を返します。<n>は、1から始まるインデックスです。

Other関数