MYSQL タイ順位を表示させ、ユーザーを重複させたくない

Question

前提

MYSQLのテーブルの内容

user_id	points（ユーザー獲得ポイント）	exam_id（クイズのレベル）
1	130	5
4	150	1
1	150	5
8	150	5
4	140	5
56	120	3
32	70	5
32	10	5
11	10	5

クイズのサイトを作成しています。上記のテーブルを使用して、ユーザーを高得点順にランキング形式で表示させたいと思っています。

実現したいこと

実現したい内容

user_id	points	exam_id	RANK
1	150	5	1(位)
8	150	5	1(位)同位の場合はタイ表示
4	140	5	3(位)
32	70	5	4(位)
11	10	5	5(位)

■同位がある場合はタイ表示
■exam_id毎に表示させる
■各user_idは重複させず、高得点のみ表示

試した事 / 質問点

user_id	points	exam_id	RANK	備考
1	150	5	1
8	150	5	1
4	140	5	2
1	130	5	3	←user_idは高得点のみ表示させ、重複させたくない
32	70	5	4
32	10	5	5	←user_idは高得点のみ表示させ、重複させたくない
11	10	5	5

下記該当のソースコードでランクのタイ表示はできたのですが、クイズは何度でも受けられる事から、上の表の様にuser_id 1やuser_id 32が重複してランクインされてしまいます

user_idは重複させず、なおかつ、排除した分はランクが繰り上がる様にしたいのですが、どの様な書き方がよいのでしょうか？

よろしくお願いいたします。

該当のソースコード

SELECT
   user_id,
   points,
   exam_id,
	(SELECT
		COUNT(DISTINCT points)
		FROM ranking b
		WHERE exam_id='5' AND a.points < b.points
    ) + 1 rank
FROM
   ranking a
WHERE
   exam_id='5' -- クイズのレベル
ORDER BY
   rank ASC

補足情報（バージョンなど）

phpMyAdmin
バージョン情報： 4.7.9

Comments

[yambejp]

実現したい内容にはid=4が抜けているのはなぜ？exam_id=5のデータもありますよね

[mendax]

ご回答、ご指摘いただきありがとうございます！ おっしゃる通りid=4もexam_id=5に入りますので、表を修正いたしました。

[sazi]

ユーザーの自身の得点が同じだった場合でも、rankingの件数は受けた件数分ですか？

Answer 1

MySQL8.0以降なら分析関数が使えるのに。。

select user_id, max_points
     ,(select count(distinct max_points) +1
       from (
          select user_id, max(points) as max_points 
          from ranking 
          where exam_id=5
          group by user_id
        ) v2
       where max_points < v1.max_points
      ) rank
from (
    select user_id, max(points) as max_points 
    from ranking 
    where exam_id=5
    group by user_id
) v1

上記はdense_rank()【同率があった場合順位は同じになり、その次は順位を飛ばさない】と同じ。
質問がrank()【同率があった場合順位は同じになり、その次は順位を飛ばす】に変更されているので、以下
※DISTINCTを付けない

select user_id, max_points
     ,(select count(max_points) +1
       from (
          select user_id, max(points) as max_points 
          from ranking 
          where exam_id=5
          group by user_id
        ) v2
       where max_points < v1.max_points
      ) rank
from (
    select user_id, max(points) as max_points 
    from ranking 
    where exam_id=5
    group by user_id
) v1

下記なども参考にして下さい。
SQLのCOUNTカラムに順位をつけるには

Comments

[mendax]

ご回答いただきありがとうございます！また構文を書いてくださりありがとうございます！
分析関数というのがあるのですね。。まだSQLに触れて日が浅いのでとても勉強になります。
既に問題は解決できましたが、様々なパターンのコードをかける様にsaziさんの構文も参考にさせていただきます。今後もよろしくお願いいたします。

[mendax]

上記二つの構文を試したところ、実現したい内容が実行できました！
【同率があった場合順位は同じになり、その次は順位を飛ばす】に変更をしていたのですが、どちらのパターンも知りたい事でしたので大変参考になりました。
調べてみたら分析関数の方が効率よくコードをかけると知り今後ランキングなどの集計には重宝しそうです。
ご丁寧な解説重ね重ね誠にありがとうございます！

Answer 2

MySQLのユーザ定義変数を利用してOracleの分析関数RANKを実現する方法 が参考になるでしょうか？

Comments

[mendax]

IF(A,B,C)関数で順位を作成する発想がなかったので、とても参考になりました！　別の解答で解決はしたのですが、こちらも後学の為に、色々勉強したいと思います！ ご回答いただきありがとうございます！

Answer 3

select t1.*,
(select count(*)+1 from ranking as t2
where t2.exam_id=t1.exam_id
and not exists(
select 1 from ranking 
where user_id=t2.user_id 
and exam_id=t2.exam_id 
and points>t2.points)
and t2.points>t1.points
) as rank
from ranking as t1
where exam_id=5
and not exists(
select 1 from ranking
where user_id=t1.user_id 
and exam_id=t1.exam_id
and points>t1.points)

Comments

[mendax]

構文まで書いていただき、また迅速なご回答ありがとうございます！勉強になります。
構文を試してみたところ、user id 1のみ重複され、それ以外は重複は出ないようになりました！
またuser id 1の重複のせいか、その部分のみランクの表示が正常に出ない様になっておりました。。　（下記画像参照）
https://imgur.com/NZRgRdf

なお、いただいた構文からORDER BY rank ASC のみ最後に付け加えております。
こちらを参考に私の方でもいろいろ試してみます。

[mendax]

すみません。今気づいたのですが、yambejpさんの構文の不具合ではなく。
同idが高得点を2回出した場合だと、重複されるという問題でした。
なので、先ほどの問題もselect DISTINCT t1.user_idでuser id 1の重複がなくなりました。
お手数おかけしてすみません。

yambejpさんの解答で解決できましたので、ベストアンサーとさせていただきます。