Edited at

15章 Random Selection(ランダムセレクション)

More than 3 years have passed since last update.


15.1 目的:サンプル行をフェッチする

ランダムな結果を返すSQLクエリ、実務でも結構あります。


  • 広告

  • ブログの「この記事を見た人は~」みたいな似非レコメンドエンジン

  • 担当、当番のアサイン(今日のお菓子担当)

  • テストデータ表示

  • ソシャゲのガチャ(最近の流行?として)


15.2 アンチパターン:データをランダムにソートする


Random/anti/orderby-rand.sql

SELECT * FROM Bugs ORDER BY RAND() LIMIT 1


インデックス効かない弱点があるんですね。ここからアンチパターンですか…

Limitの部分を3とか5にすると普通に広告とかで使いそうです。

RAND()はMySQL固有関数。SQL標準ではないです。


15.3 アンチパターンの見つけ方

ランダムの関数が簡単なので、以下のような発言を聞いたら怪しめ、みたいな感じです。


  • 「SQLは、行をランダムに返そうとすると本当に遅くなるな」

  • 「アプリケーションへのメモリ割り当てを増やしたいんだけど。いったんすべての行をアプリケーション上にフェッチしないと、ランダムに1行を選択できないんだ」

  • 「一部のエントリは、他に比べて表示される頻度が高いように見える。この乱数発生器は平等に乱数を生成しているのだろうか?」

最後のはガチャが当たらない愚痴かなんかでしょう…と思いきや、主キー次第ではあるようです。


15.4 アンチパターンを用いてもよい場合


  • データが少ない

  • バッチとか、パフォーマンスを気にしない


15.5 解決策:特定の順番に依存しない


15.5.1 1と最大値の間のランダムなキー値を選択する

JOINする割に苦労する、SQLサーバに依存した例その1


Random/soln/rand-1-to-max.sql

SELECT b1.*

FROM Bugs AS b1
INNER JOIN (
SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id
) AS b2 ON b1.bug_id = b2.rand_id;


15.5.2 欠番の穴の後にあるキー値を選択する

JOINする割に苦労する、SQLサーバに依存した例その2

Random/soln/next-higher.sql

SELECT b1.*
FROM Bugs AS b1
INNER JOIN (
SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS bug_id
) AS b2 ON b1.bug_id >= b2.bug_id
ORDER BY b1.bug_id
LIMIT 1;


15.5.3 すべてのキー値のリストを受けとり、ランダムに1つを選択する

PHPで全部キーを取得し、ランダムでピックアップ、そのキーで1行取得。

データが多い時のアンチパターン回避策にしてはお粗末なコードに見えますが…


Random/soln/rand-key-from-list.php

 <?php

$bug_id_list =
$pdo->query("SELECT bug_id FROM Bugs")->fetchAll(PDO::FETCH_ASSOC);

$rand = rand( 0, count($bug_id_list) - 1 );
$rand_bug_id = intval($bug_id_list[$rand]['bug_id']);

$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id = ?");
$stmt->bindValue(1, $rand_bug_id, PDO::PARAM_INT);
$stmt->execute();
$rand_bug = $stmt->fetch();



15.5.4 オフセットを用いてランダムに行を選択する

これ以外に何がある?という感じなんですけど…。

インデックスが効いてるカラム、可能であれば主キーを利用するパターンですね。


Random/soln/limit-offset.php

<?php

$rand_sql = "SELECT ROUND(
RAND() * (SELECT COUNT(*) FROM Bugs)
) AS id_offset"
;
$result = $pdo->query($rand_sql)->fetch(PDO::FETCH_ASSOC);
$offset = intval($result['id_offset']);

$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rand_bug = $stmt->fetch()



  • count()で行の最大値を取る

  • 0~最大値からプログラム側でランダム値を出す

  • order byはインデックスが効いてるカラムを指定し、limit、offsetはプログラム側で準備したものを指定

このパターンが使えない時、何やってもだめな気がします。DBのGRANT問題?でJOIN強制の時ですかね。

と思ったらこの解決策では、SQL標準には無いLIMIT句を使用しています。LIMIT句は、MySQL、PostgreSQL、SQLiteでサポートされています。と書いてあって、MySQLとPostgreSQLしかしらない私は驚愕しました(汗


Random/soln/row_number.php

 <?php

$rand_sql = "SELECT 1 + MOD(ABS(dbms_random.random()),
(SELECT COUNT(*) FROM Bugs)) AS id_offset FROM dual"
;
$result = $pdo->query($rand_sql)->fetch(PDO::FETCH_ASSOC);
$offset = intval($result['id_offset']);

$sql = "WITH NumberedBugs AS (
SELECT b.*, ROW_NUMBER() OVER (ORDER BY bug_id) AS RN FROM Bugs b
) SELECT * FROM NumberedBugs WHERE RN = :offset"
;
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();


あと、1件じゃなくて複数取得したい場合、割とこのパターンではまります。SELECT文の発行回数が多くなるので…

なので、ランダムキー用ハッシュを収めたカラムを準備し、それでソートする手法を見たことがあります。


  • md5?のハッシュを数文字収めたカラムでorder byし、プログラムで作ったランダム行数から必要行数(5行ずつ?とか)取得

  • 表示

  • ハッシュカラムは日次なりでupdateし直す

結果が寄り易いですが、一度で取る行数が1/5とかになるので、苦肉の策として用意しました


15.5.5 ベンダー依存の解決策

それがあるならそれ使いますという内容です

Microsoft SQL Server 2005ではTABLESAMPLE句、OracleではSAMPLE句があるようです。