BigQueryでpivotとunpivotするやつ

## 追記
2021/05/11: 公式でpivot, unpivot機能がつきました。🎉
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator

## pivotとunpivot
テーブルの縦長テーブルを横長テーブルにするのがpivot
横長テーブルを縦長テーブルにするのがunpivot

つまり、こういうこと。~~ちょっと例が悪かったかも~~
![pivot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287793/6c1431ff-f42a-986e-fce9-e61a7a31fc18.png)
例えば、GoogleAnalyticsのcustomDimensionsなんかはkey/valueの配列だからこんな感じ

## Pivot

pivotする際にBigQueryは動的にカラムを生成する術が今のところない(と思われる)ので`key`カラムの値ごとにSELECTする必要がありクエリが長くなりがち。
値ごとに`ANY_VALUE`や`MAX`なんかの集計関数を使う必要がある。

```sql
CREATE TEMP TABLE bukken AS
SELECT *
FROM UNNEST([STRUCT<id INT64, key STRING, value STRING>
  (1, '駅', '新宿'),
  (1, '家賃', '118000'),
  (1, 'タイプ', 'マンション'),
  (2, '駅', '渋谷'),
  (2, '家賃', '78000'),
  (3, 'タイプ', 'アパート'),
  (3, '駅', '池袋')]);
```

```sql
WITH ranked_key AS (
  SELECT key, RANK() OVER (ORDER BY key) AS rank
  FROM (SELECT DISTINCT key FROM bukken)
)
, wide_ranked AS (
  SELECT
    ANY_VALUE(IF(key="タイプ", rank, NULL)) AS type,
    ANY_VALUE(IF(key="家賃", rank, NULL)) AS yachin,
    ANY_VALUE(IF(key="駅", rank, NULL)) AS eki
  FROM ranked_key
)
, long_array_agg AS (
  SELECT id, ARRAY_AGG(STRUCT(value AS s) ORDER BY key) AS values
  FROM bukken
  RIGHT JOIN (
    SELECT id, key
    FROM ranked_key, (SELECT DISTINCT id FROM bukken)
    ORDER BY id, rank
  ) USING (id, key)
  GROUP BY id
)
, result AS (
  SELECT id,
    values[ORDINAL(type)].s AS type,
    values[ORDINAL(yachin)].s AS yachin,
    values[ORDINAL(eki)].s AS eki
  FROM wide_ranked, long_array_agg
  ORDER BY id
)
SELECT * FROM result;
```
[こちら](https://corecompete.com/how-to-build-pivot-tables-in-bigquery-fast-and-easy/)を参考に少し変えている。
単に`ANY_VALUE(IF(key='タイプ', value, NULL))`とする方がクエリは短いが、上記の方法の方が早いとのこと(だが寧ろ遅くなった後述)

最初に思いつく方の簡単なpivotクエリ

```sql
WITH slow AS (
  SELECT id,
    ANY_VALUE(IF(key="タイプ", value, NULL)) AS type,
    ANY_VALUE(IF(key="家賃", value, NULL)) AS yachin,
    ANY_VALUE(IF(key="駅", value, NULL)) AS eki,
  FROM bukken
  GROUP BY id
)
SELECT * FROM slow;
```

### 速度比較

```sql
CREATE TABLE bukken AS
SELECT id, key, value
FROM UNNEST([STRUCT<key STRING, value STRING>
  ('駅', '新宿'),
  ('家賃', '118000'),
  ('タイプ', 'マンション')
])
CROSS JOIN UNNEST(GENERATE_ARRAY(1, 1000000)) AS id;
```
以上のカサ増ししたテーブルで時間を測ってみたところ
fastバージョンが`24.1`秒でslowバージョンが`5.1`秒と逆に遅くなってしまった。（？）
オリジナルのクエリを弄ってしまったのが問題なのかもしれない。

## Unpivot

一方こちらは、いちいち値ごとに`ANY_VALUE`とか`MAX`とかする必要がなく書けてしまう。
キモはレコードの`STRUCT`オブジェクトを`TO_JSON_STRING`を用いてカラム名を取り出すところにある。
[参考にしたページ](https://stackoverflow.com/questions/27832170/how-to-unpivot-in-bigquery)

unpivotの処理部分は先に関数として切り出しておく。

```sql
CREATE TEMP FUNCTION UNPIVOT(x ANY TYPE, col_regex STRING) AS ((
SELECT ARRAY_AGG(STRUCT(key, value))
FROM (
  SELECT 
     REGEXP_EXTRACT(y, '^"([^"]+)') AS key,
     REGEXP_EXTRACT(y, r':"?([^"]+)') AS value
  FROM (
    SELECT SPLIT(REPLACE(REPLACE(json, '{', ''), '}', ''), ',') AS items,
    FROM (SELECT TO_JSON_STRING(x) AS json)
  ), UNNEST(items) AS y
)
WHERE REGEXP_CONTAINS(key, col_regex)
));
```
少しネストが深くて見辛いが順にみていく。

```sql
SELECT SPLIT(REPLACE(REPLACE(json, '{', ''), '}', ''), ',') AS items,
FROM (SELECT TO_JSON_STRING(x) AS json)
```

先ほどキモといった部分。`x`はpivotテーブルの１行が`STRUCT`で入ってくる。
`TO_JSON_STRING(x)`で

```text
{"id":1,"type":"マンション","yachin":118000,"eki":"新宿"}
```
とJOSN文字列に変換される。
基本的にはこれを頑張ってパースしていく。
`JSON_EXTRACT`という関数もあるがこれだとkey自体が取得できない。

とりあえず、`SPLIT(REPLACE(REPLACE(json, '{', ''), '}', ''), ',')`部分で`{`と`}`を削除して`,`で分割している。

```
items = ['"id":1', '"type":"マンション"', '"yachin":118000', '"eki":"新宿"']
```
という感じ。

```sql 
REGEXP_EXTRACT(y, '^"([^"]+)') AS key,
REGEXP_EXTRACT(y, r':"?([^"]+)') AS value
```
の部分でkeyとvalueに分解している。(正規表現はあまり得意でなく自信がない)

そして、得られたkey/valueを`ARRAY_AGG`でまとめるのだが、その前に

```sql
WHERE REGEXP_CONTAINS(key, col_regex)
```
で必要なkeyのみを取得するようにする。`col_regex`で該当するkeyのみを取得する。

#### 使い方

```sql
WITH pivot AS (
  SELECT *
  FROM UNNEST([STRUCT<id INT64, type STRING, yachin INT64, eki STRING>
    (1, 'マンション', 118000, '新宿'),
    (2, NULL, 78000, '渋谷'),
    (3, 'アパート', NULL, '池袋')])
)
SELECT
  id,
  CASE item.key
    WHEN 'type' THEN 'タイプ'
    WHEN 'yachin' THEN '家賃'
    WHEN 'eki' THEN '駅'
  END AS key,
  item.value
FROM pivot AS x, UNNEST(UNPIVOT(x, r'[^(id)]+')) AS item
WHERE value != 'null';
```

pivotされたレコードを先ほどの`UNPIVOT`関数に渡し`UNNEST`するだけ。
今回はidを含む行**以外**を取得するような正規表現を渡している。

これで縦長のテーブルに変換できる。

### 感想
BigQueryではあんまりpivotとかunpivotとかやらないような気もするが、引き出しは多い方が良いためメモ。