UI設計したり、ウェブシステムを開発したりします。 

SQL見るだけクエリ#018 ランキングに順位番号を取得する簡単な方法(FIND_IN_SET)応用編

記入日: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)

                
idairport_codeairport_namepref_codepref_namepassengers_sumdomestic_suminternational_sum
122827羽田空港(東京)13東京都869202936838281118537482
229110成田空港(東京)12千葉県42413928764277934771149
325892関西空港(大阪)27大阪府31807820698177024826050
429655福岡空港40福岡県24679617182815526398065
529654新千歳空港(札幌)1北海道24599263207327443866519
629657那覇空港(沖縄)47沖縄県21761828180809983680830
725856伊丹空港(大阪)27大阪府16504209165042090
829411中部国際空港(名古屋)23愛知県1346014966766236783526
928935鹿児島空港46鹿児島県60752105663539411671
1021045仙台空港4宮城県38553873462344393043
1128628熊本空港43熊本県34921883321313170875
1229656宮崎空港45宮崎県3410361331241697945
1329150神戸空港28兵庫県3362720336267149
1428535長崎空港42長崎県3360170327527084900
1527367広島空港34広島県31665722821076345496
1627893松山空港38愛媛県3152419305496197458
1729116石垣空港47沖縄県2614822251521099612
1827725高松空港37香川県21524301815458336972
1928743大分空港44大分県19823771876887105490
2023691小松空港17石川県18875351653086234449
2129115宮古空港47沖縄県180349018034900
2220443函館空港1北海道18005771632697167880
2328133北九州空港40福岡県17535251450606302919
2427970高知空港39高知県165627916539712308
2527053岡山空港33岡山県16119581312970298988
2621138秋田空港5秋田県1380863136903311830
2720620青森空港2青森県1250569117820572364
2827649徳島空港36徳島県121885212103598493
2924034新潟空港15新潟県12014191063706137713
3020013旭川空港1北海道1158948110951749431
3126923出雲空港32島根県105115510492601895
3227584山口宇部空港35山口県1028166101493913227
3325079名古屋空港23愛知県942753942062691
3429114奄美空港46鹿児島県8919908919900
3520178釧路空港1北海道866970866198772
3620570女満別空港1北海道8604588585001958
3729445茨城空港8茨城県822208653265168943
3829152佐賀空港41佐賀県813082610419202663
3929148静岡空港22静岡県805195488053317142
4020106帯広空港1北海道701557700943614
4127033米子空港31鳥取県69213760479287345
4224723富山空港16富山県575172455614119558
4320908花巻空港3岩手県51674247007646666
4429706岩国空港35山口県5137505137500
4521335庄内空港6山形県4425664370725494
4626881鳥取空港31鳥取県4177404108926848
4721393山形空港6山形県36667833865328025
4820772三沢空港2青森県3095273095270
4921552福島空港7福島県28243725561726820
5020081丘珠空港(札幌)1北海道2774252774250
5129126久米島空港47沖縄県2645242645240
5229119対馬空港42長崎県2576872576870
5320428根室中標津空港1北海道2148752148750
5429113八丈島空港13東京都2135692135690
5529123徳之島空港46鹿児島県2075422075420
5620613稚内空港1北海道2036542036540
5726812南紀白浜空港30和歌山県1821581821580
5829153能登空港17石川県1745441729291615
5929118五島福江空港42長崎県1689741689740
6029147大館能代空港5秋田県1592141592140
6124326松本空港20長野県1558981536762222
6226938萩・石見空港32島根県1527251527250
6329122屋久島空港46鹿児島県1459731459730
6429124沖永良部空港46鹿児島県1196361196360
6530011下地島空港47沖縄県1092469200017246
6629127与那国空港47沖縄県1044631044630
6729141調布飛行場13東京都97041970410
6829128喜界空港46鹿児島県89729897290
6929121種子島空港46鹿児島県88513885130
7029125与論空港46鹿児島県78227782270
7120107オホーツク紋別空港1北海道77299772990
7229117隠岐空港32島根県63732637320
7329135南大東空港47沖縄県47871478710
7429129利尻空港1北海道45183451830
7529136多良間空港47沖縄県44809448090
7629313天草空港43熊本県43982439820
7726436但馬空港28兵庫県42105421050
7829120壱岐空港42長崎県34992349920
7929142新島空港13東京都32343323430
8029112三宅島空港13東京都29163291630
8129111大島空港13東京都24539245390
8229143神津島空港13東京都22638226380
8329134北大東空港47沖縄県21651216510
8429131奥尻空港1北海道11881118810
8530009粟国空港47沖縄県8508500
8630010慶良間空港47沖縄県1931930
8730005小値賀空港42長崎県550
8830007岡南飛行場33岡山県220
8930001礼文空港1北海道000
9030002佐渡空港15新潟県000
9130003福井空港18福井県000
9230004八尾空港27大阪府000
9330006上五島空港42長崎県000
9430008大分県央飛行場44大分県000
9530012伊江島空港47沖縄県000
9630013波照間空港47沖縄県000

テーブルの情報を取得する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化のディレクションなどを行っています。