Red > Green > Refactor > Red

cycle is based on desire

順位付け機能(ランキング) | 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 IGNOREON 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