BigQueryのStandardSQLでランダムランブリング

概要

BigQueryのStandardSQLだとrand()関数にseedを指定することができないので、再現性を担保した上で任意の件数のランダムサンプリングの実装が面倒。
簡易的な代替手段を備忘のためまとめる。

やり方

1. ユニークIDを元にハッシュ値でソートし、rankをつける
   • ユニークIDに任意の文字列をseedの代わりに結合して扱う
2. rankで任意の件数を取得する

StandardSQLでランダムサンプリング

with
rand_sample as (
    select
        * except(rank)
    from (
        select
            word,
            dense_rank() over (order by farm_fingerprint(concat('seed', word)), word) as rank,
        from
            (select distinct word from `publicdata.samples.shakespeare`)
    )
    where
        rank <= 5
)

select * from rand_sample

参考

• BigQuery でランダムサンプリング