Snowflakeでおみくじ引いてみた

分析屋

2023年4月24日 18:50

分析屋の中田(ナカタ)です。
Snowflakeでランダム抽出をしてみました。

作りたいもの

初詣で神社に行くと必ず引く、アレを作ります。
そうです、「おみくじ」を作ってみます。

手順

おみくじ箱を作ります。
おみくじマスタを作ります。
連番マスタを作ります。
おみくじ箱にくじを投入します。
ランダムに１枚引きます。

最後に、ランダム性のテストも行います。

環境

エディション：スタンダード版(トライアル期間)
ウェアハウス：Medium 1台
SnowflakeのワークシートでSQL文を実行していきます。

1.空のおみくじ箱テーブルを作成

--おみくじ箱の作成
CREATE OR REPLACE TABLE public.omikuji_box (
    kuji_name VARCHAR(10)
);

2.おみくじマスタテーブルを作成

--おみくじマスタの作成
CREATE OR REPLACE TABLE public.omikuji_mst (
    kuji_name VARCHAR(10)
    ,amount NUMBER(7,0)
);

INSERT INTO public.omikuji_mst(kuji_name,amount)
VALUES('大吉',5000000);
INSERT INTO public.omikuji_mst(kuji_name,amount)
VALUES('中吉',4000000);
INSERT INTO public.omikuji_mst(kuji_name,amount)
VALUES('吉',3000000);
INSERT INTO public.omikuji_mst(kuji_name,amount)
VALUES('凶',2000000);
INSERT INTO public.omikuji_mst(kuji_name,amount)
VALUES('大凶',1000000);

くじごとに枚数を変えています。

例えば
VALUES('大吉',5000000);
については、「大吉」を500万枚作成することを意味します。
悪いくじになるほど、確率を下げるようにしています。

3.連番マスタテーブルを作成

　Snowflakeのワークシートで以下のSQL文を実行

--連番マスタ
CREATE OR REPLACE TABLE public.sequence_table AS
    SELECT
        ROW_NUMBER() OVER(ORDER BY 1) AS sequence
    FROM
        TABLE(GENERATOR(ROWCOUNT=>5000000)
);

くじを大量作成するために使うテーブルです。
SQLで連番を作成する方法はいろいろありますが
Snowflakeでは上記の書き方で1番から500万番まで作成できます。

(ROWCOUNT=>5000000)
「ROWCOUNTが500万以上」という比較演算子（>=）に間違えそうですが
「ROWCOUNTは500万」という意味の矢印（⇒）です。

4.おみくじ箱にくじを投入

--おみくじ投入
INSERT INTO public.omikuji_box(kuji_name)
    SELECT 
        o.kuji_name
    FROM 
        public.omikuji_mst o
    INNER JOIN 
        public.sequence_table s
    ON  o.amount >= s.sequence
;

おみくじマスタと連番マスタを比較演算子で結合しています。
これにより、おみくじマスタで指定した枚数分、レコードができあがります。

おみくじ箱の中身を確認してみましょう。

--結果確認
SELECT
    kuji_name
    ,COUNT(*)
FROM 
    public.omikuji_box
GROUP BY 
    kuji_name
;

5.くじを引いてみる

一礼し、清い心で以下のコードを実行してみましょう。

--ランダム抽出
SELECT 
    kuji_name
FROM 
    public.omikuji_box
SAMPLE (1 ROWS)
;

私の場合はこうなりました。

くじの引き直しはしませんが、日頃の行いがいいはずの私が大凶を引いたということは腑に落ちません。

それはさておき、テーブルからランダムにレコードを抽選する方法について
一般的な方法は、「乱数の列を付与して並べ替え、上位のレコードを抽出する」などですが
ここではSAMPLE関数を使用しています。

抽出したい行数だけでなく、割合での指定もできます。
詳しくは公式ドキュメントを参照してください。

https://docs.snowflake.com/ja/sql-reference/constructs/sample

ランダム性の検証

おみくじ箱の中身全体と同じ割合で抽選できているのか？
以下の方針で検証してみます。

繰り返し1000回のくじ引きをして、結果をテーブルに記録します。
1000回引いたくじの内訳を確認し、おみくじ箱の全体と近い分布になっているか確認します。

検証用の空テーブルを作成

--検証テーブル
CREATE OR REPLACE TABLE public.test_kuji(
    kuji_name varchar(10) 
);

1000回のくじ引きを行います。

--1000回の復元抽出
TRUNCATE TABLE public.test_kuji;
DECLARE
  max_cnt INTEGER default 1000;
BEGIN
    FOR i IN 1 TO max_cnt DO

        INSERT INTO public.test_kuji(kuji_name)
            SELECT 
                kuji_name
            FROM 
                public.omikuji_box
            SAMPLE (1 ROWS);

            END FOR;
END;

合計1500万枚のくじから1枚引く抽選を、1000回繰り返しています。
私の環境(Medium1台)で所要時間10分程度です。

一度に1000枚引いて終わりのクエリであればはるかに速いはずですが
それだといわゆる非復元抽出になり、今回の検証と条件が変わるため
復元抽出になるようにしています。

エンタープライズ版のウェアハウスで、複数台で分散処理の設定をすると
所要時間を抑えられるはずです。

検証テーブルの内訳

最後に、検証テーブルの内訳を確認しましょう。

--検証テーブルの内訳
SELECT
    kuji_name
    ,count(*)
FROM
    public.test_kuji
GROUP BY
    kuji_name
ORDER BY 2 DESC
;

厳密な統計的検定ではありませんが
おみくじ箱全体の内訳（5:4:3:2:1）と近い比率になっていることが分かります。

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

株式会社分析屋について

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。

この記事が参加している募集

#やってみた

37,428件