【BigQuery】PIVOTの使い方

最近、データ周りのお仕事をしています！モリヤス（@_moriyas）です！

バスケのピボット？あ〜知ってる知ってる！大事なファンダメンタルよな〜（小中高バスケ部の僕）

って今回はそんな話ではありません！！

先日、普段クエリを書いていてもなかなか使うことがなかった、PIVOTという構文を使う場面に遭遇したので、「こんな時に使えるよ！使えそうだよ！」という紹介をしたいと思います。

※間違えや、より良い方法などあれば教えていただけると幸いですm(_ _)m

1 環境
2 PIVOT構文について
3 解説
4 集計関数に関して
5 終わりに

環境

BigQuery

PIVOT構文について

使い方は下記の通りです。

SELCT ピボット操作後のテーブルのカラム名
FROM ピボットの操作をする対象テーブル
PIVOT (
  集計関数(カラム名（ピボット操作後のカラムの値になる）)
  FOR ピボット操作後のカラム名になるカラム名（自分でも何言っているかよくわからなくなるw）
  IN (ピボット操作後のカラム名になるカラム名の値)
)

ちょっと自分でもわからなくなるような日本語を使ってしまっていますが、下記で説明する実際のデータやクエリを見ていただければ理解していただけるはずですd(￣￣)

解説

前提

とあるゲームのユーザーにはランクがあって、月ごとにどのランクにいるのかのログが入っているテーブルがあるとします！

つまり、上記のテーブルは下記のようなレコードが入っています。

user_id	month	user_rank
1	2024-04-01	1
1	2024-05-01	2
1	2024-06-01	2
1	2024-07-01	2
1	2024-08-01	3
1	2024-09-01	2
1	2024-10-01	5
2	2024-04-01	4
2	2024-05-01	5
2	2024-06-01	5
3	2024-09-01	1
3	2024-10-01	2

上記のようなテーブルを、ユーザーごと1レコードにまとめておきたいという場合

つまり下記のようなテーブルに変換したい場合、使えるのがPIVOTになります！

user_id	user_rank_202404	user_rank_202405	user_rank_202406	user_rank_202407	user_rank_202408	user_rank_202409	user_rank_202410
1	1	2	2	2	3	2	5
2	4	5	5
3						1	2

クエリは次の通りです。

WITH 
temp_monthly_user_rank_table AS (
  SELECT 1 AS user_id, '2024-04-01' AS month, '1' AS user_rank
  UNION ALL
  SELECT 1, '2024-04-01', '1'
  UNION ALL
  SELECT 1, '2024-05-01', '2'
  UNION ALL
  SELECT 1, '2024-06-01', '2'
  UNION ALL
  SELECT 1, '2024-07-01', '2'
  UNION ALL
  SELECT 1, '2024-08-01', '3'
  UNION ALL
  SELECT 1, '2024-09-01', '2'
  UNION ALL
  SELECT 1, '2024-10-01', '5'
  UNION ALL
  SELECT 2, '2024-04-01', '4'
  UNION ALL
  SELECT 2, '2024-05-01', '5'
  UNION ALL
  SELECT 2, '2024-06-01', '5'
  UNION ALL
  SELECT 3, '2024-09-01', '1'
  UNION ALL
  SELECT 3, '2024-10-01', '2'
)

SELECT
  user_id,
  user_rank_202404,
  user_rank_202405,
  user_rank_202406,
  user_rank_202407,
  user_rank_202408,
  user_rank_202409,
  user_rank_202410
FROM
  temp_monthly_user_rank_table
PIVOT (
  MAX(user_rank)
  FOR month IN(
    '2024-04-01' AS user_rank_202404, 
    '2024-05-01' AS user_rank_202405, 
    '2024-06-01' AS user_rank_202406, 
    '2024-07-01' AS user_rank_202407, 
    '2024-08-01' AS user_rank_202408, 
    '2024-09-01' AS user_rank_202409, 
    '2024-10-01' AS user_rank_202410
    )
  )

「PIVOT構文について」のセクションで説明したPIVOTの使い方と実際のクエリを比較してみましょう！

SELCT ピボット操作後のテーブルのカラム名 ・・・①
FROM ピボットの操作をする対象テーブル ・・・②
PIVOT (
  集計関数(カラム名（ピボット操作後のカラムの値になる）) ・・・③
  FOR ピボット操作後のカラム名になるカラム名（自分でも何言っているかよくわからなくなるw ） ・・・④
  IN (ピボット操作後のカラム名になるカラム名の値) ・・・⑤
)

①：user_id, user_rank_202404, user_rank_202405, user_rank_202406, user_rank_202407, user_rank_202408, user_rank_202409, user_rank_202410
②：temp_monthly_user_rank_table
③：MAX(user_rank)
④：month
⑤：'2024-04-01' AS user_rank_202404, '2024-05-01' AS user_rank_202405, '2024-06-01' AS user_rank_202406, '2024-07-01' AS user_rank_202407, '2024-08-01' AS user_rank_202408, '2024-09-01' AS user_rank_202409, '2024-10-01' AS user_rank_202410

①②は説明不要かと思います！

③について：変換後のテーブルの値を見てみると変換前のテーブルのuser_rankの値になっているのがわかるかと思います。
④⑤について：変換前のテーブルの「month」カラムの値がINの中に書かれており、それぞれASを使って別名を与えられています。そのASの名前が変換後のテーブルのカラム名になっているのがわかります。

集計関数に関して

上記③の集計関数に関してですが、どのような意味があるのでしょうか？

上記のテーブル例だと意味が分かりにくので別のテーブルを用意して説明したいと思います。

前提

とあるゲームにユーザーにはランクがあって、週ごとにどのランクにいるのかのログが入っているテーブルがあるとします！

先ほどとの違いは月ごとであるか週ごとであるか

つまり、下記のようなテーブルになります。

※週の初めを月曜日としています。
※めんどくさいので4月と5月のみでuserも1と2のみのデータになっています。

user_id	week	user_rank
1	2024-04-01	1
1	2024-04-08	1
1	2024-04-15	1
1	2024-04-22	1
1	2024-04-29	1
1	2024-05-06	2
1	2024-05-13	2
1	2024-05-20	2
1	2024-05-27	1
2	2024-04-01	2
2	2024-04-08	3
2	2024-04-15	4
2	2024-04-22	3
2	2024-04-29	4
2	2024-05-06	4
2	2024-05-13	5
2	2024-05-20	5
2	2024-05-27	5

先ほどと同じくPIVOTを使って、ユーザーごと1レコードにまとめてみましょう。

また、今回の場合は週ごとのレコードですが、先ほどと同じく変換後のテーブルで月ごとのユーザーランクを出します！

あれ？週ごとのユーザーランクが出ているのに変換後は月ごとにするの？？

そう思ったあなた！！

ここで集計関数の意味がなんとなくわかるのではないでしょうか？

ひとまずクエリを書いてみます。

WITH 
temp_weekly_user_rank_table AS (
  SELECT 1 AS user_id, '2024-04-01' AS week, '1' AS user_rank
  UNION ALL
  SELECT 1, '2024-04-08', '1'
  UNION ALL
  SELECT 1, '2024-04-15', '1'
  UNION ALL
  SELECT 1, '2024-04-22', '1'
  UNION ALL
  SELECT 1, '2024-04-29', '1'
  UNION ALL
  SELECT 1, '2024-05-06', '2'
  UNION ALL
  SELECT 1, '2024-05-13', '2'
  UNION ALL
  SELECT 1, '2024-05-20', '2'
  UNION ALL
  SELECT 1, '2024-05-27', '1'
  UNION ALL
  SELECT 2, '2024-04-01', '2'
  UNION ALL
  SELECT 2, '2024-04-08', '3'
  UNION ALL
  SELECT 2, '2024-04-15', '4'
  UNION ALL
  SELECT 2, '2024-04-22', '3'
  UNION ALL
  SELECT 2, '2024-04-29', '4'
  UNION ALL
  SELECT 2, '2024-05-06', '4'
  UNION ALL
  SELECT 2, '2024-05-13', '5'
  UNION ALL
  SELECT 2, '2024-05-20', '5'
  UNION ALL
  SELECT 2, '2024-05-27', '5'
)

SELECT
  user_id,
  user_rank_202404,
  user_rank_202405
FROM
  temp_weekly_user_rank_table
PIVOT (
  MAX(user_rank) 
  FOR DATE_TRUNC(DATE(week), MONTH) IN( 
    '2024-04-01' AS user_rank_202404, 
    '2024-05-01' AS user_rank_202405
    )
  )

上記のクエリの結果が下記になります。