Athena｜再現性をもってデータをグループごとにサンプリングをするSQL

データ分析をするとき、集計された値だけ見るのではなく、いくつかデータをピックアップして傾向を見てみるということも方法としてあります。

このとき、ピックアップするデータが特定の傾向に偏らないよう、ランダムに抽出をする必要があります。また、何度抽出をしても同じ結果となるよう再現性が必要となったり、グループごとにデータをピックアップしたいケースもあるかと思います。

AWSのAmazon AthenaでランダムにデータサンプリングするSQLとしては下のものがありますが、こちらは再現性がなくSQLを実行するたびに抽出されるデータ、件数が変わってしまいます。また、グループごとに同じ件数となるようにデータを取得することはできません。

/* データの30%をランダムに取得するSQL */
SELECT
    *
FROM
    database1.table1 tablesample bernoulli(30);

今回はAthenaでデータをランダムにサンプリングでき、再現性もあり、グループごとに同じ件数取得できるSQLについて説明します。

再現性をもってデータをグループごとにサンプリングをするSQL
- SQLの詳細
おわりに
参考

再現性をもってデータをグループごとにサンプリングをするSQL

まず結論としてSQLは下のようになります。

WITH i AS (
    SELECT
        *
        , row_number() OVER (
            PARTITION BY
                /* グループ化する列を指定 */
                col02
            ORDER BY
                /* ユニークとなる列(文字列)からhashを作成。seedはシード値 */
                from_big_endian_64(
                    xxhash64(CAST(col01 || 'seed' AS varbinary))
                )
        ) AS row_num
    FROM
        "database01"."table01"
)
SELECT
    *
FROM
    i
WHERE
    /* 各グループごとに抽出する件数を指定。
       下の場合は各グループごとに3件データをピックアップ */
    row_num <= 3;

使用の際は下の個所を自身の環境、目的に当てはめて変更してください。

database01
- データ分析をするデータベース。
table01
- データ分析をするテーブル。
col01
- この値をもとにhashを求めますので、主キー等のユニークな値をもつ列を指定してください。
'seed'
- 任意の値。ランダムな値を生成する際のシード値となります。この値により抽出されるデータが変わります。
col02
- グループ分けしたい列。さらに詳細にグループ分けする場合は指定する列を増やしてください。
row_num <= 3
- 各グループごとに抽出する件数。左の場合はグループごとに3件抽出されます。

SQLの詳細

部分ごとにSQLの内容を説明します。

, row_number() OVER (
    PARTITION BY
        /* グループ化する列を指定 */
        col02
    ORDER BY
        /* ユニークとなる列(文字列)からhashを作成。seedはシード値 */
        from_big_endian_64(
            xxhash64(CAST(col01 || 'seed' AS varbinary))
        )
) AS row_num

上の部分がこのSQLのポイントです。ユニークとなる列（上ではcol01）とシード値（上では'seed'）からhashを作り出し、Window関数の一つであるrow_numberを使って、グループ化する列（上ではcol02）ごとに作成したhash値の順に連番を振っています。

hashを作り出している個々の関数については下の通りです。

from_big_endian_64
- 64bitのビッグエンディアンのバイナリデータを数値に変換します。
xxhash64
- 引数のバイナリデータからのxxhashアルゴリズムを使って64bitのhashを返します。
CAST(string AS varbinary)
- 文字列をバイナリデータに変換します。

また、col01は文字列を想定していますが、数値型の場合はhash値を作成する部分を下の構文で置き換えてください。999はシード値ですので任意の値を指定してください。

from_big_endian_64(xxhash64(to_big_endian_64(col01 + 999)))

そして最後のwhere句にて、各グループごとに1から採番されているrow_numに条件を付けることによって各グループから抽出される件数を制御しています。

WHERE
    /* 各グループごとに抽出する件数を指定。
       下の場合は各グループごとに3件データをピックアップ */
    row_num <= 3;

おわりに

Athenaで再現性をもってデータをグループごとにサンプリングをするSQLについて説明しました。特にデータが大量すぎて個々のデータの確認がしづらいときに利用できると思います。

データ分析をするときの参考となれば幸いです。

参考

このブログは以下の情報を参考にしました。

Athena｜再現性をもってデータをグループごとにサンプリングをするSQL

再現性をもってデータをグループごとにサンプリングをするSQL

SQLの詳細

おわりに

参考

関連タグの記事

新着記事

タグ

関連記事