SQLによるサンプリング抽出の方法

実際のクエリを見てみましょう。ROW_NUMBER関数のOVER句でRAND関数を組み合わせることで、各レコードにランダムな行番号が割り当てられます。LIMIT関数を使って上位10件のみを抽出してみます。

SELECT
  ROW_NUMBER()OVER(ORDER BY RAND()) AS rand_num
  , member_id
FROM sales_slip
WHERE member_id IS NOT NULL
LIMIT 10

実行結果は以下の通りです。各レコードにランダムに割り当てられた”rand_num”の上位10件が取得されたことが分かります。（ただし毎回の結果には再現性がないことに注意してください。）

次に、応用編として母集団のうち特定グループに所属する集団から10人を抽出することを考えてみましょう。特定グループを区別するためのフラグやカテゴリなどのカラムが必要ですが、先ほどのクエリにPARTITION BYという関数を一つ加えるだけで実現できます。例では、”category”カラムを使い、特定の”Toys and Kids and Baby”というカテゴリに属するメンバーのうちランダムに10人を抽出してみました。PARTITION BY関数の後ろに特定グループを区別するためのカラム名を添えます。

WITH rand AS (
  SELECT
    ROW_NUMBER()OVER(PARTITION BY category ORDER BY RAND()) AS rand_num
    , category
    , member_id
  FROM sales_slip
  WHERE member_id IS NOT NULL
)

SELECT
  rand_num
  , category
  , member_id
FROM rand
WHERE category = 'Toys and Kids and Baby'
AND rand_num <= 10

実行結果は以下の通りです。特定の”category”に属する集団からランダムに10人を抽出することができました。

ほかにも、メルマガの配信対象者にフラグを立て、フラグが立っている人のうち〇〇人を抽出するといった活用方法も考えられます。

いかがでしたでしょうか。みなさんもサンプリング抽出する際は、今回ご紹介したRAND関数とPARTITION BY関数を組み合わせて抽出してみてください。