ハードディスクメンテナンス

投稿記事数[8700]以上!PC,旧ゲーム,カメラ,動画などを、アヤしゐ「專門妖語」を使ッて解説スル!

データベース

[MySQL] ランキングなどで順位を取得する方法 [自己結合]

更新日:

以前はよく書いていたDBネタだが、最近はあまり書いていない。

ブログに書かないだけで、SQL文は今も書いている。

さて、以下のような、都道府県毎の人口データ(prefecture)があるとする。

人口データ(prefecture)

これを多い順に並べるのは簡単である。

SELECT * FROM `prefecture` ORDER BY `population` DESC ;

だが、福岡県の人口が、47都道府県中の何位であるかを求めるには、どうしたらよいか。

先に書いておくと、福岡県の人口は、以下のように9位である。

福岡県の人口は9位

この「9」という数字を得るSQL文を考える。

よくあるのが、福岡県より人口が多い都道府県の行数をカウントし、それに1を足して順位とするもの。

SELECT (SELECT COUNT(*) FROM `prefecture` AS `b` WHERE `a`.`population`

これを実行すると、

rank
-----
9

が得られる。

上で書いたように、自分より人口が上の行数をカウントし、それに1を足して順位としている。

「`a`.`population`」が福岡県の人口で、「`b`.`population`」が各都道府県の人口。

福岡県の人口よりも多い都道府県(`a`.`population`

その結果を「AS `rank`」とし、それ自体が、一番最初のSELECTの対象となっているのだ。

# MySQLで使われる「AS」は省略できることがあるが、省略しない方が読みやすいだろう。

だがこの方法、47都道府県程度であれば問題ないが、レコードが増えると非常に遅くなるという難点がある。

今回は特定の順位(福岡県)を求めたが、これが全都道府県の順位となると、その数の分、COUNTが発生し...

また、MySQLには内部結合や外部結合があり、複数のテーブルを扱うようになると使うことになるが、上記は内部結合といって、同じテーブルを対象に結合するもの。

結合はレコード数が増えると重くなるが、内部結合でも同様である。

さて、重くなるのを避けるSQL文は?

今回使用した人口データは以下に置いておくので、コピペして使ってください。

csvファイルとしてインポートするとよいかと。

2014-2015年あたりの推計人口です。

人口ランキングは上の表(画像)にあるので、いろいろ試してみるのには向いているデータかと。

<都道府県毎の人口データ>

北海道,5437988
青森県,1320329
岩手県,1283390
秋田県,1035051
宮城県,2328265
山形県,1129441
福島県,1936008
東京都,13390116
神奈川県,9101625
埼玉県,7240300
千葉県,6199316
茨城県,2919996
群馬県,1976086
栃木県,1980556
山梨県,839426
新潟県,2312006
長野県,2106006
富山県,1069465
石川県,1155139
福井県,789375
愛知県,7447161
岐阜県,2040647
静岡県,3694105
三重県,1819737
大阪府,8849823
兵庫県,5539714
京都府,2613601
滋賀県,1416912
奈良県,1375737
和歌山県,970006
広島県,2833199
岡山県,1924750
鳥取県,573801
島根県,696521
山口県,1407681
徳島県,763270
香川県,980583
愛媛県,1394449
高知県,737295
福岡県,5094239
佐賀県,834732
長崎県,1384761
大分県,1170987
熊本県,1794233
宮崎県,1114398
鹿児島県,1668529
沖縄県,1424615

関連:データベース関連の投稿

Baron Schwartz Peter Zaitsev Vadim Tkachenko
オライリージャパン
売り上げランキング: 187,031
Bill Karwin
オライリージャパン
売り上げランキング: 7,189



-データベース
-, , , ,

Copyright© ハードディスクメンテナンス , 2019 All Rights Reserved.