前回の続きみたいなもの。前回の後半のカイ二乗分布が出てきた当たりから、自分でもグダグダだなぁと感じてたんで、その辺の話の補強を。
N倍しろとかカイ二乗分布とかを考えたくない場合は、やっぱり素直に「ランダムに1つ取ってくる」操作を複数繰り返すべきとなります。というわけで、先の記事に示した1件のランダムを取ってくるSQL構文
select * from (select ri2.* from rinchan_images as ri2, (select max(id) as maxid from rinchan_images) as maxid, (select rand() as rnd) as rnd where ri2.id >= maxid.maxid*rnd.rnd and ri2.id < 1+(maxid.maxid)*rnd.rnd limit 1) as ri1;をムダに改良してみることにしました。
まず連番で飛びがない前提なのでfloorを使ってwhere部分をすっきりさせつつ、1件しか返ってこない前提なのでlimit 1もとります。ます。その上で、ランダム値がN個入ったテーブルを用意すればいいじゃないかということでそのへんを改良。
select ri.* from rinchan_images as ri, (select max(id) as maxid from rinchan_images) as maxid, (select rand() as rnd from rinchan_images limit N) as rnd where ri.id = floor(maxid.maxid*rnd.rnd);
これで完成・・・と思いきややっぱりまだ甘くて、rand()使ってる限りはN回呼んだらN個ランダムに引っ張ってこれるとは限らない。・・・てかこの問題ってNを増やせばいいとかの結局カイ二乗分布の話に行き着く・・・「時々N個にちょっと足りなくてもよい」なら、この構文使って N*4個くらい引っ張ってきて最後に limit N しとくのがたぶん無難なんだろうなぁ。
というわけで、根本的な問題「重複しないランダムな値をN個持ってくる」が残ります。これは一般的にはコストのかかる問題で、もう一般的には「重複しないN個がそろうまでひたすら乱数を振り続ける」しかなさそうです。確率問題なので「乱数を振り続けても一生N個そろわない」可能性だってあります・・・てか「振り続ける」んだから「一生」はないのか・・・
重複しないN個の乱数を引っ張ってくる処理は、SQLでもプロシージャとかつけばたぶんできるんでしょうけど、そこまでやるならスクリプト言語側でやった方がいいやということで、こんな感じの関数でも作った方がいいでしょう。
function make_random_list_string($maxnum, $count){
$tmp_random_array = array();
do {
$maked_num = rand(1, $maxnum);
if (in_array($maked_num, $tmp_random_array)){
continue;
}
$tmp_random_array[] = $maked_num;
} while (count($tmp_random_array) < $count);
return implode(',', $tmp_random_array);
}なんか「mt_randはrandよりも4倍速い」らしいけど、それはlibcのrand()関数が線形合同法だったりまだ怪しい時代だった時の話で、まともなrand()関数になった今時のlibcだとそういうことはないみたい。手元で試す限りmt_rand()の方が5%くらい早かったけどほとんど差がなし(PHP 5.1.6, glibc 2.5-107.el5_9.5) であとはこの関数の結果を where id in とかにしとけば完成と $sql = 'select * from rinchan_images where id in (' . make_random_list_string($maxnum, $count) . ');';
ただこれだと、見つかった$count個のレコードがテーブルに格納されていた順に取り出されてしまう。たいていはレコードが挿入された順かな。これを防ぐためには、見つかったレコードをもう一度order by rand()しなおすしかないと。
$sql = 'select * from rinchan_images where id in (' . make_random_list_string($maxnum, $count) . ') order by rand();';ちなみに今は「where id in (...)」の...が定数値だからあまり問題ないですが、ここが複雑なサブクエリになると最適化がいろいろ怪しいらしいので、書き方要注意のようです。
こんどこそ完成・・・と思いきやまだ穴があって、このやり方だと、このSQLを送るタイミングと、$maxnumを取得するために先に実行しておく予定の「select max(id)」を送るタイミングとの間に隙間があって、そこでupdateとかinsertとかが起こっているといろいろめんどい。そうそう気にするとこじゃないけど、そこまで気を回すならばトランザクション使っとくしかないよね。この場合必要なトランザクション分離レベルはREPEATABLE READ以上になるのかな。
てなわけで、いろいろ考えてみたもののやっぱりこれは一般には簡単には解決できない問題のようです。
「続々: SQL使ってテーブルからランダムにN件引っ張りたい場合」にさらに続いてしまったようだ。
