皆さんはクロス集計というものをご存じですか？
SQLの学習を始めたばかりの方にとっては聞きなれない言葉だと思いますが、データベースでは集計を扱うこともしばしばあるため、是非覚えたいスキルです。
そこで今回はこの「クロス集計」について解説してみたいと思います。
最初はわけがわからないかもしれませんが、何度も読み直せば理解できるはずなので頑張ってみましょう！
クロス集計とは
クロス集計とは無数の設問を掛け合わせて集計する方法で、マーケティングには欠かせない手法となっています。
街頭でアンケートに答えた経験がある方もいると思いますが、あのアンケートはクロス集計に使われることがほとんどなはずです。
またクロス集計を更に細分化すると「二重クロス集計」「三重クロス集計」など様々なものがありますが、今回の解説では「単純集計」と呼ばれる最も基本の集計で進めます。
この単純集計はGT集計とも呼ばれますが、SQLの技術情報ではこのGT集計という名称で記述されていることがほとんどなので覚えておくようにしましょう。
SQLでのクロス集計の方法
SQLでクロス集計を実行する方法はいくつかあります。
ネットで検索するとすぐに出てくる方法として「CASE式を使ったクロス集計」と「PIVOTとUNPIVOTを使ったクロス集計」という二つの集計方法があります。
どちらもプロセスに大きな違いはありませんが、PIVOT句及びUNPIVOT句はそれぞれクロス集計のための命令句となっているため、今回はこちらを使った方法で解説していきます。
PIVOTとUNPIVOTを使ったクロス集計とCASE式の違いを理解するために、興味があればCASE式を使ったクロス集計についても調べてみると良いでしょう。
PIVOTとUNPIVOT
クロス集計について学習する前に、簡単にPIVOTとUNPIVOTについて説明しておく必要がありますので、解説してみたいと思います。
PIVOT演算子とUNPIVOT演算子
PIVOT句を使うと、テーブルの中にあるレコード(行)をカラム(列)に変換することが出来ます。
逆にUNPIVOT句はは、テーブルにあるカラム(列)をレコード(行)に変換することが出来ます。
PIVOT句とUNPIVOT句は作用が対照的なため、両方セットで覚えるようにしてください。
またMicrosoftの公式サイトでは次のように記載されています。
関係演算子 PIVOT および UNPIVOT を使用すると、テーブル値式を別のテーブルに変更できます。
PIVOT 関係演算子は、テーブル値式の中のある列から一意の値を取得して出力側の複数の列に変換することで式を行列変換し、最終的な出力のそれ以外の列値に必要な集計を行います。
UNPIVOT 関係演算子の機能は PIVOT 関係演算子の逆で、テーブル値式の複数の列を列値に行列変換します。
使い方
それでは使い方について見ていきましょう。
基本的な記述は次の通りです。
// PIVOT句
SELECT
グループ化対象列の値1,
グループ化対象列の値2,
・・・
FROM テーブル名
PIVOT (
集計関数(集計対象列)
FOR グループ化対象列
IN (グループ化対象列の値1, グループ化対象列の値2, ・・・)
) AS 別名
// UNPIVOT句
SELECT
指定した列を1つにまとめる列名,
指定した列の値が入る列名
FROM テーブル名
UNPIVOT (
指定した列の値が入る列名
FOR 指定した列をまとめる列名
IN (まとめる対象の列名1, まとめる対象の列名2 ・・・)
) AS 別名
それでは実際に動かしてみましょう。
まずは簡単なテーブルを作ります。
mydate name price
---------------- ---------- -----------
2020-08-01 apple 100
2020-08-02 apple 150
2020-08-01 grape 200
2020-08-01 orange 50
2020-08-02 grape 300
2020-08-03 orange 70
2020-08-03 apple 130
このテーブルでPIVOT句を使って表にしてみます。
SELECT
name,
[2020-08-01],
[2020-08-02],
[2020-08-03]
FROM sample_db.dbo.sample_table
PIVOT(
SUM(price)
FOR mydate
IN ([2020-08-01], [2020-08-02], [2020-08-03])
) AS PRICE
GO
name 2020-08-01 2020-08-02 2020-08-03
---------- ----------- ----------- -----------
apple 100 150 130
grape 200 300 NULL
orange 50 NULL 70
通常SELECT文では検索をかけたいカラムを指定しますが、今回は日付を基に集計する必要があるため、このような形となっています。
あとはFROM句の後にPIVOT句を使い縦軸を横軸に変えるための指定をします。
IN句の中では、基軸としたい値を指定するわけですが、ここにはSELECTで指定したmydateをそのまま記述します。
こうすることで、表示結果を見てもわかるようにカラムを行に変換しリストかすることが可能となります。
UNPIVOT句はPIVOT句とは逆(実行結果としては真逆ではない点に注意)ですが、使い方についてはPIVOT句と変わりませんので具体例は割愛します。
ただしUNPIVOT句には注意すべき点があります。
PIVOT句では集計される結果が、UNPIVOT句ではされないという点です。
またUNPIVOT句では、入力に含まれる”NULL値”は出力されません。
UNPIVOT句を使用する際にはこれらの注意点をよく思い出しながら使用しましょう。
まとめ
いかがでしたか？
今回はクロス集計でよく使われる「PIVOT/UNPIVOT関係演算子」について解説してみました。
一度読むだけでは十分理解できないかもしれませんが、自分でコードを記述しながら何度も練習すればそう難しいクエリではない為、すぐ習得することが出来るはずです。
また、CASE式を使ったクロス集計よりはコードがシンプルでわかりやすいものとなっているはずなので、この記事をよく読んで何度も練習してみましょう。
もしCASE式を使った方法についても興味があれば、両方覚えてどちらが使いやすいのかを比較してみても面白いかもしれませんね！
