MySQL 5.x版本无ntile函数，求RFM评分替代实现方案

嘿，我明白你的困扰——MySQL 5.x确实没有窗口函数支持，NTILE用不了，但完全可以用用户变量+子查询来实现相同的分组效果。下面我会一步步拆解，给你一个和原NTILE(4)逻辑一致的替代方案：

核心思路 #

NTILE(4)的本质是把有序的数据集分成4个大小尽可能均等的组。我们可以通过以下步骤模拟：

1. 先计算总客户数，确定分组的基准
2. 给每个指标（近期度、频次、金额）的排序结果分配行号
3. 用行号和总客户数计算每个客户所属的分组（1-4分）

完整实现代码 #

首先，我们先初始化变量，然后分步计算每个RFM指标的分数，最后关联得到结果：

-- 1. 初始化总客户数变量（统计有交易的唯一客户数）
SET @total_customers = (SELECT COUNT(DISTINCT customer_id) FROM transaction);

-- 2. 初始化三个指标的行号计数器变量
SET @row_recency = 0;
SET @row_frequency = 0;
SET @row_monetary = 0;

-- 3. 最终查询：整合基础指标和RFM分数
SELECT 
    base.customer_id,
    base.last_order_date,
    base.count_order,
    base.sum_amount,
    recency.rfm_recency,
    frequency.rfm_frequency,
    monetary.rfm_monetary
FROM (
    -- 基础聚合：获取每个客户的核心交易指标（你原代码中移除NTILE后的部分）
    SELECT 
        customer_id,
        MAX(local_date) AS last_order_date,
        COUNT(*) AS count_order,
        SUM(amount) AS sum_amount
    FROM transaction
    GROUP BY customer_id
) AS base
-- 关联近期度（Recency）分数
JOIN (
    SELECT 
        customer_id,
        -- 用公式模拟NTILE(4)：将行号映射到1-4的分组
        FLOOR((@row_recency := @row_recency + 1 - 1) * 4 / @total_customers) + 1 AS rfm_recency
    FROM (
        -- 按最后下单日期升序排序（最早的客户排前面，分数为1；最近的为4）
        SELECT customer_id, MAX(local_date) AS last_order_date
        FROM transaction
        GROUP BY customer_id
        ORDER BY last_order_date
    ) AS recency_data
) AS recency ON base.customer_id = recency.customer_id
-- 关联频次（Frequency）分数
JOIN (
    SELECT 
        customer_id,
        FLOOR((@row_frequency := @row_frequency + 1 - 1) * 4 / @total_customers) + 1 AS rfm_frequency
    FROM (
        -- 按订单数升序排序（订单最少的客户排前面，分数为1；最多的为4）
        SELECT customer_id, COUNT(*) AS count_order
        FROM transaction
        GROUP BY customer_id
        ORDER BY count_order
    ) AS frequency_data
) AS frequency ON base.customer_id = frequency.customer_id
-- 关联金额（Monetary）分数
JOIN (
    SELECT 
        customer_id,
        FLOOR((@row_monetary := @row_monetary + 1 - 1) * 4 / @total_customers) + 1 AS rfm_monetary
    FROM (
        -- 按消费金额升序排序（金额最少的客户排前面，分数为1；最多的为4）
        SELECT customer_id, SUM(amount) AS sum_amount
        FROM transaction
        GROUP BY customer_id
        ORDER BY sum_amount
    ) AS monetary_data
) AS monetary ON base.customer_id = monetary.customer_id;

关键细节说明 #

• 分组公式：FLOOR((row_num - 1)*4/@total_customers) + 1 完全模拟NTILE(4)的逻辑——当客户总数不能被4整除时，前面的分组会比后面的多1个客户，保证各组大小尽可能均等。
• 分数调整：如果你希望近期度越高（下单越近）分数越高，当前的排序（ORDER BY last_order_date）是对的；如果想反过来（比如最近的得1分，最早的得4分），只需要把排序改成ORDER BY last_order_date DESC即可。频次和金额的分数逻辑同理。
• 变量初始化：必须先初始化变量，否则行号会从NULL开始计算，导致分组错误。

简化版（一次性执行） #

如果你不想分开设置变量，也可以把变量初始化整合到子查询中，写成单条SQL：

SELECT 
    base.customer_id,
    base.last_order_date,
    base.count_order,
    base.sum_amount,
    recency.rfm_recency,
    frequency.rfm_frequency,
    monetary.rfm_monetary
FROM (
    SELECT 
        customer_id,
        MAX(local_date) AS last_order_date,
        COUNT(*) AS count_order,
        SUM(amount) AS sum_amount
    FROM transaction
    GROUP BY customer_id
) AS base
JOIN (
    SELECT 
        customer_id,
        FLOOR((row_num - 1)*4/total) + 1 AS rfm_recency
    FROM (
        SELECT 
            customer_id,
            @row_r := @row_r + 1 AS row_num
        FROM (
            SELECT customer_id, MAX(local_date) AS last_order_date
            FROM transaction
            GROUP BY customer_id
            ORDER BY last_order_date
        ) AS rd,
        (SELECT @row_r := 0) AS init_r
    ) AS rr,
    (SELECT COUNT(DISTINCT customer_id) AS total FROM transaction) AS tc
) AS recency ON base.customer_id = recency.customer_id
JOIN (
    SELECT 
        customer_id,
        FLOOR((row_num - 1)*4/total) + 1 AS rfm_frequency
    FROM (
        SELECT 
            customer_id,
            @row_f := @row_f + 1 AS row_num
        FROM (
            SELECT customer_id, COUNT(*) AS count_order
            FROM transaction
            GROUP BY customer_id
            ORDER BY count_order
        ) AS fd,
        (SELECT @row_f := 0) AS init_f
    ) AS fr,
    (SELECT COUNT(DISTINCT customer_id) AS total FROM transaction) AS tc
) AS frequency ON base.customer_id = frequency.customer_id
JOIN (
    SELECT 
        customer_id,
        FLOOR((row_num - 1)*4/total) + 1 AS rfm_monetary
    FROM (
        SELECT 
            customer_id,
            @row_m := @row_m + 1 AS row_num
        FROM (
            SELECT customer_id, SUM(amount) AS sum_amount
            FROM transaction
            GROUP BY customer_id
            ORDER BY sum_amount
        ) AS md,
        (SELECT @row_m := 0) AS init_m
    ) AS mr,
    (SELECT COUNT(DISTINCT customer_id) AS total FROM transaction) AS tc
) AS monetary ON base.customer_id = monetary.customer_id;

这个版本不需要提前设置变量，直接执行即可，适合一次性运行的场景。

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