bigqueryでpivot/unpivotができるようになった！

12518 ワード
BigQuery Pivot 便利 BigQuery テキストリンク

タイトルの通り、bigqueryのSQL上でpivot/unpivotの機能がリリースされたようなので、早速試してみました。

pivot処理の例

pivot用のレコード作成


WITH 
Produce AS 
(
  SELECT 'A' as product, 100 as sales, 'Q1' as quarter UNION ALL
  SELECT 'A', 200, 'Q2' UNION ALL
  SELECT 'A', 300, 'Q3' UNION ALL
  SELECT 'A', 400, 'Q4' UNION ALL
  SELECT 'B', 150, 'Q1' UNION ALL
  SELECT 'B', 250, 'Q2' UNION ALL
  SELECT 'B', 350, 'Q3' UNION ALL
  SELECT 'B', 450, 'Q4'
 )

 SELECT * FROM Produce
product sales quarter
A 100 Q1
A 200 Q2
A 300 Q3
A 400 Q4
B 150 Q1
B 250 Q2
B 350 Q3
B 450 Q4

上記に対してproduct、quarter毎にsalesを集計(pivot)する場合


WITH 
Produce AS 
(
  SELECT 'A' as product, 100 as sales, 'Q1' as quarter UNION ALL
  SELECT 'A', 200, 'Q2' UNION ALL
  SELECT 'A', 300, 'Q3' UNION ALL
  SELECT 'A', 400, 'Q4' UNION ALL
  SELECT 'B', 150, 'Q1' UNION ALL
  SELECT 'B', 250, 'Q2' UNION ALL
  SELECT 'B', 350, 'Q3' UNION ALL
  SELECT 'B', 450, 'Q4'
 )

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
product Q1 Q2 Q3 Q4
A 100 200 300 400
B 150 250 350 450

pivotできた！！！

unpivot処理の例

unpivot用のレコード作成


WITH Produce AS (
  SELECT 'A' as product, 100 as Q1, 200 as Q2, 300 as Q3, 400 as Q4
  UNION ALL
  SELECT 'B', 150,250, 350, 450)

SELECT * FROM Produce
product Q1 Q2 Q3 Q4
A 100 200 300 400
B 150 250 350 450

上記に関してproduct、sales、quarter別にレコードを展開する(unpivot)場合


WITH Produce AS (
  SELECT 'A' as product, 100 as Q1, 200 as Q2, 300 as Q3, 400 as Q4
  UNION ALL
  SELECT 'B', 150,250, 350, 450)

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
product sales quarter
A 100 Q1
A 200 Q2
A 300 Q3
A 400 Q4
B 150 Q1
B 250 Q2
B 350 Q3
B 450 Q4

という感じになるようです。

まとめ

今までは、bigqueryで抽出した結果を
・case when & SUM or Count などでpivot頑張る
・外部のデータ処理用ツール(スプレッドシートやexploratoryなど）にデータを吐き出してからpivotする
でしたが、この機能実装により、bigquery上で完結できるようになりました。
大変ありがたしです。

公式のマニュアルはこちら