例えば一人のユーザーが発行するクーポンが複数ある場合、それぞれが何回使ったのかをまとめて集計したいことがありますよね。全てのクーポンが5回以上使われているのかや、全てではないが1つはクーポンが5回以上使っているのかなどを集計するなど、そういう条件ベースの分析を行うときは、MAXやMIN演算子を使うと分析しやすくなります。
SQLで、条件に基づく分類や条件ベースのフラグ付けをしたい
例えばデータセット内の各エンティティに対して、特定の条件を満たすかどうかを評価し、その結果に基づいてカテゴリやフラグを設定する手法があります。
例えばビジネスの文脈では、顧客を「高価値顧客」「中価値顧客」「低価値顧客」に分類する際に使われるなどがありますね。企業が各顧客ごとに売上を分析しようとしたときに、販売している商品のカテゴリーが3つあるとしましょう。ある顧客が、全てのカテゴリーの売上が平均より高いのか、全ての売上金額だけ高いのかは意味合いが変わってきます。
このようにデータを分類する手法は下記のようなやりかたが存在します。
- データのセグメンテーション：データを特定の基準に基づいてグループに分ける。
- 条件ベースの分類：特定の条件を満たすかどうかでデータを分類する。
- フラグ付け：特定の条件を満たすデータにフラグを付ける。
この手法の利点は、複雑なデータセットを簡単に理解しやすいカテゴリに分けることができる点です。これにより、データの分析や意思決定が容易になりますね。
MAXやMINを使って複数条件で、条件を満たしているか・満たしていないか？を判定する
例えば冒頭に書いたような1ユーザーが発行できるクーポンが複数ある場合をイメージしてみましょう。
クーポン発行システムのアップデートを考えるにあたって、全てのクーポンが沢山使われているのかと、一部のクーポンだけ使われているのかを見るなど条件をベースに分類することで、ユーザー自体のクーポンが使われやすいのか、あるクーポンが使われやすかったのかなどを比較できますね。
今回はユーザーのクーポンごとの利用数を出して、条件を満たしているのかを確認するクエリを紹介します。
with user_copon as( select user_id, coupon_id, count(distinct customer_id) as customer_usedcnt from d group by 1,2 ), flagged_bots as ( select user_id, case when min(customer_usedcnt) >= 10 then 'A' when max(customer_usedcnt) >= 10 then 'B' else 'C' end as flag from user_coupon group by user_id ) select * from flagged_bots;
クエリの説明
user_coupon user_id と coupon_id ごとに、異なる customer_id の数をカウントしています。 COUNT(DISTINCT customer_id) によって、各クーポンが何人の顧客に使用されたかを計算します。
flagged_bots 各 user_id ごとに、以下の条件に基づいてフラグを設定します。 MIN(customer_usedcnt) >= 10 の場合は 'A' フラグ MAX(customer_usedcnt) >= 10 の場合は 'B' フラグ それ以外の場合は 'C' フラグとしています。
全ての条件を満たしている場合は、各クーポンの利用回数の最低値が上回っていれば良いわけです。逆に何かしら超えている場合、各クーポンの利用回数の最大値が超えていれば満たしていると判定できます。
MAX演算子とMIN演算子の説明
まずMAX 演算子は、指定された列の中で最大の値を返します。例えば、ある列の中で最も大きな数値や最新の日付を取得する場合に使用できます。
逆にMIN演算子はその反対ですね。MIN 演算子は、指定された列の中で最小の値を返します。例えば、ある列の中で最も小さな数値や最古の日付を取得する場合に使用されます。
いかがでしたでしょうか？複数の条件を満たしているかの判定にぜひMAXやMIN演算子を使ってみてください。
