最近、データ周りのお仕事をしています!モリヤス(@_moriyas)です!
バスケのピボット?あ〜知ってる知ってる!大事なファンダメンタルよな〜(小中高バスケ部の僕)
って今回はそんな話ではありません!!
先日、普段クエリを書いていてもなかなか使うことがなかった、PIVOTという構文を使う場面に遭遇したので、「こんな時に使えるよ!使えそうだよ!」という紹介をしたいと思います。
※間違えや、より良い方法などあれば教えていただけると幸いですm(_ _)m
環境
- 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
)
)
上記のクエリの結果が下記になります。
user_id | user_rank_202404 | user_rank_202405 |
---|---|---|
1 | 1 | 2 |
2 | 4 | 5 |
試しにPIVOTの集計関数をMAXからMINに変えてみて実行してみましょう!
すると結果は下記になりました。
user_id | user_rank_202404 | user_rank_202405 |
---|---|---|
1 | 1 | 1 |
2 | 2 | 5 |
FORの後に書いたカラムの値が集計のカテゴリーのようになり、その中でMAXの値、MINの値を抽出するというような意味になっているのが分かりました!
終わりに
あまりにもひどい説明だった気がしますが、なんとなく使い方がわかっていただけたと思います!(圧)
考え方が少し難しいし、普段あまり使わないのでPIVOTへの僕自身の理解もまだまだなのかもしれませんね…
ぜひここぞという時に使ってみてくださいd( ̄  ̄)