MySQL データを横に倒して表示させるSQL
CASE
database
DB2
db2
Infinidb
Infobright
innodb
MySQL
Oracle
sql
WITH句
サマリ
チューニング
テンポラリ表
倒置
共通表式
拡張CASE
月別
条件分岐
横に並べる
横もち
横倒し
どうタイトルをつければよいのやら悩んだ。。けど以下のようなデータを、横に並べて一覧、結果を返すことをするには、SQLでこう書くのだよっていうサンプルです。IBM DB2を利用しているときも悩みましたが、MySQLはもっと悩みました。
このデータは、1950年から1960年までの日本全国の総人口数になります。人口推移
| 1950/10/1 | 佐賀県 | 945082 |
| 1950/10/1 | 長崎県 | 1645492 |
| 1950/10/1 | 熊本県 | 1827582 |
| 1950/10/1 | 大分県 | 1252999 |
| 1950/10/1 | 宮崎県 | 1091427 |
| 1950/10/1 | 鹿児島県 | 1804118 |
| 1950/10/1 | 沖縄県 | 914937 |
| 1955/10/1 | 北海道 | 4773087 |
| 1955/10/1 | 青森県 | 1382523 |
| 1955/10/1 | 岩手県 | 1427097 |
| 1955/10/1 | 宮城県 | 1727065 |
MySQLのテーブル定義的にはこんな感じです。
[sql]
show create table jinko_suii;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jinko_suii | CREATE TABLE jinko_suii (
wymd datetime DEFAULT NULL,
todohuken varchar(100) DEFAULT NULL,
jinko bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[/sql]
上記のレコードを、以下のような結果にするためにはどうすればいいのか？
| 1950年 | 1955年 | 1960年 | |
| 日本全国 | XXXX | XXXX | XXXX |
を実現したのが以下のSQLです。
[sql]
select '日本全国' as al_state, sum(case X.wyear when 1950 then X.jinko end) as Y1950, sum(case X.wyear when 1955 then X.jinko end) as Y1955, sum(case X.wyear when 1960 then X.jinko end) as Y1960 from ( select year(wymd) as wyear, sum(jinko) as jinko from jinko_suii group by year(wymd) ) as X;
[/sql]
以下のような結果になります。
[sql]
mysql> +--------------+----------+----------+----------+ | al_state | Y1950 | Y1955 | Y1960 | +--------------+----------+----------+----------+ | 日本全国 | 84114574 | 90076594 | 88416337 | +--------------+----------+----------+----------+ 1 row in set (0.00 sec)
[/sql]
データを横に並べて表示させるために、共通表式と、拡張CASE文を利用すれば、実現が可能です。今回の例では、上限のない範囲での横倒し結果なのであまり参考にならないかもしれませんが、たとえば、月とか時間単位等の上限のあるデータを横に並べて表示したい場合などには、上記方法が適していると思います。実業務においてこんなに単純な構成ではないとは思いますが、複雑な条件で抽出するなんて場合も上記の基本を把握しておけば、どんな形でも応用がききます。 ただし、上記クエリを実業務でつかう場所は、DWH、参照専用DB等で作業を行ったほうが良いかと思います。それなりに負荷は高いです。
おまけ
上記例は、日本全国でくくりました、では、各都道府県で上記を結果を実現するには、どのようなクエリを書けばよいのか？こんな感じに記述すれば良いのです。
[sql]
select X.todohuken as todohuken, sum(case X.wyear when 1950 then X.jinko end) as Y1950, sum(case X.wyear when 1955 then X.jinko end) as Y1955, sum(case X.wyear when 1960 then X.jinko end) as Y1960 from ( select todohuken as todohuken, year(wymd) as wyear, sum(jinko) as jinko from jinko_suii group by todohuken, year(wymd)) as X group by X.todohuken;
[/sql]
上記クエリの結果は以下のようになります。
[sql]
+--------------+---------+---------+---------+ | todohuken | Y1950 | Y1955 | Y1960 | +--------------+---------+---------+---------+ | 三重県 | 1461197 | 1485582 | 1485054 | | 京都府 | 1832934 | 1935161 | 1993403 | | 佐賀県 | 945082 | 973749 | 942874 | | 兵庫県 | 3309935 | 3620947 | 3906487 | | 北海道 | 4295567 | 4773087 | 5039206 | | 千葉県 | 2139037 | 2205060 | 2306010 | | 和歌山県 | 982113 | 1006819 | 1002191 | | 埼玉県 | 2146445 | 2262623 | 2430871 | | 大分県 | 1252999 | 1277199 | 1239655 | | 大阪府 | 3857047 | 4618308 | 5504746 | | 奈良県 | 763883 | 776861 | 781058 |
[/sql]
この手法は、MySQLだけでなく、IBM DB2,Oracle,SQL Server なんでも使えるかと思いますのでぜひ活用してください。
