bigquery shuffle, random sample

date: 2020-06-26 excerpt: bigquery shuffle, random sampleの方法

tag: bq bigquery gcp SQL

bigquery shuffle, random sampleの方法

データをシャッフルする

SELECT
  *
FROM 
  `project_id.dataset_name.table_name`
ORDER BY 
  RAND()

カテゴリごとに指定行数をランダムサンプルする

window関数を使用して、ランダムなindexを割り振る
indexの大きさでしきい値を決定する

SELECT
  *
FROM 
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY <category-name> ORDER BY RAND()) AS th_index
    FROM
      `project_id.bucket_name.table_name` 
  )
WHERE
  th_index <= 10000

再現性を担保しつつサンプルする

ハッシュ関数(FARM_FINGERPRINT)を利用して数値を作成し、MODを取る

WITH t AS (
  SELECT
    word,
    ROW_NUMBER() OVER() AS rn
  FROM
    `publicdata.samples.shakespeare`
)
SELECT
  word
FROM
  t
WHERE
  MOD(ABS(FARM_FINGERPRINT(CAST(rn AS STRING))), 1600) = 0

bigqueryのプレビュー機能を用いたサンプリング

テーブルのサンプリング

参考

TOP N PER GROUP IN BIGQUERY

bq bigquery gcp SQL