順位付け機能(ランキング) | FIND_IN_SET と GROUP_CONCAT を利用する ; MySQL
目的
1-0. FIND_IN_SET と GROUP_CONCAT を使って
順位付け機能(ランキング)を設計使用すること。
1-1. FIND_IN_SET と GROUP_CONCAT の設計分担を
コードの位置から解く
1-2. 順位が妥当なテーブルから、既存テーブルを UPDATE もしくは INSERT
前提条件
次の ranking テーブルが用意されている。
ranking
id | name | rank | score |
---|---|---|---|
5 | Enami | 1 | 2900 |
7 | Nakano | 2 | 2800 |
6 | Morioka | 3 | 2300 |
8 | Ohno | 4 | 2200 |
2 | Koike | 5 | 2000 |
4 | Isurugi | 6 | 1800 |
3 | Sasaki | 7 | 1700 |
1 | Yokota | 8 | 1000 |
id = 2 の Koike - san の score が 3200 になった。
しかし、順位が全く変わっていない。
id | name | rank | score |
---|---|---|---|
5 | Enami | 1 | 2900 |
7 | Nakano | 2 | 2800 |
6 | Morioka | 3 | 2300 |
8 | Ohno | 4 | 2200 |
2 | Koike | 5 | 3200 |
4 | Isurugi | 6 | 1800 |
3 | Sasaki | 7 | 1700 |
1 | Yokota | 8 | 1000 |
初めに結論
順位が妥当な ranking テーブルを取得する。
そのテーブルを使って、更新する。
順位が妥当なテーブルの取得
SELECT id, name, score, FIND_IN_SET(score, ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM ranking ) ) AS rank FROM ranking ORDER BY rank;
更新
INSERT INTO ranking SELECT id, name, rank, score FROM (SELECT id, name, FIND_IN_SET(score, (SELECT GROUP_CONCAT(score ORDER BY score DESC) FROM ranking)) AS rank, score FROM ranking) AS latest_rnk ON DUPLICATE KEY UPDATE id = latest_rnk.id, name = latest_rnk.name, rank = latest_rnk.rank, score = latest_rnk.score;
分解その1 -- GROUP_CONCAT
ranking テーブルから、点数ソートした結果を一つのレコードにしている。
SELECT GROUP_CONCAT(score ORDER BY score DESC) FROM ranking; +-----------------------------------------+ | group_concat(score order by score desc) | +-----------------------------------------+ | 3200,2900,2800,2300,2200,1800,1700,1000 | +-----------------------------------------+
ここでは仮にこのレコードを REC_RANKING としておく
分解その2 -- FIND_IN_SET
降順にソートされた点数から、一番初めに見つかった
番号が値として返ってくる。この番号が順位になる。
FIND_IN_SET(score, ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM ranking ) ) AS rank FROM ranking ORDER BY rank; 改め FIND_IN_SET(score, ( REC_RANKING ) ) FROM ranking
id = 7, score = 2300 ならば FIND_IN_SET で返ってくる値は 3 3200,2900,2800,2300,2200,1800,1700,1000 1 2 *3* 4 5 6 7 8
コマンド実行結果
id | name | rank | score |
---|---|---|---|
2 | Koike | 3200 | 1 |
5 | Enami | 2900 | 2 |
7 | Nakano | 2800 | 3 |
6 | Morioka | 2300 | 4 |
8 | Ohno | 2200 | 5 |
4 | Isurugi | 1800 | 6 |
3 | Sasaki | 1700 | 7 |
1 | Yokota | 1000 | 8 |
実行前と比較
id | name | rank | score |
---|---|---|---|
5 | Enami | 1 | 2900 |
7 | Nakano | 2 | 2800 |
6 | Morioka | 3 | 2300 |
8 | Ohno | 4 | 2200 |
2 | Koike | 5 | 3200 |
4 | Isurugi | 6 | 1800 |
3 | Sasaki | 7 | 1700 |
1 | Yokota | 8 | 1000 |
更新(厳密には挿入)
更新をする。実際、内部では INSERT を使っている
上で更新されたテーブルを使って、既存全レコードに対して INSERT を引っ掛ける
別件
INSERT IGNORE
とON DUPLICATE KEY UPDATE
だったら 後者の方が良いという情報有り結果的に PRIMARY KEY や UNIQUE に 重複がふくまれているならば、 row に対して insert をしない
上の場合、エラー(error)を吐くだけであって警告(warn)は出さない
INSERT INTO ranking SELECT id, name, rank, score FROM (SELECT id, name, FIND_IN_SET(score, (SELECT GROUP_CONCAT(score ORDER BY score DESC) FROM ranking)) AS rank, score FROM ranking) AS latest_rnk ON DUPLICATE KEY UPDATE id = latest_rnk.id, name = latest_rnk.name, rank = latest_rnk.rank, score = latest_rnk.score;
コマンド実行結果
id | name | rank | score |
---|---|---|---|
2 | Koike | 1 | 3200 |
5 | Enami | 2 | 2900 |
7 | Nakano | 3 | 2800 |
6 | Morioka | 4 | 2300 |
8 | Ohno | 5 | 2200 |
4 | Isurugi | 6 | 1800 |
3 | Sasaki | 7 | 1700 |
1 | Yokota | 8 | 1000 |
実行前と比較
id | name | rank | score |
---|---|---|---|
5 | Enami | 1 | 2900 |
7 | Nakano | 2 | 2800 |
6 | Morioka | 3 | 2300 |
8 | Ohno | 4 | 2200 |
2 | Koike | 5 | 3200 |
4 | Isurugi | 6 | 1800 |
3 | Sasaki | 7 | 1700 |
1 | Yokota | 8 | 1000 |
引用元
StackExchange --- Get the rank of a user in a score table # thanks cairnz, Michael
stackoverflow --- "INSERT IGNORE" vs "INSERT … ON DUPLICATE KEY UPDATE" # thanks Bill Karwin, Peter G., Nightfirecat