SQLサンプルと解説［2］
指標・ランクごとの顧客数の集計
次に、上記で求めた顧客ごとのデータを元に各指標・各ランクでそれぞれ顧客数がどのくらいかを集計します。
欲しい結果は以下の通りです。
rfm_rank | r_sum | f_sum | m_sum
----------+-------+-------+-------
3 | 1 | 1 | 1
2 | 2 | 0 | 1
1 | 1 | 3 | 2
SQLはリスト4の通りになります。
リスト4 RFM分析―ランクごとの顧客集計（rfm-rank-2.sql）
With /*（1）*/
user_rank_table as (
略（リスト3の中身）
),
index_table as ( /*（2）*/
select 1 as rfm_index
union all select 2 as rfm_index
union all select 3 as rfm_index
),
rfm_flag as ( /*（3）*/
select
index_table.rfm_index,
case when index_table.rfm_index = user_rank_table.r_rank then 1 else 0 end as r_flag,
case when index_table.rfm_index = user_rank_table.f_rank then 1 else 0 end as f_flag,
case when index_table.rfm_index = user_rank_table.m_rank then 1 else 0 end as m_flag
from
index_table
cross join
user_rank_table
)
select /*（4）*/
rfm_index as rfm_rank,
sum(r_flag) as r_sum,
sum(f_flag) as f_sum,
sum(m_flag) as m_sum
from
rfm_flag
group by
rfm_index
order by
rfm_index desc
;
（1）With句
ここではリスト3のSQLをサブクエリとして、With句によりuser_rank_tableと名付けています。With句はサブクエリに名前を付け、SQLの前に記述します。これでサブクエリをSQLの中で記述する場合に比べ、視認性を高めることができます。
With句は次のように記述します。
WITH <クエリ名> AS ( select <列> ～ )
（2）インデックステーブル
ここでは、後ほどJOINで利用するための番号のみのテーブルを準備しています。
番号「1」のみを表すテーブルを生成し、これを2、3と順にUNION句によりテーブル連結します。
このクエリによって得られる途中結果は、以下の通りです。
rfm_index
-----------
1
2
3
（3）ランク値ごとにフラグを立てる
リスト4で求めた顧客ごとのランクとインデックス（1～3）のクロスジョインで、各ランクの顧客の有無によりフラグを立てていきます。
このクエリによって得られる途中結果は、以下の通りです。
rfm_index | r_flag | f_flag | m_flag
-----------+--------+--------+--------
1 | 1 | 1 | 1
1 | 0 | 0 | 0
1 | 0 | 1 | 0
1 | 0 | 1 | 1
2 | 0 | 0 | 0
2 | 1 | 0 | 0
2 | 0 | 0 | 1
2 | 1 | 0 | 0
3 | 0 | 0 | 0
3 | 0 | 1 | 1
3 | 1 | 0 | 0
3 | 0 | 0 | 0
（4）フラグの集計
（3）で求めたフラグの数を集計することにより、各指標・各ランクの顧客数が求められます。
集計が終われば分析ですが、指標の軸が3つ以上の分析では通常、散布図がよく使われます。図2は少しデータ数を増やしたサンプルをRFM散布図で表現した例です。R・Fを縦横の軸に、Mを色の濃さで表しています。また、円の大きさと数値が顧客数を表しています。
この例ではFランクの高い顧客が購買金額も大きくなる傾向にあるので、やや顧客数の多いRランク1（新規顧客）やRランク3（常連顧客）に対して購買頻度を高めるアプローチが効果的と考えられます。
