ハードディスクメンテナンス ブログ

投稿記事数7,700以上!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 < b.population) + 1 AS rank FROM prefecture AS a WHERE name=’福岡県’ ;

これを実行すると、

rank
—–
9

が得られる。

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

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

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

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

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

の8件。

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

福岡県の人口は9位

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

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

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

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

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

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

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

実践ハイパフォーマンスMySQL 第3版
実践ハイパフォーマンスMySQL 第3版

スポンサーリンク
posted on shattered-blog.com at 15.01.24
Baron Schwartz Peter Zaitsev Vadim Tkachenko
オライリージャパン
売り上げランキング: 187,031


SQLアンチパターン
Bill Karwin
オライリージャパン
売り上げランキング: 7,189



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

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

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




sha-bc@336×280

sha-bc@336×280


sha-bc@336×280

sha-bc@336×280

  関連記事

no image
激重激遅SQL文の活用方法

結果を得るのに実行時間:68.400223970413秒もかかる激重SQL文。 …

no image
CPU対決!激重SQL編!(Phenom X3 8400 vs Core i5 650)

実行時間が70秒近くかかる激重クエリがある。 この実行に於ける、ハードウェアの違 …

no image
Debian7.4でWEBサーバーを立てる(Apache,MySQL,PHP,phpMyAdmin,FTP)

Windows環境でもXAMPPを入れると環境が整うので(その端末なのでFTP不 …

no image
「実践ハイパフォーマンス MySQL」を購入

「実践ハイパフォーマンス MySQL 第3版」を購入した。 アマゾンから箱が&# …

no image
日付比較や日付検索が遅い場合の改善方法(BETWEEN,MySQL)

datetime型の列(reg_time)があるテーブルに対し、年月日指定をかけ …

Optimize Your Database
WordPressで使用しているデータベースの使用容量を下げる方法

WordPressはブログシステムだから、主にテキストと画像で構成される(本ブロ …

no image
(在庫僅少)激安サーバー2機種、Linuxを入れてファイルサーバーやMySQLの学習用に!(富士通とHP)

2万円以下の激安サーバーを紹介しよう。 富士通 PRIMERGY MX130 S …

no image
[MySQL] index定義の無駄と、indexが役に立たない場合 [悪例,アンチパターン]

データベースで重要なindexであるが、全てのfieldにindexを定義するの …

idを詰める(連番を振り直す)方法
[MySQL] idを詰める(連番を振り直す)方法

数分毎に定期的にログを取得するようなプログラムを書き、DBに格納し始める。 だが …

no image
mysqlimport –helpの内容 csvファイルの内容をデータベースにインポートする

mysqlimport –help の内容を以下に付しておく。 試験 …