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

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

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

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

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

`population`でDESC(降順)すれば済む。

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

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

福岡県の人口は、以下のように9位である。

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

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

SELECT (SELECT COUNT(*) FROM `prefecture` AS `b` WHERE `a`.`population` ＜ `b`.`population`) + 1 AS `rank` FROM `prefecture` AS `a` WHERE `name`='福岡県' ;

これを実行すると、

rank
—–
9

が得られる。

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

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

福岡県の人口よりも多い都道府県(`a`.`population` ＜ `b`.`population`)であると1ツ目のWHEREで条件指定し、その件数をカウントしている。

福岡県よりも人口の多い都道府県は、

1. 東京都
2. 神奈川県
3. 大阪府
4. 愛知県
5. 埼玉県
6. 千葉県
7. 兵庫県
8. 北海道

の8件。

福岡県はその次にあるわけだから、これに1を足して9(=9位)としている。

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

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

ということなのだが、自己結合は非常に分かりにくい！

`prefecture`を`a`と`b`に「分身」させ、`a`は「WHERE `name`=’福岡県’」で福岡県に絞ったので「`a`.`population`」は福岡県の人口である。

`b`は絞っておらず、「`b`.`population`」は全都道府県の各々の人口であり、福岡県の人口と比較(WHERE `a`.`population` ＜ `b`.`population`)して多い行をカウント(COUNT(*))し、それに壹を加えたモノを順位とする…

分身の定義(AS `a`とAS `b`)が何処かにまとまっていれば分かるのだろうが、突如として、意図しない順序で出てくるのが、理解を困難にしている原因だろう。

今回は特定の順位(福岡県)を求めたが、以下のようにスルコトで、全都道府県の順位、つまり、冒頭で行った「単に`population`でDESC」と同じ結果が得られる。

SELECT (SELECT COUNT(*) FROM `prefecture` AS `b` WHERE `a`.`population` `b`.`population`) + 1 AS `rank` , `name` , FORMAT(`a`.`population`,0) AS `population` FROM `prefecture` AS `a` ORDER BY `rank` ASC ;

又は

FORMATは単に3桁区切りで表示(だ)シたかッただけであり、なくても問題ない。

但し、同一の人口の都道府県が存在した場合、順位が飛んでしまう。

RANK形式(同じ値がある場合、ランクは飛び番になる)
例：1位,2位,2位,4位,5位...

都道府県の人口で同じになることはまずないが、テストの得点などでは同一値はありうるので、DISTINCTを使うなどして、順位が飛ばないようにする必要がある。

DENSE_RANK形式(同じ値がある場合、ランクは飛ばない)
例：1位,2位,2位,3位,4位...

全都道府県の順位となると、その数の分、COUNTが発生するので遅くなる。

47都道府県程度であれば問題ないが、レコードが増えるとどン！どン！遅くなり...

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

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

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

MySQL 8.0以上でWindow関数が使える環境なら、RANK()やDENSE_RANK()が使えるのでスッキリ書けるだろうが、レンタルサーバーだと2023年の時点でもMySQL 5.xとかもあるので...

・さくらインターネット：MySQL 5.7
・ロリポップ：MySQL 5.7.36

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

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