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