業務でデータ分析用途で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
