SQLで参加者から一人選ぶくじ引きを行う

TL;DR

• 手続き型言語には頼りません
• SQLで参加者からランダムに一人を抽出します。ええそうです、SQLです。

環境

今回利用したOracleデータベースのバージョン

データベースバージョン

SELECT * FROM V$VERSION;

> Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
> PL/SQL Release 11.2.0.4.0 - Production

データ準備

「LIST」という名称のテーブルに参加者を登録します。
今回の登録データは某有名バンドから拝借しました。

参加者登録.sql

CREATE TABLE LIST AS
    SELECT '川上' AS NM FROM DUAL
    UNION ALL
    SELECT '磯辺' AS NM FROM DUAL
    UNION ALL
    SELECT '白井' AS NM FROM DUAL
    UNION ALL
    SELECT '庄村' AS NM FROM DUAL
;

実行

ランダムな数を取得する必要があるためdbms_random.random()ファンクションを用いて実装します。
また、ROWNUMとかバリバリOracle依存してます

くじ引きSQL本体.sql

SELECT
    LST.NM
FROM
    (   SELECT
            NM
        ,   ROWNUM  AS NUM
        FROM
            LIST
    ) LST
, (
    SELECT
        TO_NUMBER(ABS(MOD(dbms_random.random(), (SELECT COUNT(*) FROM LIST)))) + 1  AS NUM
    FROM
        DUAL
    WHERE
        ROWNUM = 1
    ) RDM
WHERE 1 = 1
AND LST.NUM = RDM.NUM
;

検索結果（実行時依存で値が変わります）

NM
白井

検証

何となくランダムに動いてくれていそうな気がしますが
ちゃんと散らばっているかどうか検証しましょう。

本来の検証としては悪手な気がしますが、
ランダムな値を生成する処理をconnect byに書き換えて値が散らばっているか確認します。
今回は100万回動かしてみます。

検証SQL

SELECT
    LST.NM
,   COUNT(*)    AS CNT
FROM
    (   SELECT
            NM
        ,   ROWNUM  AS NUM
        FROM
            LIST
    ) LST
, (
    SELECT
        TO_NUMBER(ABS(MOD(dbms_random.random(), (SELECT COUNT(*) FROM LIST)))) + 1  AS NUM
    FROM
        DUAL
    CONNECT BY
        LEVEL < 1000000
    ) RDM
WHERE 1 = 1
AND LST.NUM = RDM.NUM
GROUP BY
    LST.NM
;

• 結果 そこそこ散らばっている気がしますね！

NM	CNT
川上	250266
白井	249057
庄村	250712
磯辺	249964

まとめ

• SQLでもちゃんとくじ引きが出来ました！作ってみればお手軽に実行できるので便利です。
• よいSQLライフを！