BigQueryで行を列に変換したい（Pivot）

クエリを書いていると、ExcelのPivotでやるように列の値で整形したい時があります。

service	user_type	user_count
App1	member	1251
App1	temporary	419
App2	member	320
App2	temporary	118
App3	member	720
App3	temporary	330

例えば上表はmember（本会員）とtemporary（仮会員）の人数表ですが、これを..

service	member	provisional
App1	1251	419
App2	320	118
App3	720	330

このように、user_typeを右に列名とし並べてて出したい訳です。
そんな時はクエリでCASE文を使ってこう書いていました。

WITH Kaiin AS (
  SELECT 'App1' as service, 'member' as user_type, 1251 as user_count UNION ALL
  SELECT 'App1', 'provisional', 419 UNION ALL
  SELECT 'App2', 'member', 320 UNION ALL
  SELECT 'App2', 'provisional', 118 UNION ALL
  SELECT 'App3', 'member', 720 UNION ALL
  SELECT 'App3', 'provisional', 330)
SELECT 
  service,
  sum(CASE WHEN user_type="member" THEN user_count ELSE 0 END) as member,
  sum(CASE WHEN user_type="provisional" THEN user_count ELSE 0 END) as provisional,
FROM Kaiin
GROUP BY service

これ、列値が多いとCASE文がどんどん増えていくので嫌だったのですが、つい最近の2021年7月にBigQueryがPivotオプションをサポートしてくれていました。

https://cloud.google.com/bigquery/docs/release-notes#July_19_2021
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator

その結果、さっきのCASE文だらけになりそうなクエリが..

WITH Kaiin AS (
  SELECT 'App1' as service, 'member' as user_type, 1251 as user_count UNION ALL
  SELECT 'App1', 'provisional', 419 UNION ALL
  SELECT 'App2', 'member', 320 UNION ALL
  SELECT 'App2', 'provisional', 118 UNION ALL
  SELECT 'App3', 'member', 720 UNION ALL
  SELECT 'App3', 'provisional', 330)
SELECT * FROM Kaiin
  PIVOT(SUM(user_count) FOR user_type in ("member", "provisional"))

こうIN句で書けるようになって、スッキリと集計できるようになりました！
このPIVOTの構文は見慣れない印象でしたが、集計項目と列値を記載すれば残りでグルーピングしてくれるので楽ですね。個人的には有難いアップデートでした。

ちなみに集計機能を作っているときにはプロセスが反対になるのであまり使わないと思いますが、逆向きのUNPIVOTも用意されています。

ランチェスターでは1年ちょっと前にAWSに加えてGCPも使い始めたので、GCPにはまだ知らないサービスが数多くあります。そんな中でBigQueryだけ見てもこうして手堅く機能が追加されているので、最適な分析インフラを作るための探究はまだまだ続きそうです。

ランチェスターではデータエンジニアを募集中です。ご興味のある方はお気軽にお問い合わせください。
https://herp.careers/v1/lanchester
https://www.wantedly.com/companies/lanchester/projects