用户分群怎么做？NTILE打分 vs K-Means聚类，面试怎么答？

用户分群是画像系统最直接的应用场景。运营最常说的话就是「帮我把用户分个层，我要做差异化运营」。但分群的方法很多——最简单的CASE WHEN硬编码、经典的RFM+NTILE打分、高级的K-Means聚类——面试官想听的是你怎么选方案，而不只是会哪个技术。

方案一：规则分群——最简单但最常用

适用于业务方已经有明确分群标准的场景。比如运营说「消费>200块的是高价值用户」——那就直接写CASE WHEN。

-- 规则分群：业务方定义的硬编码规则
SELECT user_id,
  total_amount_usd,
  login_cnt_30d,
  CASE
    WHEN total_amount_usd >= 200 AND login_cnt_30d >= 15
      THEN 'core_user'        -- 核心用户：高消费+高活跃
    WHEN total_amount_usd >= 200 AND login_cnt_30d < 15
      THEN 'high_value_sleep' -- 高价值沉睡：花了钱但不活跃了
    WHEN total_amount_usd < 50 AND login_cnt_30d >= 20
      THEN 'active_freeloader'-- 活跃白嫖：天天来但不花钱
    WHEN total_amount_usd < 50 AND login_cnt_30d < 5
      THEN 'churn_risk'       -- 流失风险：不花钱也不活跃
    ELSE 'general'            -- 一般用户
  END AS user_segment
FROM ads_user_profile
WHERE dt = '${bizdate}';

优点：简单、可解释、业务方能理解；规则可以随时调整；SQL即可实现。

缺点：阈值是人工拍的，不一定准确；维度多了规则就写不完（3个维度有27种组合）。

方案二：RFM + NTILE打分——经典的黄金方案

RFM是用户价值分析的经典框架：Recency（最近购买时间）、Frequency（购买频率）、Monetary（消费金额）。用NTILE(5)把每个维度分成5档，然后组合出125种用户类型，再归并成8类核心用户。

-- RFM + NTILE(5)打分
WITH rfm_raw AS (
  SELECT user_id,
    DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,   -- R：越小越好
    COUNT(DISTINCT order_id) AS frequency,                 -- F：越大越好
    SUM(amount_usd) AS monetary                            -- M：越大越好
  FROM dwd_order_detail
  WHERE order_date >= DATE_SUB(CURRENT_DATE, 365)
  GROUP BY user_id
),
rfm_scored AS (
  SELECT user_id, recency, frequency, monetary,
    NTILE(5) OVER (ORDER BY recency ASC) AS r_score,       -- R反向排序！越近越高分
    NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
  FROM rfm_raw
)
SELECT user_id, r_score, f_score, m_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'champion'       -- 冠军用户
    WHEN r_score >= 4 AND f_score >= 4 AND m_score < 4  THEN 'loyal'          -- 忠诚用户
    WHEN r_score >= 4 AND f_score < 3  AND m_score >= 4 THEN 'big_spender'    -- 大额偶买
    WHEN r_score < 3  AND f_score >= 4 AND m_score >= 4 THEN 'at_risk'        -- 高价值流失风险
    WHEN r_score < 2  AND f_score < 2  AND m_score < 2  THEN 'lost'           -- 已流失
    WHEN r_score >= 3 AND f_score < 2  AND m_score < 2  THEN 'new_user'       -- 新用户
    ELSE 'general'                                                             -- 一般用户
  END AS rfm_segment
FROM rfm_scored;