分析関数の練習
情報の入手もと http://www.stat.go.jp/data/jinsui/2014np/zuhyou/05k26-2.xls
PostgreSQL 9.3 Schema Setup:
CREATE TABLE poplulationh26
("id" int, "pref" varchar(4), "men" int, "women" int, "men1" int, "women1" int)
;
INSERT INTO poplulationh26
("id", "pref", "men", "women", "men1", "women1")
VALUES
(01, '北海道', 2545, 2855, 2537, 2844),
(02, '青森県', 620, 701, 619, 699),
(03, '岩手県', 614, 670, 613, 667),
(04, '宮城県', 1134, 1194, 1129, 1187),
(05, '秋田県', 486, 551, 485, 549),
(06, '山形県', 543, 588, 542, 584),
(07, '福島県', 944, 991, 942, 986),
(08, '茨城県', 1455, 1464, 1437, 1445),
(09, '栃木県', 984, 996, 972, 982),
(10, '群馬県', 973, 1003, 956, 984),
(11, '埼玉県', 3622, 3617, 3578, 3565),
(12, '千葉県', 3082, 3115, 3046, 3068),
(13, '東京都', 6608, 6782, 6443, 6600),
(14, '神奈川県', 4548, 4548, 4488, 4478),
(15, '新潟県', 1119, 1194, 1115, 1187),
(16, '富山県', 516, 554, 512, 548),
(17, '石川県', 560, 596, 555, 591),
(18, '福井県', 382, 407, 378, 402),
(19, '山梨県', 411, 430, 407, 423),
(20, '長野県', 1025, 1084, 1015, 1069),
(21, '岐阜県', 988, 1053, 973, 1035),
(22, '静岡県', 1824, 1881, 1798, 1853),
(23, '愛知県', 3725, 3731, 3651, 3647),
(24, '三重県', 890, 936, 874, 920),
(25, '滋賀県', 699, 716, 689, 707),
(26, '京都府', 1250, 1359, 1232, 1338),
(27, '大阪府', 4256, 4579, 4183, 4495),
(28, '兵庫県', 2645, 2896, 2611, 2857),
(29, '奈良県', 649, 726, 645, 722),
(30, '和歌山県', 457, 515, 455, 512),
(31, '鳥取県', 274, 300, 273, 298),
(32, '島根県', 334, 363, 332, 360),
(33, '岡山県', 923, 1001, 916, 991),
(34, '広島県', 1367, 1466, 1352, 1449),
(35, '山口県', 665, 744, 660, 737),
(36, '徳島県', 363, 401, 362, 398),
(37, '香川県', 473, 508, 470, 504),
(38, '愛媛県', 657, 738, 654, 734),
(39, '高知県', 347, 391, 345, 389),
(40, '福岡県', 2403, 2688, 2381, 2665),
(41, '佐賀県', 393, 442, 392, 439),
(42, '長崎県', 648, 739, 645, 735),
(43, '熊本県', 844, 950, 840, 945),
(44, '大分県', 554, 617, 550, 613),
(45, '宮崎県', 523, 591, 522, 588),
(46, '鹿児島県', 780, 888, 778, 883),
(47, '沖縄県', 697, 724, 692, 720)
;
Query 1:
select
pref
,(men1 + women1) pop
,case pref
when '北海道' then '北海道地方'
when '青森県' then '東北地方'
when '岩手県' then '東北地方'
when '宮城県' then '東北地方'
when '秋田県' then '東北地方'
when '山形県' then '東北地方'
when '福島県' then '東北地方'
when '茨城県' then '関東地方'
when '栃木県' then '関東地方'
when '群馬県' then '関東地方'
when '埼玉県' then '関東地方'
when '千葉県' then '関東地方'
when '東京都' then '関東地方'
when '神奈川県' then '関東地方'
when '新潟県' then '中部地方'
when '富山県' then '中部地方'
when '石川県' then '中部地方'
when '福井県' then '中部地方'
when '山梨県' then '中部地方'
when '長野県' then '中部地方'
when '岐阜県' then '中部地方'
when '静岡県' then '中部地方'
when '愛知県' then '中部地方'
when '三重県' then '近畿地方'
when '滋賀県' then '近畿地方'
when '京都府' then '近畿地方'
when '大阪府' then '近畿地方'
when '兵庫県' then '近畿地方'
when '奈良県' then '近畿地方'
when '和歌山県' then '近畿地方'
when '鳥取県' then '中国地方'
when '島根県' then '中国地方'
when '岡山県' then '中国地方'
when '広島県' then '中国地方'
when '山口県' then '中国地方'
when '徳島県' then '四国地方'
when '香川県' then '四国地方'
when '愛媛県' then '四国地方'
when '高知県' then '四国地方'
when '福岡県' then '九州地方'
when '佐賀県' then '九州地方'
when '長崎県' then '九州地方'
when '熊本県' then '九州地方'
when '大分県' then '九州地方'
when '宮崎県' then '九州地方'
when '鹿児島県' then '九州地方'
when '沖縄県' then '沖縄地方'
else NULL
end region
,rank() over(
partition by
case pref
when '北海道' then '北海道地方'
when '青森県' then '東北地方'
when '岩手県' then '東北地方'
when '宮城県' then '東北地方'
when '秋田県' then '東北地方'
when '山形県' then '東北地方'
when '福島県' then '東北地方'
when '茨城県' then '関東地方'
when '栃木県' then '関東地方'
when '群馬県' then '関東地方'
when '埼玉県' then '関東地方'
when '千葉県' then '関東地方'
when '東京都' then '関東地方'
when '神奈川県' then '関東地方'
when '新潟県' then '中部地方'
when '富山県' then '中部地方'
when '石川県' then '中部地方'
when '福井県' then '中部地方'
when '山梨県' then '中部地方'
when '長野県' then '中部地方'
when '岐阜県' then '中部地方'
when '静岡県' then '中部地方'
when '愛知県' then '中部地方'
when '三重県' then '近畿地方'
when '滋賀県' then '近畿地方'
when '京都府' then '近畿地方'
when '大阪府' then '近畿地方'
when '兵庫県' then '近畿地方'
when '奈良県' then '近畿地方'
when '和歌山県' then '近畿地方'
when '鳥取県' then '中国地方'
when '島根県' then '中国地方'
when '岡山県' then '中国地方'
when '広島県' then '中国地方'
when '山口県' then '中国地方'
when '徳島県' then '四国地方'
when '香川県' then '四国地方'
when '愛媛県' then '四国地方'
when '高知県' then '四国地方'
when '福岡県' then '九州地方'
when '佐賀県' then '九州地方'
when '長崎県' then '九州地方'
when '熊本県' then '九州地方'
when '大分県' then '九州地方'
when '宮崎県' then '九州地方'
when '鹿児島県' then '九州地方'
when '沖縄県' then '沖縄地方'
else NULL
end
order by (men1 + women1) desc) from poplulationh26
| pref | pop | region | rank |
|------|-------|--------|------|
| 広島県 | 2801 | 中国地方 | 1 |
| 岡山県 | 1907 | 中国地方 | 2 |
| 山口県 | 1397 | 中国地方 | 3 |
| 島根県 | 692 | 中国地方 | 4 |
| 鳥取県 | 571 | 中国地方 | 5 |
| 愛知県 | 7298 | 中部地方 | 1 |
| 静岡県 | 3651 | 中部地方 | 2 |
| 新潟県 | 2302 | 中部地方 | 3 |
| 長野県 | 2084 | 中部地方 | 4 |
| 岐阜県 | 2008 | 中部地方 | 5 |
| 石川県 | 1146 | 中部地方 | 6 |
| 富山県 | 1060 | 中部地方 | 7 |
| 山梨県 | 830 | 中部地方 | 8 |
| 福井県 | 780 | 中部地方 | 9 |
| 福岡県 | 5046 | 九州地方 | 1 |
| 熊本県 | 1785 | 九州地方 | 2 |
| 鹿児島県 | 1661 | 九州地方 | 3 |
| 長崎県 | 1380 | 九州地方 | 4 |
| 大分県 | 1163 | 九州地方 | 5 |
| 宮崎県 | 1110 | 九州地方 | 6 |
| 佐賀県 | 831 | 九州地方 | 7 |
| 北海道 | 5381 | 北海道地方 | 1 |
| 愛媛県 | 1388 | 四国地方 | 1 |
| 香川県 | 974 | 四国地方 | 2 |
| 徳島県 | 760 | 四国地方 | 3 |
| 高知県 | 734 | 四国地方 | 4 |
| 宮城県 | 2316 | 東北地方 | 1 |
| 福島県 | 1928 | 東北地方 | 2 |
| 青森県 | 1318 | 東北地方 | 3 |
| 岩手県 | 1280 | 東北地方 | 4 |
| 山形県 | 1126 | 東北地方 | 5 |
| 秋田県 | 1034 | 東北地方 | 6 |
| 沖縄県 | 1412 | 沖縄地方 | 1 |
| 大阪府 | 8678 | 近畿地方 | 1 |
| 兵庫県 | 5468 | 近畿地方 | 2 |
| 京都府 | 2570 | 近畿地方 | 3 |
| 三重県 | 1794 | 近畿地方 | 4 |
| 滋賀県 | 1396 | 近畿地方 | 5 |
| 奈良県 | 1367 | 近畿地方 | 6 |
| 和歌山県 | 967 | 近畿地方 | 7 |
| 東京都 | 13043 | 関東地方 | 1 |
| 神奈川県 | 8966 | 関東地方 | 2 |
| 埼玉県 | 7143 | 関東地方 | 3 |
| 千葉県 | 6114 | 関東地方 | 4 |
| 茨城県 | 2882 | 関東地方 | 5 |
| 栃木県 | 1954 | 関東地方 | 6 |
| 群馬県 | 1940 | 関東地方 | 7 |
