ユーザーランキングを生成するSQL

  • 2016-5-16
  • ユーザーランキングを生成するSQL はコメントを受け付けていません。

1ユーザーごとにスコアを記録した1レコード存在する下記のテーブルからユーザーのランキングデータを別テーブルに生成する手法で、CASE式とSQL内部変数を組み合わせたテクニックです。
同スコアは同順位として処理され、同ランクがいた場合次の順位が欠番となります。

【元テーブルデータ】
scoreテーブル
----------
uid,score
----------
1,1502
2,2303
3,3504

【ランキングを生成する別テーブルのスキーマ】
CREATE TABLE ranking
(
uid BIGINT UNSIGNED NOT NULL,
sort_order BIGINT UNSIGNED NOT NULL,
rank BIGINT UNSIGNED NOT NULL,
score INT UNSIGNED NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY(uid)
);

【SQL例】
REPLACE INTO ranking
(uid,sort_order,rank,score,created)
SELECT
uid,
@order := @order + 1,
CASE
WHEN @prev = score  THEN @cur
WHEN @prev := score  THEN @cur := @order
END
score,
NOW()
FROM score
INNER jOIN (SELECT @cur := 0, @order := 0, @prev:=NULL) tmp
ORDER BY score DESC

関連記事

コメントは利用できません。

ページ上部へ戻る