Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL データを横に倒して表示させるSQL

どうタイトルをつければよいのやら悩んだ。。けど以下のようなデータを、横に並べて一覧、結果を返すことをするには、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 なんでも使えるかと思いますのでぜひ活用してください。