SQLでちゃんとしたランキングを作る【1/3】

SQLを使ってランキングを表示させるなんてことをやったことがある人もいるだろう。
たとえばメンバー（訪問者という意味でのユーザは2種類あって、登録ユーザをメンバー、非登録ユーザをビジターと呼んでいる）にポイント属性が設定されていて、そのポイントを毎日集計する、そしてその結果、上位から10件取り出せば、ランキングデータが1位までそろう、というものだ。

ちょっと待て！！

それたぶん、インチキ！！

MySQLであれば、

select * from histories
order by point desc
limit 10

と吐けば、pointが高い順に上から10件の結果を得ることはできるが、ただ10件取得するだけで、ランキングとして正しいかどうかは別問題だ。

というのも、同位の場合があるからだ。

ある2人のユーザが同ポイントだった場合、内部ではpointではなくidや登録日などの順で順位が変わってしまうということになる。正直これは不本意だ。

というわけで、いくつかパターンを考えてみる。以下は、3位が二人いる場合を例にとってみた。

	順位シーケンシャル	順位スキップ
10件まで	1 2 3 3 4 5 6 7 8 9	1 2 3 3 5 6 7 8 9 10
10位まで	1 2 3 3 4 5 6 7 8 9 10	1 2 3 3 5 6 7 8 9 10

これだと、右下の「順位スキップ」＋「10位まで」がよさそうだが、実はそうじゃない。
さらに以下は、3位が二人、8位が3人いる場合になる。

	順位シーケンシャル	順位スキップ
10件まで	1 2 3 3 4 5 6 7 8 8	1 2 3 3 5 6 7 8 8 8
10位まで	1 2 3 3 4 5 6 7 8 8 8 9 10	1 2 3 3 5 6 7 8 8 8 9 10

「順位スキップ」＋「10位まで」だと、同位がたくさんいればいるほど表示件数が増えてしまうことになる。
これは別に「順位シーケンシャル」でも同じだ。とにかく「10位まで」というのは件数が増えることになる。

したがって、「10件」という限定をさせておくことにする。
ただ、この「10件」というのを単純にlimitで10件取得するというのがまずいということだ。

というわけで、10件表示させるということに決定したわけだが、よく見てみると、「10件」＋「順位シーケンシャル」だと、3人いるはずの8位が2人までしか取得できてないことがわかるはずだ。

これでは意味がない。

ということで、正解としては「10件まで」＋「順位スキップ」ということになると思う。

しかし、もし2位が10名いた場合どうなるかというと、

	順位シーケンシャル	順位スキップ
10件まで	1 2 2 2 2 2 2 2 2 2	1 2 2 2 2 2 2 2 2 2
10位まで	1 2 2 2 2 2 2 2 2 2 2 3 4 5 6 7 8 9 10	1 2 2 2 2 2 2 2 2 2 2

これはまずい。
「10件」という条件だと、2位が9名までしか表示されてない。これは10件までという制限があるから途中で切れてしまっているのだ。
だったら「10位」の条件でということになるが、その場合、べらぼうに件数が増えてしまう。

というわけで、少々難儀な話になってきた。


ここでいったんSQLから離れ、実際にHTMLでWebページ上に表示させる場合、どういったものがあるのかで考えてみた。

大きく分けて2種類になると思う。

順位	名前
1	太郎
2	次郎
3	三郎
3	四朗
5	五郎
6	六郎
7	七郎
8	八郎
8	九郎
8	十郎

結局、書いてある順位が同じではあるが、名前の表示順はID順であったり登録日順であったりしてしまう。
書いてある本人からすれば、せめて同じマス（セル）に入れてほしいと思うかもしれない。

つまり、

順位	名前
1	太郎
2	次郎
3	三郎 四朗
5	五郎
6	六郎
7	七郎
8	八郎 九郎 十郎

こんな感じが理想なのかもしれない。
しかしさらに考えてみよう。

これだと2位が10名いる場合、やっぱり9名分しか表示されなくなる。

なので、必ず10位まで表示させ、各順位に該当するメンバーすべてを表示させる、というのはどうだろうか。

順位	名前
1	太郎
2	次郎
3	三郎、四朗
4	五郎
5	六郎
6	七郎
7	八郎、九郎、十郎
8	null
9	null
10	null

これなら2位が10人いても100人いても、レイアウトデザイン以外では破たんがない。
つまり「10位まで」＋「順位シーケンシャル」の順位をグループ化して10位までとするのが正解ということになる。

そしてSQLに話が戻るが、上記のレイアウトデザインで行くことにすると、順位というのを件数で取得してはいけなくなる。どういうことかというと、pointをグループ化してそれを上から10件取得しなければならない。
さらにそれぞれ該当するpointを持っているメンバーを拾ってこなければならない。

単純に考えると、pointをグループ化で1個、1位から10位までpointを持つメンバーを取得で10個、合計11個のSQLが発行されることになる。

たとえば1回目のSQLでpointを一通り取得したとする。
その次に取得したpointをforeachなどで反復処理させながら、ユーザ関数などで1反復ごとにメンバーを取得する、という方法は昔よくやったもんだ。

しかし、開発者としてこれはよろしくないと思っている。なぜなら、必ず最低「順位」＋「1回」のSQLが走るので、無駄な問い合わせが順位を増やすごとに発生してしまう。


これをどうやって少ないSQLにするか、が今回の目的だ。