BigQuery StandardSQL Tips

# 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 を有効にする方法は２つあります。

1. ヘッダーコメント  
SQL 文の1行目に `#standardSQL` と書きます
2. オプション設定  
`Show Options` でオプション画面を開き `Use Legacy SQL` のオプションを OFF にします(チェックを外す)。

CLI や API から SQL を実行する場合でも同様に Legacy SQL のオプションをオフにします。

以下ではすべて StandardSQL が有効になっているものとします。

## Tips

### 日本時間で時刻を取得する

- 現在時刻を取得する

```sql
SELECT FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", CURRENT_TIMESTAMP(), "Asia/Tokyo") AS TIME
```
```
2017-12-01 18:57:21
```

### 複数のテーブルを縦につなぐ

- テーブル名の suffix が日付になっている

```sql
SELECT 
*
FROM `table_prefix_*`
_TABLE_SUFFIX BETWEEN 20171201 AND 20171226
```

- 名前の関連性がないけどとにかくつなぎたい

```sql
SELECT * FROM table_a
UNION ALL
SELECT * FROM table_b
UNION ALL
SELECT * FROM table_c
```

### 疑似テーブルを作る

- SQL だけでテーブルを生成する

```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 で補完する

```sql
SELECT IFNULL(field1, 0) AS field1 FROM original
```

### 行番号をつける(基礎編)

- テーブルの１番左列に行番号を添付したい場合(1から連番)

```sql
SELECT ROW_NUMBER() OVER() AS row_number, * FROM original
```

| row_number | field1 |
|:---:|:----:|
| 1 | hoge |
| 2 | fuga |
| 3 | piyo |

- テーブルの１番左列に行番号を添付したい場合(0から連番)

```sql
SELECT ROW_NUMBER() OVER() -1 AS row_number, * FROM original
```

| row_number | field1 |
|:---:|:----:|
| 0 | hoge |
| 1 | fuga |
| 2 | piyo |

### 行番号をつける(応用編)

- field1 の order を変えた後に上から順に番号を添付したい

```sql
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

```sql
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)

```sql
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 |

## 参考文献

- [BigQuery 公式リファレンス](https://cloud.google.com/bigquery/docs/reference/standard-sql/)
- [ビッグデータ分析・活用のためのSQLレシピ](https://book.mynavi.jp/ec/products/detail/id=65863)