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

投稿記事数8,000以上!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
業務サーバーのCPU負荷が100%

業務サーバー(Linux)のCPU負荷が100%に。 スポンサーリンク MySQ …

no image
MySQLに於けるGROUP BYでのミス(集約キー以外を書いてしまう)

テーブル名:商品一覧 商品名,販売単価,仕入単価 品目A,1000,700 品目 …

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

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

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

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

ダンプ行を指定する箇所
[MySQL] テーブルのエクスポートで、レコード数の不一致が生じる問題 [phpMyAdmin,Dump all rows]

DB(MySQL)にある巨大なテーブルをローカルに保存したいという質問を受けた。 …

no image
csvファイルのMySQLへのインポート (LOAD DATA LOCAL INFILE,mysqlimport) さくらインターネット

自鯖(自由度高)を使うことが多いが、さくらインターネットのユーザーから質問を受け …

no image
(MySQL) key_buffer_sizeの変更 (Debian)

key_buffer_sizeとは、indexの常駐量。 これがデフォルトでは8 …

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

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

E3110
DBサーバーのハードウェア換装(Debian7.4,MySQL5.5.35) Xeon E3110

試験データベースサーバー(DBサーバー)を入れ替えた。 Debian7.4(64 …

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

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