fbpx
HOW TO TD(User Engagement)Treasure Data User Engagement

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

ホーム » SQLによるサンプリング抽出の方法

データマネジメントチームの井口 奈津美です。

本記事では、SQLによるサンプリング抽出の方法をご紹介いたします。MAツールなどを使って施策を行うときに「母集団から〇〇人だけを無作為に抽出したい」ということがあるかと思います。そうしたときに今回ご紹介する関数を使って実現することができます。

概要をご説明しますと、母集団の各レコードにランダムな行番号を割り当て、抽出したい数だけを抽出するというものです。

実際のクエリを見てみましょう。
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関数を組み合わせて抽出してみてください。

井口 奈津美

Customer Data Managementチーム

独立系SIerに入社し、SEとして業務システムの運用保守、既存システムの再構築を経験。 その後、農業のデータ分析を行うスタートアップへ転職。自社のデータ分析基盤を構築するためのシステム要件定義を担当。農林水産省のスマート農業実証事業が立ち上がるとプロジェクト管理業務も行った。 2021年にトレジャーデータに参画。データマネジメントチームにて、TREASURE DATA CDPの構築や施策のデータ集計等、データ基盤の設計・開発・運用支援に携わる。

得意領域 : データ活用・分析支援

Back to top button