[SQL]データの縦持ち、横持ちを入れ替える
はじめに
弊社のデータ分析案件において、RedshiftにてSQLを実行してデータを抽出することが増えてまいりました。SQLはデータの抽出には強力なのですが、JavaやRubyなどのプログラミング言語と比べると独特であることも事実です。
例えば(PL/SQLなどは除いてですが)
- ループがない
- 変数に代入して次の処理に行くことができない
- メソッドやクラスに処理を分割できない
などです。
これらの特徴からSQLでデータ抽出を行う為には、JavaやRubyなどとは異なった手法を取る必要があります。そこで「JavaやRubyとかでは・・・というやり方でやるのだけど、SQLではどうやるのだろう」というケースについて、シリーズ形式で何回かに分けて取り上げたいと思います。
尚、SQLの基本的な構文(select、join、group byなど)については説明しません。またデータベースの種類に関わらず使用できるよう、できるだけ汎用的なSQLになるようにしたいと思います。ですが特定の関数を使う場合などは、この限りではありません。
動作環境について
動作環境にはPostgreSQL 9.3.5を使用していきます。
横持ちのデータを縦持ちに入れ替える
では最初のケースです。正規化されておらず、以下のように横持ちになっているデータがあるとします。
# qualifications_horizontalテーブル employee_id qualification_id_1 qualification_id_2 qualification_id_3 qualification_id_4 100 1000 200 3000 4000 101 1000 102 2000 3000 4000 103 3000 4000 104 1000 2000 4000
employee_id(従業員ID)毎のqualification_id1〜4(資格ID)が正規化されておらず横持ちになっています。これを以下のように正規化した縦持ちの形で抽出する方法についてです。
# 実行結果 employee_id qualification_id 100 1000 100 2000 100 3000 100 4000 101 1000 102 2000 102 3000 102 4000 103 3000 103 4000 104 1000 104 2000 104 4000
まずは整数型のカラムのみを持つピボットテーブルを作成し、1〜横持ちのカラム数(今回は1〜4)のレコードを登録します。
# pivotテーブル seq 1 2 3 4
後はSQLでの抽出です。
- ピボットテーブルと横持ちのテーブル(qualifications_horizontalテーブル)をcross join
- case句で横持ちのqualification_idを縦持ちに変換
- 値を持つ行のみをwhere句で絞り込み
を行います。SQLにすると以下のようになります。
select sub.* from ( select q.employee_id ,case p.seq when 1 then q.qualification_id_1 when 2 then q.qualification_id_2 when 3 then q.qualification_id_3 when 4 then q.qualification_id_4 end as qualification_id from qualifications_horizontal as q cross join pivot as p ) sub where sub.qualification_id is not null order by sub.employee_id ,sub.qualification_id ;
このSQLを実行すると、上記「実行結果」のように縦持ちになるはずです。
縦持ちのデータを横持ちに入れ替える
次に正規化された縦持ちのデータを、非正規化して横持ちにするケースについてです。集計処理は正規化して行うが、最終的にはお客様が横持ちの形で表示することを希望する場合などに使うかと思います。以下のように縦持ちしたデータがあるとします。
# qualifications_verticalテーブル employee_id qualification_id 100 1000 100 2000 100 3000 100 4000 101 1000 102 2000 102 3000 102 4000 103 3000 103 4000 104 1000 104 2000 104 4000
これを以下のように横持ちの形で抽出する方法についてです。
# 実行結果 employee_id qualification_id1 qualification_id2 qualification_id3 qualification_id4 100 1000 2000 3000 4000 101 1000 NULL NULL NULL 102 2000 3000 4000 NULL 103 3000 4000 NULL NULL 104 1000 2000 4000 NULL
これについては、SQLのみで実現できます。
- employee_id毎に行番号を付与するサブクエリをつくる
- サブクエリの値をcase句で横持ちに変換
を行います。SQLにすると以下のようになります。
select tmp.employee_id ,max(case tmp.seq when 1 then tmp.qualification_id else null end) as qualification_id1 ,max(case tmp.seq when 2 then tmp.qualification_id else null end) as qualification_id2 ,max(case tmp.seq when 3 then tmp.qualification_id else null end) as qualification_id3 ,max(case tmp.seq when 4 then tmp.qualification_id else null end) as qualification_id4 from ( select employee_id ,qualification_id ,row_number() over (partition by employee_id) as seq from qualifications_vertical ) tmp group by tmp.employee_id ;
このSQLを実行すると、上記「実行結果」のように横持ちになるはずです。
まとめ
ピボットテーブルを使用した縦持ちへの変換、行番号を付与しての横持ちへの変換については、なかなか自分で考えつかないかと思います(少なくとも自分は考えつきませんでした)。ですが事前に知っていれば、実案件への適用もできるかと思います。このような実案件に役立つhow toを取り上げていきたいと思います。
参考文献
今回の内容については、以下の本を参考にさせて頂きました。ありがとうございました。
10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く