SQLサンプルと解説［1］
RFM分析のSQLの例を示し解説をしていきます。
顧客ごとのRFMの集計とランク付け
まず、顧客ごとにRFMそれぞれの指標値を集計し、ランク１〜3を割り振ります。
欲しい結果は以下です。
user_id | recency | frequency | monetary | r_rank | f_rank | m_rank ----------+---------+-----------+----------+--------+--------+-------- UID0002 | 11 | 1 | 980 | 1 | 1 | 1 UID0001 | 1 | 20 | 5500 | 2 | 3 | 3 UID0003 | 0 | 3 | 2010 | 3 | 1 | 2 UID0004 | 4 | 1 | 250 | 2 | 1 | 1
顧客ごとの集計のSQLはリスト3の通りになります。
リスト3 RFM分析SQL―顧客ごとの集計（rfm-rank-1.sql）
select
user_id,
recency,
frequency,
monetary,
case /*（2）*/
when recency < 1 then 3
when recency < 7 then 2
else 1
end as r_rank,
case
when frequency >= 10 then 3
when frequency >= 5 then 2
when frequency >= 1 then 1
end as f_rank,
case
when monetary >= 5000 then 3
when monetary >= 1000 then 2
else 1
end as m_rank
from ( /*（1）*/
select
user_id,
current_date - max(cast(date as date)) as recency, /*（1）-(b)*/
count(date) as frequency, /*（1）-(c)*/
sum(price) as monetary /*（1）-(d)*/
from
sales_history
group by
user_id /*（1）-(a)*/
) as user_rfm
;
内側のサブクエリの内容から順に確認していきます。
（1）顧客ごとのRFM値の算出
- （a）「顧客ごと」で集計するため、group by句でuser_idを指定しグルーピングします。
- （b）直近購買日recencyとして、顧客の最も新しい購買日と現在の日付の差分を算出します。cast(date as date)は文字列型のdate（左辺）をdate型に型変換（キャスト）します。今回のサンプルでは現在の日付を「2017/06/14」として算出しています。
- （c）頻度frequencyを、count関数で顧客の購買の回数を集計することで算出します。
- （d）合計金額monetaryを、sum関数で顧客の購買金額の和を集計することで算出します。
このサブクエリによって得られる途中結果は、以下の通りです。
user_id | recency | frequency | monetary ----------+---------+-----------+---------- UID0002 | 11 | 1 | 980 UID0001 | 1 | 20 | 5500 UID0003 | 0 | 3 | 2010 UID0004 | 4 | 1 | 250
（2）ランクの算出
算出したrecency、frequency、monetaryの値を元に、CASE式を使ってRFMの指標ごとにランク付けしていきます。
CASE式の構文は以下の通りです。構文は2種類あり、WHENの後に値のみ記述する構文（単純CASE式）と、「<項目> = <値1> 」のように比較演算が利用可能な構文（検索CASE式）があります。上のリスト3では検索CASE式の構文を利用しています。
単純CASE式の構文
CASE <項目> WHEN <値1> THEN <値4> WHEN <値2> THEN <値5> ELSE <値3> END
検索CASE式の構文（比較演算子は"="のほか不等号などが使用可能）
CASE WHEN <項目> = <値1> THEN <値4>
WHEN <項目> = <値2> THEN <値5>
ELSE <値3> END
「when recency < 1 then 3」でrecencyが1未満ではランクを3と算出しています。ここでは「1」がランクのしきい値です。特定のランクに偏る場合はこのしきい値を調整しましょう。
以上で顧客ごとのRFM指標ごとの集計とランク付けができました。
