[PostgreSQL]横持ちデータと縦持ちデータの変換

業務でデータ分析用途でSQLを書く必要があり、表題のような変換が必要になった。
それぞれのデータへの変換方法をメモする。なお、使用したDBMSはPostgreSQL 9.6。
PostgreSQLでのDB作成方法なども簡単にメモしておく。

DB登録とテーブル登録

任意のitem_idと、そのアイテムに基づく推薦データを持つテーブルを作成する

create database testdb;
create table recommend_item (item_id int PRIMARY KEY, recommend_item_1 int, recommend_item_2 int, recommend_item_3 int, recommend_item_4 int);

テストデータ登録

ランダムな値を登録する

INSERT INTO recommend_item (item_id, recommend_item_1, recommend_item_2, recommend_item_3, recommend_item_4) VALUES 
(1, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT),
(2, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT),
(3, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT),
(4, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT),
(5, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT, (random() * 10000)::INT);

現時点で作成されたデータは下記。

 item_id | recommend_item_1 | recommend_item_2 | recommend_item_3 | recommend_item_4 
---------+------------------+------------------+------------------+------------------
       1 |             3535 |             3085 |                8 |             3241
       2 |             2547 |             1186 |             6959 |             6764
       3 |             8174 |             5804 |             3546 |              716
       4 |             2563 |             6874 |             7527 |             8290
       5 |             2124 |             3318 |             2690 |             7319

行持ちのデータに変換する

変換のために連番のレコードを持つテーブルを別途作成する

create table pivot (id int);
INSERT INTO pivot (id) VALUES
(1),
(2),
(3),
(4);
select * FROM pivot;

 id
----
  1
  2
  3
  4

変換する

  • 横持ちのテーブルとpivotテーブルをクロスジョイン
    • 2つのテーブルの全ての行の組み合わせを得る
  • CASE文でpivotテーブルのidと、推薦アイテム末尾の数字が対応するカラムの値を抽出
    • 横持ちのテーブルを縦持ちのテーブルに変換する
  • item_id順にソートする
SELECT
    sub.*
FROM
(
    SELECT
        r.item_id,
        CASE p.id
            WHEN 1 THEN r.recommend_item_1
            WHEN 2 THEN r.recommend_item_2
            WHEN 3 THEN r.recommend_item_3
            WHEN 4 THEN r.recommend_item_4
        END AS r_item_id
    FROM recommend_item AS r
    CROSS JOIN pivot AS p
) sub
WHERE
    sub.r_item_id IS NOT NULL
ORDER BY
    sub.item_id,
    sub.r_item_id
;
 item_id | r_item_id
---------+-----------
       1 |         8
       1 |      3085
       1 |      3241
       1 |      3535
       2 |      1186
       2 |      2547
       2 |      6764
       2 |      6959
       3 |       716
       3 |      3546
       3 |      5804
       3 |      8174
       4 |      2563
       4 |      6874
       4 |      7527
       4 |      8290
       5 |      2124
       5 |      2690
       5 |      3318
       5 |      7319

縦持ちを横持ちにする

データを登録して作成したテーブル

 item_id | recommend_item_id
---------+----------------
       1 |            589
       1 |           6372
       1 |           4951
       1 |           5054
       2 |           2143
       2 |           1845
       2 |           8303
       2 |           5656
       3 |           6784
       3 |           1838
       3 |           8742
       3 |           6793
       4 |           5079
       4 |           1289
       4 |           7979
       4 |           2038
       5 |           8052
       5 |           6153
       5 |           7842
       5 |           1599

変換する

  • サブクエリでrecommend_item2テーブルの各行に連番をふる
    • ウインドウ関数row_number()で連番を振る
    • PARTITION BY でitem_id ごとの連番にする
      • GROUP BY と異なり集約はしない。
  • item_idで集約してcase句で横持ちに変換する
SELECT
    sub.item_id,
    max(CASE sub.seq WHEN 1 THEN recommend_item_id ELSE NULL END) AS recommend_item_1,
    max(CASE sub.seq WHEN 2 THEN recommend_item_id ELSE NULL END) AS recommend_item_2,
    max(CASE sub.seq WHEN 3 THEN recommend_item_id ELSE NULL END) AS recommend_item_3,
    max(CASE sub.seq WHEN 4 THEN recommend_item_id ELSE NULL END) AS recommend_item_4
FROM
(
    SELECT
        item_id,
        recommend_item_id,
        row_number() OVER (PARTITION by item_id) AS seq
    FROM recommend_item2
) sub
GROUP BY
    sub.item_id
 item_id | recommend_item_1 | recommend_item_2 | recommend_item_3 | recommend_item_4
---------+------------------+------------------+------------------+------------------
       1 |              589 |             6372 |             4951 |             5054
       2 |             2143 |             1845 |             8303 |             5656
       3 |             6784 |             1838 |             8742 |             6793
       4 |             5079 |             1289 |             7979 |             2038
       5 |             8052 |             6153 |             7842 |             1599

余談:MySQLでの変換方法

MySQLには連番を振るrow_number()がないため、各カラムを結合する方法がある。

select item_id, `recommend_item_1` as value from `recommend_item`
union all
select item_id, `recommend_item_2` as value from `recommend_item`
union all
select item_id, `recommend_item_3` as value from `recommend_item`
union all
select item_id, `recommend_item_4` as value from `recommend_item`
order by item_id, value

参考

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

17 + 3 =

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください