【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 |
以上、お疲れさまでした！