如何合并RFM用户分群SQL的分值计算与分段逻辑代码

合并逻辑非常简单，你只需要将分段用的CASE语句作为新增字段，直接添加到原有RFM分值计算逻辑的最终SELECT查询中即可，无需修改原有已验证的计算逻辑。

合并后完整可运行代码 #

WITH rfm_raw AS (
SELECT  
    user_id,
    DATE_DIFF(CURRENT_DATE(),MAX(created_at), DAY) AS R,
    COUNT(*) AS F,
    AVG(SUM) AS M
FROM `TABLE` -- 替换为你的实际表名
GROUP BY user_id
),
calc_r AS (
SELECT 
  user_id,
  R,
  NTILE(4) OVER (ORDER BY R ASC) R_S
FROM rfm_raw),
calc_f AS (
SELECT user_id,
F,
NTILE(4) OVER (ORDER BY F DESC) F_S
FROM rfm_raw),
calc_m AS (
SELECT user_id,
M,
NTILE(4) OVER (ORDER BY M DESC) M_S
FROM rfm_raw)

SELECT
calc_r.user_id,
R,
R_S,
F,
F_S,
M,
M_S,
-- 以下为新增的分段逻辑
CASE 
WHEN (R_S = 4) AND (F_S  =4 ) AND ( M_S  = 4) 
THEN 'Champions'
WHEN (R_S BETWEEN 3 AND 4) AND (F_S BETWEEN 3 AND 4 ) AND ( M_S BETWEEN 2 AND 4) 
THEN 'Loyal Customers'
WHEN (R_S BETWEEN 3 AND 4) AND (F_S BETWEEN 1 AND 4) AND ( M_S BETWEEN 1 AND 4) 
THEN 'Potential Loyalists'
WHEN (R_S = 4) AND (F_S =1 ) AND ( M_S =1) 
THEN 'New Customers'
WHEN (R_S = 3) AND (F_S BETWEEN 1 AND 3) AND ( M_S BETWEEN 1 AND 3) 
THEN 'Promising'
WHEN (R_S BETWEEN 2 AND 3) AND (F_S BETWEEN 1 AND 4 ) AND ( M_S BETWEEN 1 AND 4) 
THEN 'Need Attention'
WHEN (R_S=2) AND (F_S =1 ) AND ( M_S =2) 
THEN 'About to sleep'
WHEN (R_S <=2) AND (F_S BETWEEN 1 AND 4 ) AND ( M_S BETWEEN 1 AND 4 ) 
THEN 'At risk'
WHEN (R_S <=2) AND (F_S BETWEEN 3 AND 4) AND ( M_S BETWEEN 2 AND 4) 
THEN 'Cannot lose them '
WHEN (R_S =1) AND (F_S =4 ) AND ( M_S  =1) 
THEN 'Hibernating'
WHEN (R_S =1) AND (F_S =1 ) AND ( M_S =1) 
THEN 'Lost'
END AS rfm_segment
FROM calc_r
LEFT JOIN calc_f
ON calc_r.user_id = calc_f.user_id
LEFT JOIN calc_m
ON calc_r.user_id = calc_m.user_id

可选优化建议 #

原代码拆分三个CTE再JOIN的写法可以简化，直接在一个CTE里完成三个分值的计算，减少多余的关联操作，性能更优：

WITH rfm_raw AS (
SELECT  
    user_id,
    DATE_DIFF(CURRENT_DATE(),MAX(created_at), DAY) AS R,
    COUNT(*) AS F,
    AVG(SUM) AS M
FROM `TABLE`
GROUP BY user_id
),
rfm_score AS (
SELECT
  user_id,
  R,
  NTILE(4) OVER (ORDER BY R ASC) R_S,
  F,
  NTILE(4) OVER (ORDER BY F DESC) F_S,
  M,
  NTILE(4) OVER (ORDER BY M DESC) M_S
FROM rfm_raw
)
SELECT 
  *,
  CASE 
WHEN (R_S = 4) AND (F_S  =4 ) AND ( M_S  = 4) 
THEN 'Champions'
WHEN (R_S BETWEEN 3 AND 4) AND (F_S BETWEEN 3 AND 4 ) AND ( M_S BETWEEN 2 AND 4) 
THEN 'Loyal Customers'
WHEN (R_S BETWEEN 3 AND 4) AND (F_S BETWEEN 1 AND 4) AND ( M_S BETWEEN 1 AND 4) 
THEN 'Potential Loyalists'
WHEN (R_S = 4) AND (F_S =1 ) AND ( M_S =1) 
THEN 'New Customers'
WHEN (R_S = 3) AND (F_S BETWEEN 1 AND 3) AND ( M_S BETWEEN 1 AND 3) 
THEN 'Promising'
WHEN (R_S BETWEEN 2 AND 3) AND (F_S BETWEEN 1 AND 4 ) AND ( M_S BETWEEN 1 AND 4) 
THEN 'Need Attention'
WHEN (R_S=2) AND (F_S =1 ) AND ( M_S =2) 
THEN 'About to sleep'
WHEN (R_S <=2) AND (F_S BETWEEN 1 AND 4 ) AND ( M_S BETWEEN 1 AND 4 ) 
THEN 'At risk'
WHEN (R_S <=2) AND (F_S BETWEEN 3 AND 4) AND ( M_S BETWEEN 2 AND 4) 
THEN 'Cannot lose them '
WHEN (R_S =1) AND (F_S =4 ) AND ( M_S  =1) 
THEN 'Hibernating'
WHEN (R_S =1) AND (F_S =1 ) AND ( M_S =1) 
THEN 'Lost'
END AS rfm_segment
FROM rfm_score

内容的提问来源于stack exchange，提问作者Wilcat