[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を武器にデータ活用時代を生き抜く