BigQueryで正規分布の乱数を作成する

現状(2021/06/27)BigQuery内に関数がないのと、ググった感じ誰も書いてなかったようなので。
PythonやRなら関数で一発ですが、それをBQに持ってくるの面倒だし、BigQuery上で実験したいって時に。

概要

ボックス・ミュラー法を用いて一様分布(RAND関数)から正規分布の乱数を作成する

クエリ

WITH
generate_random AS (
    SELECT
        index,
        RAND() AS x,
        RAND() AS y,
    FROM (SELECT GENERATE_ARRAY(1, 1000, 1) AS idx)
    ,UNNEST(idx) AS index
)
,
generate_norm AS (
    SELECT
        index,
        SQRT(-2*LOG(x))*COS(2*ACOS(-1)*y) AS norm
    FROM generate_random
)
SELECT 
    *
FROM generate_norm

-- 平均/分散を確認
-- SELECT 
--     AVG(norm),
--     VAR_POP(norm),
--     VAR_SAMP(norm)
-- FROM generate_norm