記入日:2023-01-26 編集日:2023-01-26
SQL見るだけクエリ。今回は、前回書いたFIND_IN_SETを使って、順位番号をつけるSQLを応用してみます。 前回と異なるのは、順位付けする値を、GROUP BYで取得した値でおこないます。
この記事を読むと(約5分)
SQLを使って特定の項目に対して、全体の中で何番目の数値に値するのか、ランキングをふっていく方法がわかります。
特定の値はカラムとして存在しているものではなく、GROUP BYを使って合計(SUM)した値を使います。
今回も使う関数的には、前回同様、FIND_IN_SETやGROUP_CONCATです。 これらと副問合せ(サブクエリ)などを組み合わせて、ランキングの値を導きます。
FIND_IN_SETやGROUP_CONCATの考え方は前回の内容をご確認いただくとイメージしやすいかと思います。
具体的に今回取得したい内容は、各空港が属する都道府県毎の国内線利用者数を取得し、その値がランキング的に何位に値するのかを取得しようと思います。
以下の様なテーブルを用意しました。(テーブル名:sql_airport)
id | airport_code | airport_name | pref_code | pref_name | passengers_sum | domestic_sum | international_sum |
---|---|---|---|---|---|---|---|
1 | 22827 | 羽田空港(東京) | 13 | 東京都 | 86920293 | 68382811 | 18537482 |
2 | 29110 | 成田空港(東京) | 12 | 千葉県 | 42413928 | 7642779 | 34771149 |
3 | 25892 | 関西空港(大阪) | 27 | 大阪府 | 31807820 | 6981770 | 24826050 |
4 | 29655 | 福岡空港 | 40 | 福岡県 | 24679617 | 18281552 | 6398065 |
5 | 29654 | 新千歳空港(札幌) | 1 | 北海道 | 24599263 | 20732744 | 3866519 |
6 | 29657 | 那覇空港(沖縄) | 47 | 沖縄県 | 21761828 | 18080998 | 3680830 |
7 | 25856 | 伊丹空港(大阪) | 27 | 大阪府 | 16504209 | 16504209 | 0 |
8 | 29411 | 中部国際空港(名古屋) | 23 | 愛知県 | 13460149 | 6676623 | 6783526 |
9 | 28935 | 鹿児島空港 | 46 | 鹿児島県 | 6075210 | 5663539 | 411671 |
10 | 21045 | 仙台空港 | 4 | 宮城県 | 3855387 | 3462344 | 393043 |
11 | 28628 | 熊本空港 | 43 | 熊本県 | 3492188 | 3321313 | 170875 |
12 | 29656 | 宮崎空港 | 45 | 宮崎県 | 3410361 | 3312416 | 97945 |
13 | 29150 | 神戸空港 | 28 | 兵庫県 | 3362720 | 3362671 | 49 |
14 | 28535 | 長崎空港 | 42 | 長崎県 | 3360170 | 3275270 | 84900 |
15 | 27367 | 広島空港 | 34 | 広島県 | 3166572 | 2821076 | 345496 |
16 | 27893 | 松山空港 | 38 | 愛媛県 | 3152419 | 3054961 | 97458 |
17 | 29116 | 石垣空港 | 47 | 沖縄県 | 2614822 | 2515210 | 99612 |
18 | 27725 | 高松空港 | 37 | 香川県 | 2152430 | 1815458 | 336972 |
19 | 28743 | 大分空港 | 44 | 大分県 | 1982377 | 1876887 | 105490 |
20 | 23691 | 小松空港 | 17 | 石川県 | 1887535 | 1653086 | 234449 |
21 | 29115 | 宮古空港 | 47 | 沖縄県 | 1803490 | 1803490 | 0 |
22 | 20443 | 函館空港 | 1 | 北海道 | 1800577 | 1632697 | 167880 |
23 | 28133 | 北九州空港 | 40 | 福岡県 | 1753525 | 1450606 | 302919 |
24 | 27970 | 高知空港 | 39 | 高知県 | 1656279 | 1653971 | 2308 |
25 | 27053 | 岡山空港 | 33 | 岡山県 | 1611958 | 1312970 | 298988 |
26 | 21138 | 秋田空港 | 5 | 秋田県 | 1380863 | 1369033 | 11830 |
27 | 20620 | 青森空港 | 2 | 青森県 | 1250569 | 1178205 | 72364 |
28 | 27649 | 徳島空港 | 36 | 徳島県 | 1218852 | 1210359 | 8493 |
29 | 24034 | 新潟空港 | 15 | 新潟県 | 1201419 | 1063706 | 137713 |
30 | 20013 | 旭川空港 | 1 | 北海道 | 1158948 | 1109517 | 49431 |
31 | 26923 | 出雲空港 | 32 | 島根県 | 1051155 | 1049260 | 1895 |
32 | 27584 | 山口宇部空港 | 35 | 山口県 | 1028166 | 1014939 | 13227 |
33 | 25079 | 名古屋空港 | 23 | 愛知県 | 942753 | 942062 | 691 |
34 | 29114 | 奄美空港 | 46 | 鹿児島県 | 891990 | 891990 | 0 |
35 | 20178 | 釧路空港 | 1 | 北海道 | 866970 | 866198 | 772 |
36 | 20570 | 女満別空港 | 1 | 北海道 | 860458 | 858500 | 1958 |
37 | 29445 | 茨城空港 | 8 | 茨城県 | 822208 | 653265 | 168943 |
38 | 29152 | 佐賀空港 | 41 | 佐賀県 | 813082 | 610419 | 202663 |
39 | 29148 | 静岡空港 | 22 | 静岡県 | 805195 | 488053 | 317142 |
40 | 20106 | 帯広空港 | 1 | 北海道 | 701557 | 700943 | 614 |
41 | 27033 | 米子空港 | 31 | 鳥取県 | 692137 | 604792 | 87345 |
42 | 24723 | 富山空港 | 16 | 富山県 | 575172 | 455614 | 119558 |
43 | 20908 | 花巻空港 | 3 | 岩手県 | 516742 | 470076 | 46666 |
44 | 29706 | 岩国空港 | 35 | 山口県 | 513750 | 513750 | 0 |
45 | 21335 | 庄内空港 | 6 | 山形県 | 442566 | 437072 | 5494 |
46 | 26881 | 鳥取空港 | 31 | 鳥取県 | 417740 | 410892 | 6848 |
47 | 21393 | 山形空港 | 6 | 山形県 | 366678 | 338653 | 28025 |
48 | 20772 | 三沢空港 | 2 | 青森県 | 309527 | 309527 | 0 |
49 | 21552 | 福島空港 | 7 | 福島県 | 282437 | 255617 | 26820 |
50 | 20081 | 丘珠空港(札幌) | 1 | 北海道 | 277425 | 277425 | 0 |
51 | 29126 | 久米島空港 | 47 | 沖縄県 | 264524 | 264524 | 0 |
52 | 29119 | 対馬空港 | 42 | 長崎県 | 257687 | 257687 | 0 |
53 | 20428 | 根室中標津空港 | 1 | 北海道 | 214875 | 214875 | 0 |
54 | 29113 | 八丈島空港 | 13 | 東京都 | 213569 | 213569 | 0 |
55 | 29123 | 徳之島空港 | 46 | 鹿児島県 | 207542 | 207542 | 0 |
56 | 20613 | 稚内空港 | 1 | 北海道 | 203654 | 203654 | 0 |
57 | 26812 | 南紀白浜空港 | 30 | 和歌山県 | 182158 | 182158 | 0 |
58 | 29153 | 能登空港 | 17 | 石川県 | 174544 | 172929 | 1615 |
59 | 29118 | 五島福江空港 | 42 | 長崎県 | 168974 | 168974 | 0 |
60 | 29147 | 大館能代空港 | 5 | 秋田県 | 159214 | 159214 | 0 |
61 | 24326 | 松本空港 | 20 | 長野県 | 155898 | 153676 | 2222 |
62 | 26938 | 萩・石見空港 | 32 | 島根県 | 152725 | 152725 | 0 |
63 | 29122 | 屋久島空港 | 46 | 鹿児島県 | 145973 | 145973 | 0 |
64 | 29124 | 沖永良部空港 | 46 | 鹿児島県 | 119636 | 119636 | 0 |
65 | 30011 | 下地島空港 | 47 | 沖縄県 | 109246 | 92000 | 17246 |
66 | 29127 | 与那国空港 | 47 | 沖縄県 | 104463 | 104463 | 0 |
67 | 29141 | 調布飛行場 | 13 | 東京都 | 97041 | 97041 | 0 |
68 | 29128 | 喜界空港 | 46 | 鹿児島県 | 89729 | 89729 | 0 |
69 | 29121 | 種子島空港 | 46 | 鹿児島県 | 88513 | 88513 | 0 |
70 | 29125 | 与論空港 | 46 | 鹿児島県 | 78227 | 78227 | 0 |
71 | 20107 | オホーツク紋別空港 | 1 | 北海道 | 77299 | 77299 | 0 |
72 | 29117 | 隠岐空港 | 32 | 島根県 | 63732 | 63732 | 0 |
73 | 29135 | 南大東空港 | 47 | 沖縄県 | 47871 | 47871 | 0 |
74 | 29129 | 利尻空港 | 1 | 北海道 | 45183 | 45183 | 0 |
75 | 29136 | 多良間空港 | 47 | 沖縄県 | 44809 | 44809 | 0 |
76 | 29313 | 天草空港 | 43 | 熊本県 | 43982 | 43982 | 0 |
77 | 26436 | 但馬空港 | 28 | 兵庫県 | 42105 | 42105 | 0 |
78 | 29120 | 壱岐空港 | 42 | 長崎県 | 34992 | 34992 | 0 |
79 | 29142 | 新島空港 | 13 | 東京都 | 32343 | 32343 | 0 |
80 | 29112 | 三宅島空港 | 13 | 東京都 | 29163 | 29163 | 0 |
81 | 29111 | 大島空港 | 13 | 東京都 | 24539 | 24539 | 0 |
82 | 29143 | 神津島空港 | 13 | 東京都 | 22638 | 22638 | 0 |
83 | 29134 | 北大東空港 | 47 | 沖縄県 | 21651 | 21651 | 0 |
84 | 29131 | 奥尻空港 | 1 | 北海道 | 11881 | 11881 | 0 |
85 | 30009 | 粟国空港 | 47 | 沖縄県 | 850 | 850 | 0 |
86 | 30010 | 慶良間空港 | 47 | 沖縄県 | 193 | 193 | 0 |
87 | 30005 | 小値賀空港 | 42 | 長崎県 | 5 | 5 | 0 |
88 | 30007 | 岡南飛行場 | 33 | 岡山県 | 2 | 2 | 0 |
89 | 30001 | 礼文空港 | 1 | 北海道 | 0 | 0 | 0 |
90 | 30002 | 佐渡空港 | 15 | 新潟県 | 0 | 0 | 0 |
91 | 30003 | 福井空港 | 18 | 福井県 | 0 | 0 | 0 |
92 | 30004 | 八尾空港 | 27 | 大阪府 | 0 | 0 | 0 |
93 | 30006 | 上五島空港 | 42 | 長崎県 | 0 | 0 | 0 |
94 | 30008 | 大分県央飛行場 | 44 | 大分県 | 0 | 0 | 0 |
95 | 30012 | 伊江島空港 | 47 | 沖縄県 | 0 | 0 | 0 |
96 | 30013 | 波照間空港 | 47 | 沖縄県 | 0 | 0 | 0 |
テーブルの情報を取得するSQLに関して、注目する部分は、以下の9~24行目です。
SELECT pref_code,pref_name,SUM(domestic_sum) AS total,
FIND_IN_SET(SUM(domestic_sum),(SELECT GROUP_CONCAT(total)FROM(SELECT SUM(domestic_sum) AS total FROM sql_airport GROUP BY pref_name ORDER BY total DESC) AS total_array)) AS rank FROM sql_airport GROUP BY pref_name ORDER BY pref_code ASC
大まかな流れとしては、
【以下を取得】
★都道府県のコード、
★都道府県名、
★都道府県別の国内線利用者数、
★順位
→都道府県別の国内線利用者数の全ての数値から、
対象の都道府県の数字が何番目にあるか取得
→都道府県別の国内線利用者数を
計算して数の大きい順に並べる
【sql_airportテーブルから取得】
【都道府県毎に束ねる】
【都道府県コードの小さい順に並べる】
という感じです。
try { include_once '../model/class.php'; // データベース接続 $database = new Database(); $db = $database->connect(); // SQLを準備し、パラメータをバインドする $query = 'SELECT pref_code, pref_name, SUM(domestic_sum) AS total, FIND_IN_SET(SUM(domestic_sum),( SELECT GROUP_CONCAT(total) FROM( SELECT SUM(domestic_sum) AS total FROM sql_airport GROUP BY pref_name ORDER BY total DESC ) AS total_array )) AS rank FROM sql_airport GROUP BY pref_name ORDER BY pref_code ASC'; $stmt = $db->prepare($query); // 実行する $stmt->execute(); // 結果を取得する $result = $stmt->fetchAll(PDO::FETCH_ASSOC); print_r($result); // データベースから切断する $database->disconnect(); } catch(PDOException $e) { echo $e->getMessage(); }
これを実行すると、二次元連想配列という形式で、情報を取得することができます。
Array ( [0] => Array ( [pref_code] => 1 [pref_name] => 北海道 [total] => 26730916 [rank] => 2 ) [1] => Array ( [pref_code] => 2 [pref_name] => 青森県 [total] => 1487732 [rank] => 22 ) [2] => Array ( [pref_code] => 3 [pref_name] => 岩手県 [total] => 470076 [rank] => 32 ) [3] => Array ( [pref_code] => 4 [pref_name] => 宮城県 [total] => 3462344 [rank] => 10 ) [4] => Array ( [pref_code] => 5 [pref_name] => 秋田県 [total] => 1528247 [rank] => 21 ) [5] => Array ( [pref_code] => 6 [pref_name] => 山形県 [total] => 775725 [rank] => 28 ) [6] => Array ( [pref_code] => 7 [pref_name] => 福島県 [total] => 255617 [rank] => 34 ) [7] => Array ( [pref_code] => 8 [pref_name] => 茨城県 [total] => 653265 [rank] => 29 ) [8] => Array ( [pref_code] => 12 [pref_name] => 千葉県 [total] => 7642779 [rank] => 6 ) [9] => Array ( [pref_code] => 13 [pref_name] => 東京都 [total] => 68802104 [rank] => 1 ) [10] => Array ( [pref_code] => 15 [pref_name] => 新潟県 [total] => 1063706 [rank] => 26 ) [11] => Array ( [pref_code] => 16 [pref_name] => 富山県 [total] => 455614 [rank] => 33 ) [12] => Array ( [pref_code] => 17 [pref_name] => 石川県 [total] => 1826015 [rank] => 17 ) [13] => Array ( [pref_code] => 18 [pref_name] => 福井県 [total] => 0 [rank] => 37 ) [14] => Array ( [pref_code] => 20 [pref_name] => 長野県 [total] => 153676 [rank] => 36 ) [15] => Array ( [pref_code] => 22 [pref_name] => 静岡県 [total] => 488053 [rank] => 31 ) [16] => Array ( [pref_code] => 23 [pref_name] => 愛知県 [total] => 7618685 [rank] => 7 ) [17] => Array ( [pref_code] => 27 [pref_name] => 大阪府 [total] => 23485979 [rank] => 3 ) [18] => Array ( [pref_code] => 28 [pref_name] => 兵庫県 [total] => 3404776 [rank] => 11 ) [19] => Array ( [pref_code] => 30 [pref_name] => 和歌山県 [total] => 182158 [rank] => 35 ) [20] => Array ( [pref_code] => 31 [pref_name] => 鳥取県 [total] => 1015684 [rank] => 27 ) [21] => Array ( [pref_code] => 32 [pref_name] => 島根県 [total] => 1265717 [rank] => 24 ) [22] => Array ( [pref_code] => 33 [pref_name] => 岡山県 [total] => 1312972 [rank] => 23 ) [23] => Array ( [pref_code] => 34 [pref_name] => 広島県 [total] => 2821076 [rank] => 15 ) [24] => Array ( [pref_code] => 35 [pref_name] => 山口県 [total] => 1528689 [rank] => 20 ) [25] => Array ( [pref_code] => 36 [pref_name] => 徳島県 [total] => 1210359 [rank] => 25 ) [26] => Array ( [pref_code] => 37 [pref_name] => 香川県 [total] => 1815458 [rank] => 18 ) [27] => Array ( [pref_code] => 38 [pref_name] => 愛媛県 [total] => 3054961 [rank] => 14 ) [28] => Array ( [pref_code] => 39 [pref_name] => 高知県 [total] => 1653971 [rank] => 19 ) [29] => Array ( [pref_code] => 40 [pref_name] => 福岡県 [total] => 19732158 [rank] => 5 ) [30] => Array ( [pref_code] => 41 [pref_name] => 佐賀県 [total] => 610419 [rank] => 30 ) [31] => Array ( [pref_code] => 42 [pref_name] => 長崎県 [total] => 3736928 [rank] => 9 ) [32] => Array ( [pref_code] => 43 [pref_name] => 熊本県 [total] => 3365295 [rank] => 12 ) [33] => Array ( [pref_code] => 44 [pref_name] => 大分県 [total] => 1876887 [rank] => 16 ) [34] => Array ( [pref_code] => 45 [pref_name] => 宮崎県 [total] => 3312416 [rank] => 13 ) [35] => Array ( [pref_code] => 46 [pref_name] => 鹿児島県 [total] => 7285149 [rank] => 8 ) [36] => Array ( [pref_code] => 47 [pref_name] => 沖縄県 [total] => 22976059 [rank] => 4 ) )
見やすくする為に、HTMLとCSSで体裁を整えると、以下のようになります。
pref_code | 都道府県名 | 国内線利用者数合計 | 順位 |
---|---|---|---|
1 |
北海道 |
26730916 |
2 |
2 |
青森県 |
1487732 |
22 |
3 |
岩手県 |
470076 |
32 |
4 |
宮城県 |
3462344 |
10 |
5 |
秋田県 |
1528247 |
21 |
6 |
山形県 |
775725 |
28 |
7 |
福島県 |
255617 |
34 |
8 |
茨城県 |
653265 |
29 |
12 |
千葉県 |
7642779 |
6 |
13 |
東京都 |
68802104 |
1 |
15 |
新潟県 |
1063706 |
26 |
16 |
富山県 |
455614 |
33 |
17 |
石川県 |
1826015 |
17 |
18 |
福井県 |
0 |
37 |
20 |
長野県 |
153676 |
36 |
22 |
静岡県 |
488053 |
31 |
23 |
愛知県 |
7618685 |
7 |
27 |
大阪府 |
23485979 |
3 |
28 |
兵庫県 |
3404776 |
11 |
30 |
和歌山県 |
182158 |
35 |
31 |
鳥取県 |
1015684 |
27 |
32 |
島根県 |
1265717 |
24 |
33 |
岡山県 |
1312972 |
23 |
34 |
広島県 |
2821076 |
15 |
35 |
山口県 |
1528689 |
20 |
36 |
徳島県 |
1210359 |
25 |
37 |
香川県 |
1815458 |
18 |
38 |
愛媛県 |
3054961 |
14 |
39 |
高知県 |
1653971 |
19 |
40 |
福岡県 |
19732158 |
5 |
41 |
佐賀県 |
610419 |
30 |
42 |
長崎県 |
3736928 |
9 |
43 |
熊本県 |
3365295 |
12 |
44 |
大分県 |
1876887 |
16 |
45 |
宮崎県 |
3312416 |
13 |
46 |
鹿児島県 |
7285149 |
8 |
47 |
沖縄県 |
22976059 |
4 |
SQLの使いどころ
サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。
SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。
また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。
KK
機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。