CASEを使って行列を置き換える方法

やりたいこと

prefecture	date	temperature
東京都	2016-11-01	10
東京都	2016-11-02	11
東京都	2016-11-03	10
東京都	2016-11-04	9
大阪府	2016-11-01	12
大阪府	2016-11-02	11
大阪府	2016-11-03	10
大阪府	2016-11-04	10

みたいなデータがあったときに

prefecture	2016-11-01	2016-11-02	2016-11-03	2016-11-04
東京都	10	11	10	9
大阪府	12	11	10	10

このような形にしたいケースがあると思います(グラフ化、集計 etc...)。
これをSQLのみを使ってうまく実現する方法を紹介します。

やり方

まず、テーブルを作ってみます。

CREATE TABLE `temperatures` (
 `prefecture` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `date` date NOT NULL,
 `temperature` int(11) NOT NULL,
 PRIMARY KEY (`prefecture`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

データを入れます。

INSERT INTO temperatures VALUES
("東京都", "2016-11-01", 10),
("東京都", "2016-11-02", 11),
("東京都", "2016-11-03", 10),
("東京都", "2016-11-04", 9),
("大阪府", "2016-11-01", 12),
("大阪府", "2016-11-02", 11),
("大阪府", "2016-11-03", 10),
("大阪府", "2016-11-04", 10)

ここから日付ごとのフィールドを作って、値を該当するtemperatureにしていく必要があります。
CASEを使ってその形を作ります。

SELECT prefecture, 
CASE WHEN `date` = '2016-11-01' THEN temperature ELSE NULL END AS '2016-11-01',
CASE WHEN `date` = '2016-11-02' THEN temperature ELSE NULL END AS '2016-11-02',
CASE WHEN `date` = '2016-11-03' THEN temperature ELSE NULL END AS '2016-11-03',
CASE WHEN `date` = '2016-11-04' THEN temperature ELSE NULL END AS '2016-11-04'
FROM `temperatures`

取得結果が以下のようになります。

prefecture	2016-11-01	2016-11-02	2016-11-03	2016-11-04
大阪府	12	NULL	NULL	NULL
大阪府	NULL	11	NULL	NULL
大阪府	NULL	NULL	10	NULL
大阪府	NULL	NULL	NULL	10
東京都	10	NULL	NULL	NULL
東京都	NULL	11	NULL	NULL
東京都	NULL	NULL	10	NULL
東京都	NULL	NULL	NULL	9

あとはこの結果を利用してprefectureごとにまとめてあげればOKです。

SELECT t1.prefecture,
SUM(t1.`2016-11-01`) AS '2016-11-01',
SUM(t1.`2016-11-02`) AS '2016-11-02',
SUM(t1.`2016-11-03`) AS '2016-11-03',
SUM(t1.`2016-11-04`) AS '2016-11-04'
FROM (
SELECT prefecture, 
CASE WHEN `date` = '2016-11-01' THEN temperature ELSE NULL END AS '2016-11-01',
CASE WHEN `date` = '2016-11-02' THEN temperature ELSE NULL END AS '2016-11-02',
CASE WHEN `date` = '2016-11-03' THEN temperature ELSE NULL END AS '2016-11-03',
CASE WHEN `date` = '2016-11-04' THEN temperature ELSE NULL END AS '2016-11-04'
FROM `temperatures`
)t1 GROUP BY prefecture

prefecture	2016-11-01	2016-11-02	2016-11-03	2016-11-04
東京都	10	11	10	9
大阪府	12	11	10	10

これで目的のデータを無事に取得することができました。

補足

• 今回はprefecture, dateでuniqueにしてあるのでシンプルな形式になっていますが、 集計対象がuniqueになってない場合でもCASE組むところで集計関数を使って欲しいデータを出すことも可能です。
• CASEが膨大になる場合は作るの大変なので、SQLの生成を自動化できるようプログラム組んであげるといいと思います。ループ回すだけなのですぐ作れます。
• 日毎のデータを今回はターゲットにしていますが、"A(prefecture)-B(date)の組み合わせに紐づくC(temperature)というデータ構造"であれば同じことができるので他にも応用できると思います。

まとめ

データ抽出周りは日次のタスクになってしまうことも多く、見直してみるとかなり時間を使ってしまっていることもあるので、手間かかってるなと感じたらまず上記の方法を用いて簡易化すると改善に繋がります(もちろん最終的には人の手が全くかからないように自動化する方がいいです。)。