バナー画像

皆さんはクロス集計というものをご存じですか?

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式を使った方法についても興味があれば、両方覚えてどちらが使いやすいのかを比較してみても面白いかもしれませんね!

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。