【MySQL】SQLでランキング

・自分なりに見やすいやり方（ぇ
・@ を使ったユーザ定義変数を利用
・MyBatis の外だしSQLで実際に利用可能
・8.0以降で使える RANK() 関数は使わないでいきます
・【Java】Caffeineでお手軽にローカルキャッシュ　と組み合わせるとキャッシュされていい感じになります

環境

・Windows10 64bit
・SpringFramework 4
・MyBatis 3.2.5
・MySQL 5.6

ショップのランキングを想定

・shop テーブル
ショップの情報が登録されている

・shop_review テーブル
ショップのレビュー情報が登録されている
ランキングで順位をつけるポイント数はこちらに登録

・同点の場合は同一順位、次点は同点の分を飛ばして順位をつける

元になる抽出用SQLを作る

・shop_review.point_total の平均を小数点１位まで出して降順で並べる
・同点の場合はレビュー件数が多いほうが先
・50件以上レビューがある
・10件まで

	SELECT
		sr.shop_id,
		shop.name AS 'shop.name',
		ROUND(AVG(sr.point_total), 1) AS 'avg_point_total',
		COUNT(sr.point_total) AS 'cnt_point_total'
	FROM
		shop_review sr
		INNER JOIN shop ON
			sr.shop_id = shop.id
	GROUP BY
		sr.shop_id
	HAVING COUNT(*) >= 50
	ORDER BY
		avg_point_total DESC,
		cnt_point_total DESC
	LIMIT 10 OFFSET 0

shop_id	shop.name	avg_point_total	cnt_point_total
12	ショップなんとか	5.0	123
1	まいショップ	4.8	148
14	いいお店	4.7	312
35	こちらもいい店です	4.6	5144
22	株式会社ショップ	4.6	1222
78	サンプル店舗	4.6	101
4	私はこちらが好きです店	4.5	464
9	いやこちらでしょう店	4.5	121
43	そろそろネタがない店	4.4	138
7	１０位の店	4.2	90

ランキングを付加

・@rank : ランキング
・@gap : 同一順位用のギャップ
・@rankval : 順位付けをする avg_point_total 保存用

SELECT
	@rank := CASE
		WHEN @rankval = avg_point_total THEN @rank
		ELSE @rank + @gap + 1
	END AS 'ranking',
	@gap := CASE
		WHEN @rankval = avg_point_total THEN @gap + 1
		ELSE 0
	END AS 'gap',
	@rankval := avg_point_total AS 'rankval',
	base.*
FROM
(
	SELECT
		sr.shop_id,
		shop.name AS 'shop.name',
		ROUND(AVG(sr.point_total), 1) AS 'avg_point_total',
		COUNT(sr.point_total) AS 'cnt_point_total'
	FROM
		shop_review sr
		INNER JOIN shop ON
			sr.shop_id = shop.id
	GROUP BY
		sr.shop_id
	HAVING COUNT(*) >= 50
	ORDER BY
		avg_point_total DESC,
		cnt_point_total DESC
	LIMIT 10 OFFSET 0
) AS base,
(SELECT @RANK:=0, @rankval:=NULL, @gap:=0) AS X

ranking	gap	rankval	shop_id	shop.name	avg_point_total	cnt_point_total
1	0	5.0	12	ショップなんとか	5.0	123
2	0	4.8	1	まいショップ	4.8	148
3	0	4.7	14	いいお店	4.7	312
4	0	4.6	35	こちらもいい店です	4.6	5144
4	1	4.6	22	株式会社ショップ	4.6	1222
4	2	4.6	78	サンプル店舗	4.6	101
7	0	4.5	4	私はこちらが好きです店	4.5	464
7	1	4.5	9	いやこちらでしょう店	4.5	121
9	0	4.4	43	そろそろネタがない店	4.4	138
10	0	4.2	7	１０位の店	4.2	90

以上、お疲れさまでした！