ストック数的に誰も見ていないと思っていたけれど、意外と参考にしたという声を現実世界で最近ちょくちょく聞いているので Medium の方にちゃんと書き直しました。
やりたいこと
タイトルの通り BigQuery でランダムサンプリングをする方法を調べたり考えたりしたのでメモを残しておきます。
方法その 1
乱数生成結果を入れただけの列を追加して、その列についてソートするという作業をサブクエリで記述します。
SELECT
word
FROM (
SELECT word, RAND() AS rand
FROM [publicdata:samples.shakespeare]
)
ORDER BY
rand
LIMIT
100
RAND() 関数が真の乱数を生成するという仮定の下でランダムサンプリングが実現できており、最も素直で確実な方法だと思います。
欠点
ただ、この方法には 1 つ大きな欠点があります。
BigQuery では乱数の seed を固定できないので、再現性がないのです。
どう頑張っても実行するたびに結果が変わってしまいます。
コメントで seed を固定が可能との指摘を頂きました。
ただ、 LIMIT を使った場合、大抵は同じ結果が返ってくるけれど、保証は無いようです。
再現性のない実験や分析なんて、トトロやラピュタの目撃情報と大差ありません。
方法その 2
最近見かけて頭良いなと思った方法を改良したものです。
SELECT
word
FROM (
SELECT word, ROW_NUMBER() OVER() AS rand
FROM [publicdata:samples.shakespeare]
)
WHERE
ABS(HASH(STRING(rand))) % 1600 = 0
以下のような処理を行っています:
-
RAND()ROW_NUMBER()を使って重複が無い列を追加して、その列をHASH()に突っ込む-
HASH()は STRING か INTEGER しか受け取れないので STRING に変換している
-
- 負の値を除くために
HASH()の結果をABS()に突っ込む - それを適当な整数で割った余りを指定して行を抽出する
HASH() が良い感じにランダムな整数値を返すという仮定の下に成り立っている方法なので、乱数としての質はイマイチだと思います。
ですが、この方法の素晴らしいところは何度やっても同じ結果が返ってくるという再現性です。
LIMIT を使って抽出する個数を制御すると BigQuery の機嫌次第で毎回結果が変わるので、何で割り切れるものを抽出するか (上記の例では 1600 になっているところ) を変えて、おおよその個数を制御しています。
今回は元のデータが 164656 行あって、大体 100 行ほどランダムサンプリングしたかったので 1600 で割り切れるものを抽出しました。
欠点
もう書きましたが、個数の制御がアバウトにしかできません。
LIMIT を使って個数を指定すると再現性が失われてしまいます。
まとめ
紹介した 2 種類の方法で大抵の場合は何とかなりそうですが、それぞれ一長一短なので状況によって使い分ければ良いかなと思っています。
もちろん再現性と個数の正確な指定を両立できる方法があればベストですが、残念ながらそれは思いつきませんでした。
どなたか知っている人がいたらコメントで教えてください。