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

SQL見るだけクエリ#009 郵便番号データ(12万件)都市別登録数ランキング上位50

記入日:2023-01-05 編集日:2023-01-05

SQL見るだけクエリ。今回は前回に続いて、郵便番号データを使ってGROUP BYとLIMITを組み合わせて練習をしてみようと思います。 全国12万件のレコードを都市別でカウントし、都市別に何件のレコードがあるか計算、上位50都市を抽出してみようと思います。

この記事を読むと(約5分)
10〜20万件程度のデータから、GROUP BYとLIMIT句を使ってカウントする方法を知ることが出来ます。

全国12万件のレコードが入っている、postal_codeテーブルの、都市欄をカウントし、 都市別で何件の郵便番号レコードがあるのかを調べてみようと思います。どの都市が一番多いのでしょうか?

さていつもの様に、テーブルの情報を取得するSQLに関して、注目する部分は、以下の9~12行目のみです。

SELECT city, COUNT(city) AS total FROM postal_code GROUP BY city ORDER BY total DESC LIMIT 50 この部分で、データベース内にあるpostal_codeテーブルの、city欄(都市の文字が入っている項目)をカウントしていってます。

try {
    include_once '../model/class.php';

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT city, COUNT(city) AS total FROM postal_code 
    GROUP BY city 
    ORDER BY total DESC 
    LIMIT 50';
    $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
        (
            [city] => 富山市
            [total] => 1147
        )

    [1] => Array
        (
            [city] => 港区
            [total] => 925
        )

    [2] => Array
        (
            [city] => 岐阜市
            [total] => 837
        )

    [3] => Array
        (
            [city] => 上越市
            [total] => 754
        )

    [4] => Array
        (
            [city] => 新宿区
            [total] => 696
        )

    [5] => Array
        (
            [city] => 京都市上京区
            [total] => 656
        )

    [6] => Array
        (
            [city] => 京都市伏見区
            [total] => 631
        )

    [7] => Array
        (
            [city] => 京都市下京区
            [total] => 594
        )

    [8] => Array
        (
            [city] => 長岡市
            [total] => 588
        )

    [9] => Array
        (
            [city] => 金沢市
            [total] => 570
        )

    [10] => Array
        (
            [city] => 京都市右京区
            [total] => 556
        )

    [11] => Array
        (
            [city] => 京都市中京区
            [total] => 552
        )

    [12] => Array
        (
            [city] => 福井市
            [total] => 530
        )

    [13] => Array
        (
            [city] => 姫路市
            [total] => 523
        )

    [14] => Array
        (
            [city] => 京都市左京区
            [total] => 513
        )

    [15] => Array
        (
            [city] => 千代田区
            [total] => 485
        )

    [16] => Array
        (
            [city] => 豊田市
            [total] => 483
        )

    [17] => Array
        (
            [city] => 和歌山市
            [total] => 430
        )

    [18] => Array
        (
            [city] => 大分市
            [total] => 426
        )

    [19] => Array
        (
            [city] => 高岡市
            [total] => 421
        )

    [20] => Array
        (
            [city] => 栗原市
            [total] => 418
        )

    [21] => Array
        (
            [city] => 豊川市
            [total] => 417
        )

    [22] => Array
        (
            [city] => 奥州市
            [total] => 414
        )

    [23] => Array
        (
            [city] => 奈良市
            [total] => 400
        )

    [24] => Array
        (
            [city] => 鳥取市
            [total] => 399
        )

    [25] => Array
        (
            [city] => 稲沢市
            [total] => 390
        )

    [26] => Array
        (
            [city] => 名古屋市中村区
            [total] => 382
        )

    [27] => Array
        (
            [city] => 下関市
            [total] => 374
        )

    [28] => Array
        (
            [city] => 気仙沼市
            [total] => 367
        )

    [29] => Array
        (
            [city] => 松山市
            [total] => 364
        )

    [30] => Array
        (
            [city] => 京都市北区
            [total] => 361
        )

    [31] => Array
        (
            [city] => 平川市
            [total] => 358
        )

    [32] => Array
        (
            [city] => 長崎市
            [total] => 357
        )

    [33] => Array
        (
            [city] => 豊橋市
            [total] => 356
        )

    [34] => Array
        (
            [city] => 旭川市
            [total] => 349
        )

    [35] => Array
        (
            [city] => 南砺市
            [total] => 330
        )

    [36] => Array
        (
            [city] => 秋田市
            [total] => 325
        )

    [37] => Array
        (
            [city] => 江南市
            [total] => 324
        )

    [38] => Array
        (
            [city] => 西宮市
            [total] => 323
        )

    [39] => Array
        (
            [city] => 横手市
            [total] => 323
        )

    [40] => Array
        (
            [city] => 岡崎市
            [total] => 322
        )

    [41] => Array
        (
            [city] => 豊岡市
            [total] => 315
        )

    [42] => Array
        (
            [city] => 長浜市
            [total] => 314
        )

    [43] => Array
        (
            [city] => 長野市
            [total] => 312
        )

    [44] => Array
        (
            [city] => 登米市
            [total] => 310
        )

    [45] => Array
        (
            [city] => 高知市
            [total] => 308
        )

    [46] => Array
        (
            [city] => 岡山市北区
            [total] => 308
        )

    [47] => Array
        (
            [city] => 伊達市
            [total] => 307
        )

    [48] => Array
        (
            [city] => 佐世保市
            [total] => 297
        )

    [49] => Array
        (
            [city] => 津市
            [total] => 296
        )

)
			

郵便番号データ(12万件)
都市別登録数ランキングBEST50

多い順 city total 割合
1
富山市
1,147件
0.92%
2
港区
925件
0.74%
3
岐阜市
837件
0.67%
4
上越市
754件
0.61%
5
新宿区
696件
0.56%
6
京都市上京区
656件
0.53%
7
京都市伏見区
631件
0.51%
8
京都市下京区
594件
0.48%
9
長岡市
588件
0.47%
10
金沢市
570件
0.46%
11
京都市右京区
556件
0.45%
12
京都市中京区
552件
0.44%
13
福井市
530件
0.43%
14
姫路市
523件
0.42%
15
京都市左京区
513件
0.41%
16
千代田区
485件
0.39%
17
豊田市
483件
0.39%
18
和歌山市
430件
0.35%
19
大分市
426件
0.34%
20
高岡市
421件
0.34%
21
栗原市
418件
0.34%
22
豊川市
417件
0.33%
23
奥州市
414件
0.33%
24
奈良市
400件
0.32%
25
鳥取市
399件
0.32%
26
稲沢市
390件
0.31%
27
名古屋市中村区
382件
0.31%
28
下関市
374件
0.30%
29
気仙沼市
367件
0.29%
30
松山市
364件
0.29%
31
京都市北区
361件
0.29%
32
平川市
358件
0.29%
33
長崎市
357件
0.29%
34
豊橋市
356件
0.29%
35
旭川市
349件
0.28%
36
南砺市
330件
0.26%
37
秋田市
325件
0.26%
38
江南市
324件
0.26%
39
西宮市
323件
0.26%
40
横手市
323件
0.26%
41
岡崎市
322件
0.26%
42
豊岡市
315件
0.25%
43
長浜市
314件
0.25%
44
長野市
312件
0.25%
45
登米市
310件
0.25%
46
高知市
308件
0.25%
47
岡山市北区
308件
0.25%
48
伊達市
307件
0.25%
49
佐世保市
297件
0.24%
50
津市
296件
0.24%

富山市がトップでした。

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。