SQLだけでランダム抽出

SQLだけでrandomに値を抽出する方法です。
SQLServerを題材に実際に抽出するクエリのサンプルを用いて説明します。

やりたいこと

ユーザごとにお気に入り登録している商品があり、商品は複数存在するカテゴリーのどれかに属しているとします。全てのユーザーに対しカテゴリ毎にランダムで1件抽出して、カテゴリーの種類数分の商品をユーザに紐づけることを目指します。

データ

CREATE TABLE Member (
    id int,
    name text
);

CREATE TABLE MemberFavoriteItem (
    member_id int,
    item_id int
);

CREATE TABLE Item (
    id int,
    category_id int,
    name text
);

-- 最終的に作りたいデータ
CREATE TABLE UserRandomCategoryItem (
    member_name text,
    category_id int,
    item_name text
)

抽出方法

STEP 1

全ての対象データを結合し、ウィンドウ関数で分割したデータの中にランダムな連続する値を持たせます。ポイントはウィンドウ関数でランダム化したい単位に分割して、カテゴリーの中に含まれるitem_idの中でランダムな値を割り振っているところです。
注意点
* SQLServerではテーブル名の先頭に#をつけるとtempテーブル
* NEWID()によってレコード毎にユニークな文字列を付与し、それを基準にORDER BYした結果にROW_NUMBERを付与することでランダムな順序を実現している。NEWID()ではなくRAND()を使いたかったが、シードをユニークにしないと値が同じになってしまうので使っていない

STEP2

ランダムに割り振った数値を見て必要な個数分where句で絞ります。
※ウィンドウ関数の結果は直接whereで指定できないので、一度tempテーブルを経由するか、サブクエリとして用いる必要があります。

実際のクエリ

-- STEP1
SELECT
    Member.name AS member_name,
    Item.category_id AS category_id,
    Item.name as item_name,
    ROW_NUMBER() OVER(PARTITION BY member_id, category_id ORDER BY NEWID()) AS random_num
INTO #AggregateResult
FROM
    Member
    INNER JOIN MemberFavoriteItem ON Member.id = MemberFavoriteItem.member_id
    INNER JOIN Item ON MemberFavoriteItem.item_id = Item.id
;

-- STEP2
INSERT INTO MemberRandomCategoryItem
SELECT
    member_name,
    category_id,
    item_name
FROM
     #AggregateResult
WHERE
    -- カテゴリ毎に複数種類欲しいなら数を変える
    random_num <= 1

SQLServerでやってみる

検証用データを用意する

INSERT INTO Member
VALUES
    (1, 'hoge'),
    (2, 'hero'),
    (3, 'fuga')
;

INSERT INTO MemberFavoriteItem
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (1, 5),
    (1, 6),
    (1, 7),
    (1, 8),
    (1, 9),
    (2, 1),
    (2, 2),
    (2, 3),
    (2, 4),
    (2, 5),
    (2, 6),
    (2, 7),
    (2, 8),
    (2, 9),
    (3, 1),
    (3, 2),
    (3, 3),
    (3, 4),
    (3, 5),
    (3, 6),
    (3, 7),
    (3, 8),
    (3, 9)
;

INSERT INTO Item
VALUES
    (1, 1, 'apple'),
    (2, 1, 'orange'),
    (3, 1, 'lemon'),
    (4, 2, 'chocolate'),
    (5, 2, 'cookie'),
    (6, 2, 'candy'),
    (7, 3, 'tea'),
    (8, 3, 'milk'),
    (9, 3, 'coffee')

結果

全てのメンバーに対してカテゴリ毎に1件ランダムで結果が返ってきました。

実行するたびに結果は変わります。