横持ちのデータを縦持ちに変換して使いたかったので調べらたUNPIVOTと呼ばれる演算子が存在することを知りました。
ドキュメント見ても使い方がピンとこなかったので備忘録がてら使い方と考え方を紹介。
やりたいこと
例えば、動画再生回数を4半期毎に管理するviewsテーブルがあったとします。
このテーブルは動画のタイトルとQ1～Q4までの期間で何度動画が再生されたかを持っています。
| title | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| 逆襲のシャア | 1200 | 2500 | 900 | 1100 |
| 彗星の魔女 | 3300 | 3000 | 2100 | 2200 |
それを何らかの要件により1レコードを1クオーターで取得したくなりました。
つまり、「逆襲のシャア」のQ1での再生回数、Q2での再生回数・・・Q4での再生回数といった形です。
| title | plays | quarter |
|---|---|---|
| 逆襲のシャア | 1200 | Q1 |
| 逆襲のシャア | 2500 | Q2 |
| 逆襲のシャア | 900 | Q3 |
| 逆襲のシャア | 1100 | Q4 |
| 彗星の魔女 | 3300 | Q1 |
| 彗星の魔女 | 3000 | Q2 |
| 彗星の魔女 | 2100 | Q3 |
| 彗星の魔女 | 2200 | Q4 |
クエリ
冒頭で触れた通りUNPIVOT（アンピポット）演算子を使うと一発でやりたいことが実現できます。
WITH views AS (
SELECT '逆襲のシャア' as title, 1200 as Q1, 2500 as Q2, 900 as Q3, 1100 as Q4, UNION ALL
SELECT '彗星の魔女', 3300, 3000, 2100, 2200)
SELECT * FROM views UNPIVOT(play_count FOR quarter IN (Q1, Q2, Q3, Q4))
考え方
UNPIVOTを使ってめでたく実現したいことはできたのですが、この演算子の使い方を理解するのに頭を悩ませました。
実際に手を動かしクエリを発行しても、構文のどこが結果に掛かってくるのかイメージを持てず？？？だったので、僕なりの解釈をしてみました。
構文の日本語化
SELECT
*
FROM
[テーブル or サブクエリ] UNPIVOT([縦持ちにしたいカラムのデータを保持するカラム] FOR [縦持ちにしたいカラムのカラム名を保持するカラム] IN (縦持ちにしたいカラム))
まずはクエリ構文をこんな感じで日本語化してみます。
この段階で「ほぅ」と理解はできないので上記て使ったviewsを題材に段階的に解きほぐしていきます。
1. テーブル or サブクエリ
これは書いてある通りで、テーブルかサブクエリを指定します。
なので当てはめるのはviewsです。
SELECT
*
FROM views UNPIVOT([縦持ちにしたいカラムのデータを保持するカラム] FOR [縦持ちにしたいカラムのカラム名を保持するカラム] IN (Q1, Q2, Q3, Q4))
2. 縦持ちにしたいカラム
ここが重要なんですが、どのデータを縦持ちにしたいのかを指定します。
やりたいことは、「1レコードを1クーターで取得したい」なので、Q1～Q4を当てはめます。
SELECT
*
FROM views UNPIVOT([縦持ちにしたいカラムのデータを保持するカラム] FOR [縦持ちにしたいカラムのカラム名を保持するカラム] IN (Q1, Q2, Q3, Q4))
3. 縦持ちにしたいカラムのカラム名を保持するカラム
カラムカラムでややこしいんですが、要はQ1、Q2、Q3、Q4を縦持ちにしたいんだよね？
OK、じゃあ変換するときに元がなんてカラム名だったのか分かるように新しいカラムを定義してそこに突っ込んどくぜ！
ってのを指定する箇所です。
任意の名称を付けられるので表現にあったカラム名を指定します。
SELECT
*
FROM views UNPIVOT([縦持ちにしたいカラムのデータを保持するカラム] FOR quarter IN (Q1, Q2, Q3, Q4))
4. 縦持ちにしたいカラムのデータを保持するカラム
最後に元のQ1、Q2、Q3、Q4に入っている値（再生回数）は変換後どこに入れるの？って部分を指定します。
こちらも任意の名称を付けられるので表現にあったカラム名を指定します。
SELECT
*
FROM views UNPIVOT(play_count FOR quarter IN (Q1, Q2, Q3, Q4))
結果
元データ : SELECT * FROM views
UNPIVOT : SELECT * FROM views UNPIVOT(play_count FOR quarter IN (Q1, Q2, Q3, Q4))
所感
頻繁に利用することはないと思いますが、ハマればとても便利だと思います。
ただ慣れてないと初見でうっとなる気もしますが。。。（僕もそう
参考
UNPIVOTの名前の通りピボットをアンしている演算子になるので、縦持ちデータを横持ちに変換するPIVOTもあります。