MySQLのランキング関数の実装

今、すべての学生のスコアをランク付けし、ランクをリストする要件があります。私はちょうどこの要件を見た、私は少し混乱している、全くアイデアがありません、なぜそれが難しい少し要件、私はそれを行うことはありません情報を確認するためにインターネットに移動し、すべての実装をリストアップし、それらのすべてを学ばなければなりません。

データベースの準備

スコアテーブルの作成 s_score

CREATE TABLE `s_score` (
 `id` int NOT NULL AUTO_INCREMENT,
 `score` int NOT NULL DEFAULT 0,
 `name` varchar(20) CHARACTER SET utf8mb4 NULL,
 PRIMARY KEY (`id`)
);

データ挿入

INSERT INTO `s_score` (`name`, `score`) VALUES
(' , 80),
(' , 90),
(' , 60),
(' , 70),
(' , 80),
(' , 87),
(' , 69),
(' , 69),
(' , 91),
(' , 96),
(' , 96),
(' , 80),
(' , 88);

一般的な実装

ランク関数 RANK は、この要件を完全にサポートするために MySQL 8.0 で導入されましたが、MySQL 8.0 以上でサポートされている必要があります。 8.0 未満のバージョンで、ランクを記録するためにユーザ変数を使用してこれを実現する方法は何ですか。ユーザ変数："@"で始まり、ユーザ変数とカラム名を区別するために"@var_name "という形式をとります。列が指定されていない限り、任意のランダムな複合スカラー式にすることができます。以下はユーザー変数の使い方の小さな例です。

select @a:=1 a,@b:=@a+1 b

実施結果

:=は代入を意味し、プログラミング言語の代入とは少し異なります。RANKランキング関数を実装するために単純なSQLを使用した場合の効果を以下に示します。

名前表示のためのユーザー変数の簡単な実装

SELECT name,score, @rank:=@rank+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc

横並びランキング

今そこに別の問題は、同じスコア、サイドバイサイドランキングがあり、ランキングは同じでなければなりません。前のスコアの値を記録する一時変数を使用して、前のスコアが現在と等しいかどうかを判断し、等しい直接以前のランキング状況に戻り、それ以外の場合はランキング+ 1。

select name,score,case when @temp_score=score then @rank when @temp_score:=score then @rank:=@rank+1 END 
 `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc

横並びランキングスキップ

1位が同点の場合、次の順位は自動的にスキップされます。例えば、1位が2人同点の場合、91人が3位となり、人数に応じた順位となります。

SELECT name,score,rank from (
SELECT name ,score,@rank :=IF( @temp_score = score, @rank, @rank_incr ) `rank`,@rank_incr := @rank_incr + 1,
	@temp_score := score FROM score s,(SELECT@rank := 0,@temp_rank := NULL,@rank_incr := 1 ) q ORDER BY score DESC) a

SQLウィンドウ関数の使用

ウィンドウ関数の基本構文は以下の通りです：

ソート関数/集計関数を選択

overの後にスペースがあることに注意してください。この構文はちょっと面倒で、うまく書けるまで何十回も試しました。ウィキペディアによると、ウィンドウ関数は現在のレコードとそれ以降のレコードの両方にアクセスできるようにします。ウィンドウ関数は、現在の行の周囲に所定の長さを持つ行または列を定義し、ウィンドウ内のデータセット全体で計算を実行します。ウィンドウはデータセットで、関数はデータの計算方法です。

partiton byはオプションです。partiton byを使用しない場合、テーブル全体をコレクションとし、最後にソート関数を使用して、ソート番号のソート順序に従って各レコードを取得します。ソート関数は、主にrank()、dense_rank、row_number、それらの主な違いです：

• rank()：同じフィールドをソートし、同点の場合は並列にランク付けされ、ランク差が生じます。
• dense_rank()：同じフィールドをソートし、同じ現象が発生した場合に同順位となる、連続した順位
• row_number()：同じフィールドを並べ替えると、ランクがリンクされている、同じがある場合でも、並んでランクされません

	select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`,
	DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score