データサイエンティストの阿部です。最近来てくれたインターン生と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 でした!次回またお会いしましょう。