BigQueryで確率乱数を生成【Sexy Tech for You #2】

データサイエンティストの阿部です。最近来てくれたインターン生と10歳違いなことに驚きました。光陰矢の如し！

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第2話を配信しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

BigQuery クエリ

正規乱数

#StandardSQL
-- ボックス＝ミュラー法に基づく正規乱数発生関数
CREATE TEMPORARY FUNCTION RNORM(mu FLOAT64, sigma FLOAT64) AS (
  (SQRT(-2*LOG(RAND()))*SIN(2*ACOS(-1)*RAND())) * sigma + mu  
);

-- 10万件サンプリング
SELECT
    sample_id,
    RNORM(171.2, 5.5) AS sample_hight  -- 関数の入力値は平均と標準偏差
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS sample_id

指数乱数

#StandardSQL
-- 逆関数法による指数乱数発生関数
CREATE TEMPORARY FUNCTION REXP(lambda float64) AS (
    -(1/lambda) * LOG(1-RAND()) 
);

-- 10万件サンプリング
SELECT
    sample_id,
    REXP(0.01) AS sample_interval
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS sample_id

ポアソン乱数

#StandardSQL

-- 指数乱数発生関数
CREATE TEMPORARY FUNCTION REXP(lambda float64) AS (
    -(1/lambda) * LOG(1-RAND()) 
);

-- ポアソン分布のパラメータ ラムダを指定
CREATE TEMPORARY FUNCTION lambda() AS (
    0.05
);

-- B*Cコの指数乱数列を発生させて、累積和が1以上に到達する乱数の個数を取得する試行をA回繰り返して、ポアソン乱数のテーブルを作成する
CREATE TEMP TABLE rpois_set AS (
SELECT 
    *,
    ROW_NUMBER()OVER() AS rownum
FROM(
SELECT
    session,
    COUNT(1) - 1 AS rpois_val
FROM(
SELECT 
    *
FROM(
SELECT 
    *,
    SUM(start_flg)OVER(ORDER BY trial ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
FROM(
SELECT 
    ROW_NUMBER()OVER() AS trial,
    REXP(lambda()) AS rexp,
    IF(B=1 AND C=1, 1, 0) AS start_flg
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS A, -- セッション数。現在のBigQueryの生成数限界は POWER(2,20)-1
    UNNEST(GENERATE_ARRAY(1, CEIL(lambda()))) AS B, -- B*Cが試行回数。ポアソン分布のパラメータ ラムダの10倍としている
    UNNEST(GENERATE_ARRAY(1, 10)) AS C
))
WHERE 
    trial IS NOT NULL -- QUALIFY句利用の際にWHERE指定が必要なため。特に意味はない
    QUALIFY IFNULL(SUM(rexp)OVER(PARTITION BY session ORDER BY trial ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) < 1 
)
GROUP BY
    1
HAVING 
    SUM(rexp) >= 1
));

-- ポアソン乱数テーブルから乱数を取得する関数を定義
CREATE TEMP FUNCTION RPOIS(target_rownum int64)AS ((
    SELECT rpois_val
    FROM rpois_set
    WHERE rownum = target_rownum
));

-- テーブル行数以上のサンプリングをする場合に備えて行数取得
CREATE TEMPORARY FUNCTION limit_sample_num() AS ((
    SELECT MAX(rownum) FROM rpois_set
));


--100万件サンプリング
SELECT 
    sample_id,
    RPOIS(target_rownum) AS sample_action_cnt
FROM(
SELECT
    sample_id,
    IF(MOD(sample_id,limit_sample_num())!=0, MOD(sample_id,limit_sample_num()), limit_sample_num()) AS target_rownum --乱数テーブルの行数以上のサンプル数が必要な場合に備えて、テーブル行数で剰余を取る
FROM
    UNNEST(GENERATE_ARRAY(1, 1000000)) AS sample_id
)

補足

ポアソン乱数発生関数は他の2つの関数と異なり、一時的にポアソン乱数テーブルを作成し、そのテーブルから指定のサンプル数だけポアソン乱数を取得するというフローになっています。確率分布のパラメータも関数内ではなく、クエリの冒頭で定義しています。また乱数テーブルを作成するための計算過程で、ポアソン分布のパラメータの整数倍でレコード数が増えるため、ラムダが30を超えるとメモリ不足エラーで計算不能となりましたので、ご留意ください。

動画でも述べておりますが、ポアソン乱数発生関数は、JavaScriptを用いてUDFを定義した方がシンプルで便利だと思いますが、今回はSQLで統一することを優先しましたのでご容赦頂けましたら幸いです。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。