Help us understand the problem. What is going on with this article?

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした