SQL

SQLで縦持ちと横持ちを変形する

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

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

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

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

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

 

id : レコードID

user_id : ユーザーID (ユーザーは4人)

area : 購入地域(購入知識は4箇所)

amount : 購入金額

 

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

user_idGinzaHarajukuShinjukuShibuya
 A xxx xxx xxx xxx
 xxx xxx xxx xxx
 xxx xxx xxx xxx
 xxx xxxxxx  xxx

 

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

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

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

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

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

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

 

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

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

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

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

まとめ

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

12/11(水)セミナーを開催いたします!



こんなお悩みや課題はありませんか?
✓データ活用するためには何を始めればよいかわからない
✓データマネジメントを行うために必要なことが知りたい
✓実績のある会社にAI構築をお願いしたいがなかなか見つからない

こんなお悩みをお持ちの方に向けて、12月11日(水)表参道にて、無料セミナーを開催いたします。
今回はSupership グループのSupership株式会社・DATUM STUDIO株式会社・ちゅらデータ株式会社の3社で開催します。

▼▼▼▼詳細は以下バナーをクリック!▼▼▼▼

このページをシェアする: