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

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句があるようです。