1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【MySQL】SQLでランキング 【ユーザ定義変数】

Last updated at Posted at 2020-12-28

【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 の平均を小数点1位まで出して降順で並べる
・同点の場合はレビュー件数が多いほうが先
・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 10位の店 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 10位の店 4.2 90

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

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?