行列変換

諸々の事情でテーブルが微妙な正規化になっていることがある。
よく見かける例が、ユーザ情報のテーブルに、メアド1、メアド2…といったもの。

繰り返し項目が列ベースになっている困ったテーブルとの相互変換のメモ。

列ベースの困ったテーブル：

CREATE TABLE colbase (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  email1 VARCHAR(256) NOT NULL,
  email2 VARCHAR(256) NOT NULL,
  email3 VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO colbase(id, name, email1, email2, email3) VALUES(1, 'pen', 'one@example.com', 'two@example.com', 'three@example.com');

視覚的にはまあわかりやすいのだけど、検索のときに複数カラムを確認しないとならなかったり、メアドを4つ以上登録できるようにする場合にスキーマ変更が必要になったりと面倒が多い。

だからやっぱりちゃんと正規化したい。

CREATE TABLE user (
  id INT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE user_email (
  id INT,
  seq INT,
  email VARCHAR(256) NOT NULL,
  PRIMARY KEY (id, seq)
);
INSERT INTO user(id, name) VALUES(1, 'pen');
INSERT INTO user_email(id, seq, email) VALUES(1, 1, 'one@example.com');
INSERT INTO user_email(id, seq, email) VALUES(1, 2, 'two@example.com');
INSERT INTO user_email(id, seq, email) VALUES(1, 3, 'three@example.com');

リファクタするならこういうSQLになる。(列→行)

SELECT id, 1 AS seq, email1 FROM colbase WHERE email1 IS NOT NULL
UNION ALL
SELECT id, 2 AS seq, email2 FROM colbase WHERE email2 IS NOT NULL
UNION ALL
SELECT id, 3 AS seq, email3 FROM colbase WHERE email3 IS NOT NULL;

抽出の際に列ベースの形が必要ならこうやって出せる。(行→列)

SELECT id
       ,MAX(CASE WHEN seq = 1 THEN seq ELSE NULL END) AS email1
       ,MAX(CASE WHEN seq = 2 THEN seq ELSE NULL END) AS email2
       ,MAX(CASE WHEN seq = 3 THEN seq ELSE NULL END) AS email3
  FROM user_email
 GROUP BY id;

MAX()に特に意味はない。GROUP BYに対して何かしら集計関数を使う必要があるだけ。