【SQL】ランダムにソートしてデータを取得する方法｜DBMS別構文・高速化・実践パターン完全ガイド

-- 全件をランダム順に取得
SELECT * FROM products
ORDER BY RAND();

-- ランダムに5件取得
SELECT * FROM products
ORDER BY RAND()
LIMIT 5;

-- 全件をランダム順に取得
SELECT * FROM products
ORDER BY RANDOM();

-- ランダムに5件取得
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

-- ランダムに5件取得
SELECT * FROM products
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST 5 ROWS ONLY;

-- ROWNUMを使う古い書き方
SELECT * FROM (
    SELECT * FROM products
    ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 5;

-- ランダムに5件取得
SELECT TOP 5 *
FROM products
ORDER BY NEWID();

EXPLAIN SELECT * FROM products ORDER BY RAND() LIMIT 5;
-- type: ALL（フルスキャン）
-- Extra: Using temporary; Using filesort
-- ← 一時テーブル作成 + 全行ソートが走る

-- ランダムな1件を高速取得
SELECT * FROM products
WHERE id >= FLOOR(1 + RAND() * (SELECT MAX(id) FROM products))
LIMIT 1;

-- ランダムな5件を取得（重複の可能性あり）
SELECT p.*
FROM products p
JOIN (
    SELECT FLOOR(1 + RAND() * (SELECT MAX(id) FROM products)) AS rand_id
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
          UNION ALL SELECT 4 UNION ALL SELECT 5) AS n
) AS r ON p.id >= r.rand_id
GROUP BY r.rand_id
HAVING COUNT(*) > 0;

WITH numbered AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY id) AS rn,
           COUNT(*) OVER ()                AS total
    FROM products
),
random_picks AS (
    SELECT FLOOR(1 + RAND() * total) AS pick
    FROM (SELECT DISTINCT total FROM numbered) t
    -- 5件分の乱数を生成
    CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                UNION ALL SELECT 4 UNION ALL SELECT 5) n
)
SELECT n.*
FROM numbered n
JOIN random_picks rp ON n.rn = rp.pick;

-- SYSTEM: ブロック単位サンプリング（高速・非均一）
-- 全行の約10%をサンプリング
SELECT * FROM products TABLESAMPLE SYSTEM (10);

-- BERNOULLI: 行単位サンプリング（均一・やや低速）
SELECT * FROM products TABLESAMPLE BERNOULLI (5)
ORDER BY RANDOM()
LIMIT 10;

-- REPEATABLE(seed) で再現可能なサンプリング
SELECT * FROM products
TABLESAMPLE SYSTEM (10) REPEATABLE (42);

-- 全行の約10%をサンプリング
SELECT * FROM products TABLESAMPLE (10 PERCENT);

-- 行数で指定する方法
SELECT * FROM products TABLESAMPLE (100 ROWS);

-- REPEATABLE(seed) で再現可能
SELECT * FROM products
TABLESAMPLE (10 PERCENT) REPEATABLE (12345);

-- BLOCK SAMPLE: ブロック単位（高速）
SELECT * FROM products SAMPLE BLOCK (10);

-- ROW SAMPLE: 行単位（均一）
SELECT * FROM products SAMPLE (5);

-- シード付きサンプリング
SELECT * FROM products SAMPLE (10) SEED (42);

-- シード42を固定すると毎回同じ順序
SELECT * FROM products
ORDER BY RAND(42)
LIMIT 10;

-- 日付をシードにすることで「今日だけ固定」にする
SELECT * FROM products
ORDER BY RAND(UNIX_TIMESTAMP(CURDATE()))
LIMIT 5;

-- SELECTの前にSETSEED()でシードを設定
-- SETSEED(-1.0 ～ 1.0 の範囲で指定)
SELECT SETSEED(0.42);
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 10;

-- セッションレベルでシードを設定
BEGIN
    DBMS_RANDOM.SEED(42);
END;
/

SELECT * FROM products
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST 10 ROWS ONLY;

-- 整数シードを指定（0 ～ 2^31-1）
-- ただしNEWID()にシード指定はできないため
-- CHECKSUM(RAND(42), col) を使う
SELECT TOP 10 *
FROM products
ORDER BY CHECKSUM(RAND(42), id);

-- エントリーテーブルから1名をランダム抽選
SELECT user_id, user_name, entry_at
FROM lottery_entries
WHERE campaign_id = 100
  AND is_valid = 1
ORDER BY RAND()
LIMIT 1;

-- 当選者をトランザクション内で確定させる
BEGIN;
UPDATE lottery_entries
SET is_winner = 1
WHERE entry_id = (
    SELECT entry_id
    FROM (
        SELECT entry_id FROM lottery_entries
        WHERE campaign_id = 100 AND is_valid = 1
        ORDER BY RAND() LIMIT 1
    ) t
);
COMMIT;

-- カテゴリ「SQL基礎」から難易度別に3問ずつランダム出題
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY difficulty
               ORDER BY RAND()
           ) AS rn
    FROM quiz_questions
    WHERE category = 'SQL基礎'
      AND is_active = 1
)
SELECT question_id, difficulty, question_text
FROM ranked
WHERE rn <= 3
ORDER BY difficulty, rn;

-- MySQL: MOD(CRC32(user_id), 2) で0=A群 / 1=B群
SELECT
    user_id,
    CASE MOD(CRC32(user_id), 2)
        WHEN 0 THEN 'A'
        ELSE 'B'
    END AS ab_group
FROM users;

-- PostgreSQL: MD5 + ハッシュで3グループ均等分割
SELECT
    user_id,
    CASE MOD(ABS(hashtext(user_id::text)), 3)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        ELSE 'C'
    END AS ab_group
FROM users;

-- PostgreSQL: 100万件テーブルから1%をサンプリング
SELECT
    COUNT(*) AS sample_count,
    COUNT(email) AS email_filled,
    COUNT(phone) AS phone_filled,
    ROUND(100.0 * COUNT(email) / COUNT(*), 1) AS email_rate,
    ROUND(100.0 * COUNT(phone) / COUNT(*), 1) AS phone_rate
FROM customers
TABLESAMPLE BERNOULLI (1);

-- Oracle: 5%をサンプリングして年代別件数を確認
SELECT
    TRUNC(age / 10) * 10 AS age_group,
    COUNT(*)              AS cnt
FROM customers SAMPLE (5)
GROUP BY TRUNC(age / 10) * 10
ORDER BY age_group;

-- 名前・金額・日付をランダムに生成して100件INSERT
INSERT INTO orders (customer_name, amount, order_date)
SELECT
    CONCAT('Customer_', n),
    ROUND(1000 + RAND() * 99000),   -- 1,000〜100,000
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)  -- 過去1年
FROM (
    SELECT a.n + b.n * 10 AS n
    FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
          UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) a
    CROSS JOIN
         (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
          UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    WHERE a.n + b.n * 10 BETWEEN 1 AND 100
) nums;

INSERT INTO orders (customer_name, amount, order_date)
SELECT
    'Customer_' || n,
    ROUND((1000 + RANDOM() * 99000)::numeric, 0),
    CURRENT_DATE - (RANDOM() * 365)::int
FROM generate_series(1, 100) AS n;