こんにちは。データ事業部２部の安部健太郎です。

本日は、SQLについてお話いたします。データ分析をする際、DBからデータを抽出して、そのデータをPythonやRで分析する場合に役に立つ話です。

データ量が少ない場合は、DBから必要そうなデータを全てダウンロードしてから、ローカル環境でPythonやRでデータ整形をしてもいいのですが、データが大きい場合は、PythonやRの処理が非常に遅くなってしまう場合があります。そのため、できる限りのことはデータをSQLで処理させてしまった方がいいということがよくあります。また、お客さんによっては、Excelでの納品が必要になる場合もあるため、そのような場合も最初から必要な形でデータを取り出したほうがExcel作業が楽になります。

その一つの技術として、データの縦持ちと横持ちの変形があります。PythonやRではpivot機能を使えば簡単にできますが、SQLで同じことを実現しようとすると少々複雑な書き方が必要なため、本記事で紹介いたします。

まず、今回使用するデータですが、この架空の購買データを用います。データの中には、以下の４つの値が、１００レコードあります。

id : レコードID

user_id : ユーザーID　（ユーザーは４人）

area : 購入地域（購入知識は４箇所）

amount : 購入金額

今回は、ユーザー、および購入地域ごとに、購入金額の合計値を出してみましょう。
最終的なゴールは、1回のクエリで、以下のような結果を出力することです。

縦持ちから横持ちへの変換

まず、横持ちにせず、縦持ちのままuser_id、areaごとの合計値を出力するには、以下のようにクエリを書けばOKです。
最終的に欲しいデータは、全てこの結果に含まれています。このクエリを、本記事ではクエリ１と呼びます。

#クエリ１
select
	user_id,
	area, 
	sum(amount)
from
	sales
group by
	user_id,
	area

必要なデータが過不足なく得られたので、あとは上で得られたデータを横持ちに変換するだけです。

このデータを横持ちに変形するためには、以下のようにクエリ（以下クエリ２）を書き換えます。クエリ１で得られたデータは、クエリ２の中でxと名前をつけています。

#クエリ2
select
    user_id,
    max(case area when "Ginza" then amount else null end) as Ginza,
    max(case area when "Harajuku" then amount else null end) as Harajuku,
    max(case area when "Shinjuku" then amount else null end) as Shinjuku,
    max(case area when "Shibuya" then amount else null end) as Shibuya
from
    (
        select
            user_id,
            area, 
            sum(amount) as amount
        from
            sales
        group by
            user_id,
            area
     ) x
group by
     user_id

クエリ2の考え方ですが、抽出元のテーブル（クエリ２の中ではxと名前をつけている）から1行ごとにデータを取り出し、areaカラムの値を見ながら出力テーブル(selectの下から５行)の対応するカラムにテーブルxのamount値を入れ、それ以外のカラムは全てNULL を入れています。
それらを全ての行に対して実行し、最後に group by コマンド でuser_id 毎にまとめているという流れです。

最初は慣れないかもしれませんが、このような書き方で縦持ちデータを横持ちに変換することができます。

横持ちから縦持ちへの変換

次に、今抽出したデータを縦持ちに変換してみましょう。いま抽出したデータをそのまま使ってもいいですし、こちらからダウンロードしても構いません。

横持ちデータを縦持ちデータに変換する方法は簡単です。各列ごとに`user_id` と`amount`のデータを取り出し、 union all でつなげてしまえば完了です。

ただしその時に、3列目には手打ちで地名(area)を入力し、`as area` と書くようにしましょう。これがないと地名の情報が消えてしまいます。selectコマンドの中に、同じ地名を二度書く必要があるので、注意しましょう。

#クエリ３
select user_id, Ginza as amount, 'Ginza' as area from sales_table

union all

select user_id, Harajuku as amount, 'Harajuku' as area from sales_table

union all

select user_id, Shinjuku as amount, 'Shinjuku' as area from sales_table

union all

select user_id, Shibuya as amount, 'Shibuya' as area from sales_table

まとめ

今回は、SQLを使った縦持ちと横持ちのデータの変換方法についてお話しました。
やや複雑ですが、実務ではよく使う書き方になるので、ぜひ覚えておきましょう。