BigQueryでpivotとunpivotするやつ2

## 前置き

だいぶ前に、pivot, unipivotのBigQueryの記事を書きました
https://qiita.com/taniyam/items/aa235248859499f1bfbb
この時は公式対応されておらず、スマートにクエリを書くことが難しく、複雑なクエリになりがちでした。

しかし、とうとう公式でpivot, unpivotが来たので紹介したいと思います。
https://cloud.google.com/bigquery/docs/release-notes#May_10_2021
(ほぼ、公式ドキュメントに添います。)
私が試したメモとしてこの文章を残します。

## pivot & unpivot

pivotとは**縦長テーブル**のフィールドをカラムに置き換えて**横長テーブル**を作ることです。
unpivotはその逆。
ただし、pivotは集計を伴う場合があり、pivot処理は不可逆的になる場合があります。

**縦長テーブル**

| 行 | product | sales | quarter |
|----|---------|-------|---------|
| 1  | Kale    | 51    | Q1      |
| 2  | Kale    | 23    | Q2      |
| 3  | Kale    | 45    | Q3      |
| 4  | Kale    | 3     | Q4      |
| 5  | Apple   | 77    | Q1      |
| 6  | Apple   | 0     | Q2      |
| 7  | Apple   | 25    | Q3      |
| 8  | Apple   | 2     | Q4      |

**横長テーブル**

| 行 | product | Q1 | Q2 | Q3 | Q4 |
|----|---------|----|----|----|----|
| 1  | Kale    | 51 | 23 | 45 | 3  |
| 2  | Apple   | 77 | 0  | 25 | 2  | 

二つのテーブルは行数や列数こそ違いますが、表している情報としては同じです。
ただ、quarterがQ3までしか入っていない時にQ4を追加する場合を考えると**縦長**で保持する方が行を追加するだけで済み対応が楽です。
カラムを追加するのはスキーマの変更を伴い重たい作業になってしまいます。
一方で、**横長**の方がquarter,productごとの比較が見やすいです。

## pivot

以下の縦長テーブルを横長テーブルに変換したいと思います。

```sql
CREATE TEMP TABLE Produce AS
SELECT *
FROM UNNEST([STRUCT<product STRING, sales INT64, quarter STRING>
  ('Kale', 51, 'Q1'),
  ('Kale', 23,'Q2'),
  ('Kale', 45,'Q3'),
  ('Kale', 3,'Q4'),
  ('Apple', 77,'Q1'),
  ('Apple', 0,'Q2'),
  ('Apple', 25,'Q3'),
  ('Apple', 2,'Q4')
]);
-- SELECT * FROM Produce;
```

作ったテーブルに対して、以下のクエリを発行すると横長テーブルになります。(試したい場合は上のクエリをWITH句にして下のクエリと連結した方が見やすいかも)

```sql
SELECT product, Q1, Q2, Q3, Q4
FROM (SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));
```
ここで、新しく追加されたPIVOT Operatorが出てきて、結果は

| 行 | product | Q1 | Q2 | Q3 | Q4 |
|----|---------|----|----|----|----|
| 1  | Kale    | 51 | 23 | 45 | 3  |
| 2  | Apple   | 77 | 0  | 25 | 2  | 

となります。PIVOTの中で指定されていない`product`がユニークになり出力されいるのがわかります。
つまり、PIVOTの前のFROMで必要なカラムだけに絞っておくことが重要です。


PIVOTの使い方は公式に以下のようにあります。

```
PIVOT(
  aggregate_function_call [as_alias][, ...]
  FOR input_column
  IN ( pivot_column [as_alias][, ...] )
)
```

#### `aggregate_function_call`
集計関数を記述し、`input_column`で展開したカラムのフィールドになります。
エイリアスをつけることで、複数指定することもできます。
その場合、自動生成されるカラム名は「エイリアス名_`pivot_column`」となります。

`SUM`, `COUNT`, `COUNTIF`, `MAX`, `MIN`, `AVG`, `ARRAY_AGG(column_name IGNORE NULLS)`
あたりよく使いそうなのは確認しました。
しかし、`ANY_VALUE `は非対応っぽいです。

PIVOT Operatorの前のFROM句で指定しているテーブルの`aggregate_function_call`, `input_column`で指定され**なかった**残りのカラムがキー(ユニーク)になり、行で出力されます。
余ったカラムと`input_column`をキーにGROUP BYするイメージですね。

#### `input_column`
縦横変換したいカラムを指定します。
**式が使える**ので空中でカラムを作ったり、型を変換したりできます。
STRING型以外の型もある程度使えるようです。

#### `pivot_column`
`input_column`で横展開し新しく作るカラムを指定します。
ここで指定した集合と`input_column`で指定した値が一致したものが集計されます。
`input_column`がSTRING型でBigQueryの仕様でカラム名にできない文字列の場合(日本語など)はエイリアスをつけることで別名にできます。
また、`input_column`がSTRING型以外の場合も暗黙的にカラム名が生成されたりします。詳しくは公式ドキュメント参照。

ちなみに**定数限定で式が使えません！！**

```
PIVOT(SUM(sales) FOR quarter IN (SELECT DISTINCT quarter FROM Produce));
```

みたいなことを書きたくなりますが、できません。残念。
query parameterも使えないそうです。

### pivotサンプル (結果のフィールドを複数にする場合)

先ほどのテーブルを少し改造して`profit`のカラムを追加しました。

```sql
WITH Produce AS (
  SELECT *
  FROM UNNEST([STRUCT<product STRING, sales INT64, profit INT64, quarter STRING>
    ('Kale', 51, 1, 'Q1'),
    ('Kale', 23, 2, 'Q2'),
    ('Kale', 45, 3, 'Q3'),
    ('Kale', 3, 4, 'Q4'),
    ('Apple', 77, 5, 'Q1'),
    ('Apple', 0, 6, 'Q2'),
    ('Apple', 25, 7, 'Q3'),
    ('Apple', 2, 8,'Q4')
  ])
)
```

上のテーブルを下のクエリでpivotする

```sql
SELECT *
FROM Produce
PIVOT(SUM(sales) AS sales, SUM(profit) AS profit FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));
```

| 行 | product | sales_Q1 | profit_Q1 | sales_Q2 | profit_Q2 | sales_Q3 | profit_Q3 | sales_Q4 | profit_Q4 |
|----|---------|----------|-----------|----------|-----------|----------|-----------|----------|-----------|
| 1  | Kale    | 51       | 1         | 23       | 2         | 45       | 3         | 3        | 4         |
| 2  | Apple   | 77       | 5         | 0        | 6         | 25       | 7         | 2        | 8         |

展開したカラム名が自動的に作られています。
このように`aggregate_function_call`ではエイリアスをつければ複数指定することもできます。

pivotテーブルは出力用途でフラットな表にすることを考えるとSTRUCTにしたいモチベーションも特にないような気がしますが、結果をSTRUCTでまとめたいならこう書けばいいでしょうか。

```sql
SELECT *
FROM Produce
PIVOT(
  ARRAY_AGG(STRUCT(sales, profit) IGNORE NULLS)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);
```

| 行 | product | Q1.sales | Q1.profit | Q2.sales | Q2.profit | Q3.sales | Q3.profit | Q4.sales | Q4.profit |
|----|---------|----------|-----------|----------|-----------|----------|-----------|----------|-----------|
| 1  | Kale    | 51       | 1         | 23       | 2         | 45       | 3         | 3        | 4         |
| 2  | Apple   | 77       | 5         | 0        | 6         | 25       | 7         | 2        | 8         |

(`SUM`にはなっていないしQ1~Q4が配列になるため、その後の処理がQ1~Q4に対してUNNESTが必要なり大変そう。
`SUM`になっていない問題は前段のクエリであらかじめ`SUM`しておき、`product`,`quarter`でユニークにしとけば大丈夫そう。)

おまけで`input_column`がSTRINGではないパターンを試しました。
(このpivot操作で得られる表自体に特に意味はありません)

```sql
SELECT *
FROM (SELECT * EXCEPT(quarter) FROM Produce)
PIVOT(
  SUM(sales)
  FOR profit IN (1, 2, 7, 8 AS eight)
);
```

| 行 | product | _1   | _2   | _7   | eight |
|----|---------|------|------|------|-------|
| 1  | Kale    | 51   | 23   | null | null  |
| 2  | Apple   | null | null | 25   | 2     |

数字のままだとカラム名にできないため、暗黙的に「_」がついています。
また、`eight`のように自分で名前を変えることもできます。



## unpivot

基本的にpivotの逆です。しかし、集計操作をしたことで落ちた情報までは戻せません。

pivotしたテーブルを戻します。

| 行 | product | Q1 | Q2 | Q3 | Q4 |
|----|---------|----|----|----|----|
| 1  | Kale    | 51 | 23 | 45 | 3  |
| 2  | Apple   | 77 | 0  | 25 | 2  | 

```sql
WITH Produce AS (
  SELECT *
  FROM UNNEST([STRUCT<product STRING, Q1 INT64, Q2 INT64, Q3 INT64, Q4 INT64>
    ('Kale', 51, 23, 45, 3),
    ('Apple', 77, 0, 25, 2)
  ])
)
```

以上のテーブルを以下のクエリでunpivotできます。

```sql
SELECT *
FROM (SELECT *  FROM Produce)
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4));
```

UNPIVOTの使い方は公式に以下のようにあります。
と言ってもPIVOTとほぼ同じで集計の逆操作はできないことに注意するとすぐに理解できます。

`single_column_unpivot`: unpivotするカラムが一つの場合

```sql
UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
  values_column
  FOR name_column
  IN (columns_to_unpivot)
)
```

#### `values_column`
`columns_to_unpivot`で指定するカラムのフィールドをまとめるカラム名

#### `name_column`
`columns_to_unpivot`で指定するカラム名をまとめるカラムの名前

#### `columns_to_unpivot`
pivotされたカラムを指定する。

### unpivotサンプル (結果のフィールドを複数にする場合の逆)

| 行 | product | sales_Q1 | profit_Q1 | sales_Q2 | profit_Q2 |
|----|---------|----------|-----------|----------|-----------|
| 1  | Kale    | 51       | 1         | 23       | 2         |
| 2  | Apple   | 77       | 5         | 0        | 6         |

先ほど作ったこのテーブル(のカラムを減らしたもの)をunpivotしてみます。

`multi_column_unpivot`モードを使い、以下のように書けます。

```sql
WITH Produce AS (
  SELECT *
  FROM UNNEST([STRUCT<product STRING, sales_Q1 INT64, profit_Q1 INT64, sales_Q2 INT64, profit_Q2 INT64>
    ('Kale', 51, 1, 23, 2),
    ('Apple', 77, 5, 0, 6)
  ])
)
SELECT *
FROM Produce
UNPIVOT(
  (sales, profit)
  FOR quarter
  IN (
    (sales_Q1, profit_Q1) AS 'Q1',
    (sales_Q2, profit_Q2) AS 'Q2'
  )
);
```

| 行 | product | sales | profit | quarter |
|----|---------|-------|--------|---------|
| 1  | Kale    | 51    | 1      | Q1      |
| 2  | Kale    | 23    | 2      | Q2      |
| 3  | Apple   | 77    | 5      | Q1      |
| 4  | Apple   | 0     | 6      | Q2      |

想定通りの結果になっています。

## あ
pivotで作り出すカラム名に式が使えないため、完全に動的に...というわけには行きませんが、とても簡潔に書けるのでいい感じです。
使える機会があったらどんどん使っていきたいと思います。
それでは。