SQL分组查询实现RFM客户价值模型的特征提取方法

RFM拆开来就是R（最近购买时间）、F（购买频次）、M（消费金额）。这三组数据不会凭空长在数据库里，得靠SQL从订单表一点点“挖”出来。核心思路很简单——用GROUP BY按客户ID做聚合运算，再配合几个函数把特征值提取出来。

但说起来轻巧，实际操作中容易翻车的地方还真不少。比如你直接对原始订单表做SELECT却忘了加GROUP BY，或者把MAX(order_date)和COUNT(*)丢在没有分组的查询里，数据库会毫不客气地甩给你一句：ERROR 1140: In aggregated query without GROUP BY。这就好比你要数一个班级的平均成绩，但忘了按班级分组，系统根本不知道你在统计谁的数据。

具体到三个维度的计算：

• R：用MAX(order_date)找出每位客户最后一次下单时间。注意字段类型要是DATE或DATETIME，否则MAX()可能按字符串排序，结果会跑偏
• F：用COUNT(*)统计订单数目。如果一张订单含多件商品，别错用COUNT(product_id)——那会把同一订单的多个商品当成多次购买
• M：用SUM(order_amount)汇总实付金额。确认这个字段不包含退款或优惠券抵扣等干扰项，否则需要先用WHERE过滤

从日期到天数，这一步很多人栽跟头

MAX(order_date)返回的还是日期，而RFM打分需要数值型的“距今多少天”。这里就涉及到日期差函数，不同数据库的写法差别很大，很容易踩坑。

MySQL里用DATEDIFF(CURDATE(), MAX(order_date))；PostgreSQL的话，CURRENT_DATE - MAX(order_date)会自动返回整数天数；SQLite则是julianday('now') - julianday(MAX(order_date))。千万别图省事用NOW() - order_date这种写法——SQL压根不认，直接报错。

有几个细节必须留意：

• 保证order_date字段非空，不然MAX()返回NULL，整个差值也跟着变NULL
• 如果业务里存在未来订单（比如预售），DATEDIFF可能算出来负数。这时需要加一个NULLIF(GREATEST(0, DATEDIFF(...)), 0)来兜底
• 这个计算字段只能在SELECT或HA VING里引用，千万别放到WHERE里——否则SQL会一脸茫然地报错Unknown column in WHERE clause

分箱打分、拼接组合，这才是技术活

SQL没有内置的RFM分箱功能，全靠手动写条件表达式。常见做法是对R、F、M分别用CASE WHEN打分（1到5分），然后把三个数字拼起来，比如543。

关键点在于：分档阈值不能写死，必须基于当前数据分布动态算。R越小越好，所以“近30天”的打5分、“31–60天”的打4分；而F和M是越大越好，得先用子查询或CTE算出分位数。

实际操作中有几个要点：

• 推荐用窗口函数预计算分位点：PostgreSQL/Oracle可以用PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY recency_days)；MySQL 8.0+则用PERCENT_RANK()代替
• 别在一个CASE里混搭R、F、M的排序方向（R升序打分、F/M降序打分），很容易把逻辑搞反。最好分开写三段CASE，最后用CONCAT()拼接
• 拼接时用CONCAT(r_score, f_score, m_score)，别用r_score * 100 + f_score * 10 + m_score——万一某个维度恰好是0分（虽然罕见但可能），位数就会乱掉

HA VING vs WHERE：千万别搞混顺序

建模时经常需要过滤“无效客户”，比如只保留至少下过2单、总金额超过100元的客户。这类条件依赖聚合结果（COUNT(*) >= 2），必须用HA VING，而不是WHERE。

你是不是也很自然地想过直接写WHERE COUNT(*) >= 2？别急，这样写立马报错Invalid use of group function。原因很直观：WHERE在分组前执行，根本看不到聚合后的值。

• HA VING是唯一可以引用COUNT/SUM/MAX等聚合结果的地方
• 如果还要结合原始字段过滤（比如WHERE order_status = 'paid'），务必写在HA VING前面——先筛有效订单，再聚合，最后用HA VING控制客户准入门槛
• 性能上，HA VING无法利用索引，数据量大时建议先用WHERE尽可能缩小数据集，再做分组

话说回来，真正头疼的不是SQL写法本身，而是R、F、M三者分布往往极度偏态——比如90%的客户M=0。这种情况下，分档边界稍有偏差，大量客户就会挤在同一个RFM组合里。所以，SQL跑完之后，千万别偷懒，一定要拿几个典型样本用业务规则手工检验，不能全信自动分箱结果。我过去踩过的坑，大多都是因为没扛住这轮核查才发现的。