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

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

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句にして下のクエリと連結した方が見やすいかも)

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のカラムを追加しました。

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する

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でまとめたいならこう書けばいいでしょうか。

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操作で得られる表自体に特に意味はありません)

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

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できます。

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

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

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

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モードを使い、以下のように書けます。

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