本筆記將利用sql語言構建RFM模型颠焦，將會有兩種辦法對用戶進行分類卒废。
第一種方法是基于有明確業(yè)務指標計算RFM分值。
第二種是按二八定律設定閥值系馆。
首先看看RFM模型是什么？
R值:Rencency(最近一次消費) 指的是用戶在店鋪最近一次購買時間距離分析點的時間間隔;
F值:Frequency(消費頻率) 指的是是用戶在固定時間內的購買次數(shù);
M值:Monetary(消費金額) 指的是一段時間(通常是1年)內的消費金額;
根據(jù)三個值的高低之分顽照，會得出8種類型的客戶由蘑；
一般每個指標都會有1，2代兵，3尼酿，4，5分的分值標準奢人，此指標一般根據(jù)具體業(yè)務需求進行設置谓媒；
如：
然后根據(jù)以上標準對用戶進行打分，并會求得各指標均值何乎，進行比較句惯，大于均值為高，少于均值為低支救。
接下來按照此標準用sql執(zhí)行抢野。
方法一
第一步
首先我們導入相關數(shù)據(jù)，并去重數(shù)據(jù)放進新表 temp_trade各墨；
由于時間關系指孤，以導入如下數(shù)據(jù)，期間利用
SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');
set dates=date(date_time);
這兩個函數(shù)對原表（紅框）日期進行處理贬堵；
create table o_retailers_trade_user
(
user_id int (9),
item_id int (9),
behavior_type int (1), -- 用戶行為類型(1-曝光;2-購買;3-加入購物?;4-加入收藏夾恃轩。)
user_geohash varchar (14),
item_category int (5), -- 品類ID
time varchar (13) -- 用戶發(fā)生行為的時間
);
-- 日期時間數(shù)據(jù)處理 增加新列date_time、dates
ALTER TABLE o_retailers_trade_user
ADD COLUMN date_time datetime null;
UPDATE o_retailers_trade_user
SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');
alter table o_retailers_trade_user add column dates char(10) null;
update o_retailers_trade_user
set dates=date(date_time);
desc o_retailers_trade_user;
再檢查一下關鍵字段有無缺失值
SELECT COUNT(user_id) ,COUNT(item_id) ,COUNT(item_category) ,COUNT(behavior_type) ,COUNT(time)
FROM o_retailers_trade_user;
查詢后得出并無缺失黎做。
再檢查一下用戶行為是否有1叉跛、2、3蒸殿、4以外的異常值筷厘；
SELECT behavior_type FROM o_retailers_trade_user
WHERE behavior_type NOT IN (1,2,3,4);
查詢結果無異常值；
-- 建新表宏所，放進 去重后的 數(shù)據(jù)
create table temp_trade like o_retailers_trade_user;
insert into temp_trade select distinct * from o_retailers_trade_user;
#####**第二步**
**對R值進行計算**
R值定義:Rencency(最近一次消費) 指的是用戶在店鋪最近一次購買時間距離分析點的時間間隔;
1. 查詢每用戶ID最近一次購買時間酥艳，即求時間最大值，排序后見到時間最大值為12月18日爬骤，接下來將假設項目在12月19日進行充石，來求與最近一次購買時間的差值，即下面的相隔天數(shù)霞玄。
SELECT user_id , max(dates) AS 最近一次消費時間
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
ORDER BY 最近一次消費時間 desc
查詢結果如下：
2. 利用case when 函數(shù)得出每個相差天數(shù)對應的分值赫冬；
為方便后面對表調用浓镜，創(chuàng)建了視圖；
CREATE VIEW r_clevel AS
SELECT user_id , 最近一次消費時間 , DATEDIFF('2019-12-19',最近一次消費時間) AS 相差天數(shù),
(CASE
WHEN DATEDIFF('2019-12-19',最近一次消費時間)<=2 THEN 5
WHEN DATEDIFF('2019-12-19',最近一次消費時間)<=4 THEN 4
WHEN DATEDIFF('2019-12-19',最近一次消費時間)<=6 THEN 3
WHEN DATEDIFF('2019-12-19',最近一次消費時間)<=8 THEN 2
ELSE
1 END )AS R分值
FROM
(
SELECT user_id , max(dates) AS 最近一次消費時間
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
ORDER BY 最近一次消費時間 desc
)a
視圖如下（部分截圖）：
#####**第三步**
**對F值進行計算**
F值定義：Frequency(消費頻率) 指的是是用戶在固定時間內的購買次數(shù);
1. 首先求出每個用戶的購買頻次劲厌，即需用到count函數(shù)，對用戶行為類型2購物進行計數(shù)听隐；
SELECT user_id , COUNT(user_id) AS 購買頻次
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
ORDER BY 購買頻次 desc
查詢結果如下：
2. 利用case when 函數(shù)得出每個用戶購買次數(shù)對應的分值补鼻；
為方便后面對表調用，也創(chuàng)建了視圖雅任；
CREATE VIEW f_clevel AS
SELECT user_id , 購買頻次 ,
(CASE
WHEN 購買頻次<=2 THEN 1
WHEN 購買頻次<=4 THEN 2
WHEN 購買頻次<=6 THEN 3
WHEN 購買頻次<=8 THEN 4
ELSE 5 END )AS F分值
FROM
(
SELECT user_id , COUNT(user_id) AS 購買頻次
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
)a
視圖如下（部分截圖）：
#####**第四步**
**求R风范、F的均值**
-- 1.R平均值
SELECT avg(R分值) as 'r_avg' FROM r_clevel;
-- 2.F平均值
select avg(F分值) as 'f_avg' from f_clevel;
計算結果：
#####**第五步**
**對用戶進行等級劃分**
由于該數(shù)據(jù)沒有M值，故只建立了4個分類沪么，若有M值可得8個分類硼婿；
繼續(xù)利用case when 函數(shù)計算，最后創(chuàng)建視圖RFM_table
create view RFM_table
as
select a.*,b.分值,
(case
when a.分值>2.5515 and b.分值>2.2606 then '重要高價值客戶' when a.分值<2.5515 and b.分值>2.2606 then '重要喚回客戶'
when a.分值>2.5515 and b.分值<2.2606 then '重要深耕客戶' when a.分值<2.5515 and b.分值<2.2606 then '重要挽留客戶' END
) as user_class
from r_clevel a, f_clevel b
where a.user_id=b.user_id;
查詢結果如下：
#####**第六步**
**統(tǒng)計各用戶等級數(shù)量**
SELECT user_class , COUNT(user_class)AS 數(shù)量
FROM
RFM_table
GROUP BY user_class
查詢結果如下：
到此禽车，整個方法一就完成了寇漫。
#方法二
由于方法一有時給人感覺分值指標有點拍腦袋想出來的，這里可以常用用二八法則來確定一個閥值殉摔，即在前20%的用戶屬于高分值用戶州胳，后面的為低分值。
#####**第一步**
**統(tǒng)計本次表中帶有購買行為的用戶數(shù)量逸月，并找出在前20%位置的找出R值的閥值**
1. 求得20%的用戶數(shù)是165x20%=33人
SELECT COUNT(DISTINCT user_id) AS 購買用戶數(shù)
FROM
temp_trade
WHERE behavior_type='2'
2. 利用limit函數(shù)栓撞，找到排第33個用戶的相差天數(shù)，作為閥值碗硬。
SELECT
相差天數(shù)
FROM
(
SELECT user_id , 最近一次消費時間 , DATEDIFF('2019-12-19',最近一次消費時間) AS 相差天數(shù)
FROM
(
SELECT user_id , max(dates) AS 最近一次消費時間
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
ORDER BY 最近一次消費時間 desc
)a
ORDER BY 相差天數(shù) DESC
)b
LIMIT 32,1
查詢結果：
#####**第二步**
**找出F值的閥值**
SELECT
購買頻次
FROM
(
SELECT user_id , 購買頻次
FROM
(
SELECT user_id , COUNT(user_id) AS 購買頻次
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id
ORDER BY 購買頻次 DESC
)a
)b
LIMIT 32,1
查詢結果：
得到R瓤湘、F兩個維度分類閾值分別為：19、7恩尾；
#####**第三步**
**在原表中求得用戶的相差天數(shù)及購買頻次**
創(chuàng)建視圖RF_TABLE
CREATE VIEW RF_TABLE AS
SELECT user_id , 最近一次消費時間 , DATEDIFF('2019-12-19',最近一次消費時間) AS 相差天數(shù),購買頻次
FROM
(
SELECT user_id , max(dates) AS 最近一次消費時間 , COUNT(user_id) AS 購買頻次
FROM
temp_trade
WHERE behavior_type='2'
GROUP BY user_id)a
查詢結果：
#####**第四步**
**對用戶進行等級劃分**
select user_id,
(case
when 相差天數(shù)<=19 and 購買頻次>=7 then '重要高價值客戶' when 相差天數(shù)>19 and 購買頻次>=7 then '重要喚回客戶'
when 相差天數(shù)<=19 and 購買頻次<7 then '重要深耕客戶' when 相差天數(shù)>19 and 購買頻次<7 then '重要挽留客戶' END
) as user_class
from RF_TABLE ;
查詢結果：
#####**第五步**
**統(tǒng)計各用戶等級數(shù)量**
SELECT user_class , COUNT(user_class)AS 數(shù)量
FROM
(
select user_id,
(case
when 相差天數(shù)<=19 and 購買頻次>=7 then '重要高價值客戶' when 相差天數(shù)>19 and 購買頻次>=7 then '重要喚回客戶'
when 相差天數(shù)<=19 and 購買頻次<7 then '重要深耕客戶' when 相差天數(shù)>19 and 購買頻次<7 then '重要挽留客戶' END
) as user_class
from RF_TABLE
) a
GROUP BY user_class
查詢結果：
再次對比方法一結果：
查詢結果如下：
發(fā)現(xiàn)方法二缺少了最高價值客戶數(shù)據(jù)弛说，原因應在于在此數(shù)據(jù)中，沒有用戶的的R值和F值同時處于前20%而導致的特笋，而數(shù)據(jù)量較少也是其中一個原因剃浇。
說明兩種不同方法，得出的結果會有較大的出入猎物。
