BigQuery StandardSQL Tips
BigQuery StandardSQL で便利な書き方を紹介します。
BigQuery は StandardSQL に対応していますが、同じ SQL でもプラットフォームごとに独特の方言があります。
StandardSQL と言えども、プラットフォームによって文法に細かい差異があるということです。
本記事の内容はすべて BigQuery における StandardSQL の書き方を紹介しています。
つまり BigQuery 以外のプラットフォームで本記事の SQL を書いても意図したとおりに動作しない可能性があるのでご注意ください。
対象読者
基本的な SQL の書き方を知っていて、StandardSQL を書いたことがある人を想定しています。
BigQuery 自体の使い方に関する説明は省略します。
BigQuery の Web コンソールで StandardSQL を有効にする
BigQuery の Web コンソールはデフォルトで LegacySQL に対応しています。
StandardSQL を使うためには StandardSQL を有効にする必要があります。
StandardSQL を有効にする方法は２つあります。
- ヘッダーコメント
SQL 文の1行目に#standardSQLと書きます - オプション設定
Show Optionsでオプション画面を開きUse Legacy SQLのオプションを OFF にします(チェックを外す)。
CLI や API から SQL を実行する場合でも同様に Legacy SQL のオプションをオフにします。
以下ではすべて StandardSQL が有効になっているものとします。
Tips
日本時間で時刻を取得する
- 現在時刻を取得する
SELECT FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", CURRENT_TIMESTAMP(), "Asia/Tokyo") AS TIME
2017-12-01 18:57:21
複数のテーブルを縦につなぐ
- テーブル名の suffix が日付になっている
SELECT
*
FROM `table_prefix_*`
_TABLE_SUFFIX BETWEEN 20171201 AND 20171226
- 名前の関連性がないけどとにかくつなぎたい
SELECT * FROM table_a
UNION ALL
SELECT * FROM table_b
UNION ALL
SELECT * FROM table_c
疑似テーブルを作る
- SQL だけでテーブルを生成する
WITH
generated AS (
SELECT 1 AS num, "hoge" AS name
UNION ALL
SELECT 2 AS num, "fuga" AS name
UNION ALL
SELECT 3 AS num, "piyo" AS name
)
SELECT * FROM generated
generated
| num | name |
|---|---|
| 1 | hoge |
| 2 | fuga |
| 3 | piyo |
NULL を埋める
- field1 が NULL の可能性がある場合に 0 で補完する
SELECT IFNULL(field1, 0) AS field1 FROM original
行番号をつける(基礎編)
- テーブルの１番左列に行番号を添付したい場合(1から連番)
SELECT ROW_NUMBER() OVER() AS row_number, * FROM original
| row_number | field1 |
|---|---|
| 1 | hoge |
| 2 | fuga |
| 3 | piyo |
- テーブルの１番左列に行番号を添付したい場合(0から連番)
SELECT ROW_NUMBER() OVER() -1 AS row_number, * FROM original
| row_number | field1 |
|---|---|
| 0 | hoge |
| 1 | fuga |
| 2 | piyo |
行番号をつける(応用編)
- field1 の order を変えた後に上から順に番号を添付したい
WITH
modified AS (
SELECT * FROM original_table ORDER BY field1 DESC
)
SELECT ROW_NUMBER() OVER() AS row_number, * FROM modified
| row_number | field1 |
|---|---|
| 1 | piyo |
| 2 | hoge |
| 3 | fuga |
URL をパースする
- URL を要素に分解する
https://www.example.com/foo/bar?key=val#name
WITH
urls AS (
SELECT 'https://www.example.com/foo/bar?key=val#name' AS url
)
select
regexp_extract(url, '^([^:]+)') AS protocol -- プロトコル
, regexp_extract(url, '//([^/]+)') AS domain -- ドメイン
, regexp_extract(url, '//[^/]+([^?#]+)') AS path -- パス
, split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)] AS path1 -- １つ目のパス
, split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(3)] AS path2 -- ２つ目のパス
, regexp_extract(url, '\\?([^#]+)') AS query -- クエリ
, regexp_extract(url, '\\#(.+)') AS name -- アンカー
FROM urls
| protocol | domain | path | path1 | path2 | query | name |
|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|
| https | www.example.com | /foo/bar | foo | bar | key=val | name |
JSON を展開する
- JSON の値を取得する (JSON_EXTRACT_SCALAR)
- JSON の一部を取得する (JSON_EXTRACT)
select
JSON_EXTRACT('{"foo":{"bar":1}}', '$.foo') as foo
, JSON_EXTRACT_SCALAR('{"foo":{"bar":1}}', '$.foo.bar') as foobar
, JSON_EXTRACT_SCALAR(JSON_EXTRACT('{"foo":[{"bar":2}]}', '$.foo[0]'), '$.bar') as foobar2
| foo | foobar | foobar2 |
|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|
| {"bar":1} | 1 | 2 |