【 MySQL 】「重み付けをしてランダムに表示」をSQLだけでやる方法

はじめに

何かのリストのテーブルから１レコードランダムに取り出したい時、選ばれる頻度を重みをつけて調整したいことがあります。
例えば、おすすめの商品をランダムに表示する際、普段はお手頃な商品を表示するけど、たまーに高額の商品を表示したいような時です。
上手いやり方がないかと思い調べてみたのですが見当たらなかったので少し雑ですが次のような方法を考えてみました。

二重に抽選を行うことで「重み付けをしてランダムに表示」

例えば次のようなテーブル（suggestion）があったとします。

このテーブルから「score」が高いほど頻度高くランダムに「product」を取り出したい場合、

SELECT *
FROM suggestion
WHERE score >= FLOOR(RAND( ) * 100 )
ORDER BY RAND( )
LIMIT 0 , 1

こんな感じで実現できます。
これは二重で抽選をしている感じなのですが、まずはじめに

WHERE score >= FLOOR(RAND( ) * 100 )

この部分で「10〜100」のランダムな数字を生成し、その数字よりも「score」が大きいもので絞っています。
この数字が例えば「68」だった場合に抽出されるのは下記の通り、

この数字が例えば「40」だった場合に抽出されるのは下記の通りとなります。

つまり「score」の数字が大きいほど抽出される頻度は高くなり、「score」が100のみかんは必ず抽出の対象となります。
これだけだと何度やってもみかんが選ばれてしまうので、

ORDER BY RAND( )
LIMIT 0 , 1

の部分で抽出されたものの中からさらにランダムに抽選を行なっています。

実際の頻度はどうなるか

何となくそれっぽい実装になってはいるのですが、上記を実際に実行した場合のそれぞれの出現頻度はどの程度になるのでしょうか？
例えばscoreが100のみかんの場合で考えてみます。
みかんは1回目の絞り込みで対象外になることはありません。そして、2回目の絞り込みでは1回目に絞り込まれた件数によって選ばれる確率が異なります。1回目の絞り込みで残る件数はどの場合も1/10なので、
「1回目の絞り込みで10個残り、その上で、みかんが選ばれる確率」は

1/10 * 1/10

となります。
これを「1回目で9個、8個」とやっていくと

1/10*1/10 + 1/10*1/9 + 1/10 * 1/8 + ... + 1/10 * 1/1 = 0.2929

となり、みかんが選ばれる確率は約29%ということになりました。
続いてscoreが90のりんごは

「1回目の絞り込みで1個残り、その上で、りんごが選ばれる確率」は0なので

1/10*1/10 + 1/10*1/9 + ... + 1/10 * 1/2 + 0 = 0.1929

となり、約19%となります。同様に繰り返していくと下記のようになりました。

グラフにしてみると

線形にはなっておらず、左に行くほど勾配がついています。
つまり、選ばれやすいものと選ばれにくいものは顕著に結果として現れやすい選び方であると言えます。

今回は10項目×10 scoreでやっているのでわかりやすいですが、同じスコアを持つ項目が複数ある場合などには注意が必要そうです。
例えば「100」というスコアを持つ項目が1万個あり、「1」というスコアを持つ項目が1つだけあった場合、「1」のスコアの項目が出る確率は1/100なのに対して、「100」のスコアを持つ項目は約1/10000の確率でしか出現しないことになります。

最後に

SQLはいろんな言語と組み合わせて利用しますが、
これからプログラムを習得しようという時、結局どの言語が1番コスパがいいのかを出してみました。
すでにプログラマーの方もぜひ参考にしてみてください。