在SQL中实现qcut进行RFM分析：解决ORA-00978分组函数嵌套问题

问题梳理 #

你已经用Python完成了RFM分析的模型实现，现在因为生产环境以PHP为主，需要在Oracle 12c或PostgreSQL中用SQL落地分析逻辑——核心需求是实现自定义分位数分箱（比如指定recency的分箱边界生成5个分位数），最初用percentile_disc的写法过于冗长，后来尝试CTE写法又碰到了ORA-00978错误，接下来我们一步步解决这些问题。

先解决ORA-00978错误 #

你写的CTE里MAX_VALUES部分用了sum(max(recency) + 0.0000000001)这种嵌套聚合函数，Oracle不允许在没有GROUP BY的情况下嵌套使用聚合函数（而且这里sum完全是多余的），直接用max()获取最大值即可修正这个错误。

修正后的基础RFM分箱代码（Oracle） #

WITH RFM AS (
    SELECT 
        SRC_USER_ID,
        COUNT(DISTINCT PICKUP_DATE) - 1 AS frequency,
        (MAX(PICKUP_DATE) - MIN(PICKUP_DATE)) AS recency,
        (TO_DATE('2018/05/12', 'yyyy/mm/dd') - MIN(PICKUP_DATE)) AS T,
        SUM(PRICE_TOTAL) AS monetary_value
    FROM TRANSACTIONS
    GROUP BY SRC_USER_ID
),
MAX_VALUES AS (
    SELECT 
        MAX(recency) + 0.0000000001 AS max_recency,
        MAX(frequency) + 0.00000001 AS max_frequency,
        MAX(monetary_value) + 0.000000001 AS max_monetary
    FROM RFM
)
SELECT 
    SRC_USER_ID,
    recency,
    frequency,
    monetary_value,
    -- 等宽分箱：将指标分成5个区间
    WIDTH_BUCKET(recency, 0, max_recency, 5) AS recency_quantile,
    WIDTH_BUCKET(frequency, 0, max_frequency, 5) AS frequency_quantile,
    WIDTH_BUCKET(monetary_value, 0, max_monetary, 5) AS monetary_quantile
FROM RFM, MAX_VALUES;

自定义分位数边界的实现方案 #

如果需要像你提到的，给recency指定固定分箱边界（比如0,0,74,321生成5个分位数），可以用更灵活的方式实现：

方案1：CASE WHEN 自定义分箱（通用Oracle/PostgreSQL） #

这种方式最直观，完全可控分箱逻辑：

WITH RFM AS (
    SELECT 
        SRC_USER_ID,
        COUNT(DISTINCT PICKUP_DATE) - 1 AS frequency,
        (MAX(PICKUP_DATE) - MIN(PICKUP_DATE)) AS recency,
        (TO_DATE('2018/05/12', 'yyyy/mm/dd') - MIN(PICKUP_DATE)) AS T,
        SUM(PRICE_TOTAL) AS monetary_value
    FROM TRANSACTIONS
    GROUP BY SRC_USER_ID
)
SELECT 
    SRC_USER_ID,
    recency,
    frequency,
    monetary_value,
    -- 自定义recency分箱规则
    CASE
        WHEN recency <= 0 THEN 1
        WHEN recency <=74 THEN 2
        WHEN recency <=321 THEN 3
        WHEN recency <= (SELECT PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY recency) FROM RFM) THEN 4
        ELSE 5
    END AS recency_quantile,
    -- 其他指标用等频分箱的话，直接用NTILE
    NTILE(5) OVER (ORDER BY frequency) AS frequency_quantile,
    NTILE(5) OVER (ORDER BY monetary_value) AS monetary_quantile
FROM RFM;

方案2：PostgreSQL专属简化写法 #

PostgreSQL的WIDTH_BUCKET支持传入分箱边界数组，自定义分箱更简洁：

WITH RFM AS (
    SELECT 
        SRC_USER_ID,
        COUNT(DISTINCT PICKUP_DATE) - 1 AS frequency,
        (MAX(PICKUP_DATE) - MIN(PICKUP_DATE)) AS recency,
        (DATE '2018-05-12' - MIN(PICKUP_DATE)) AS T,
        SUM(PRICE_TOTAL) AS monetary_value
    FROM TRANSACTIONS
    GROUP BY SRC_USER_ID
)
SELECT 
    SRC_USER_ID,
    recency,
    frequency,
    monetary_value,
    -- 直接传入自定义边界数组生成5个分位数
    WIDTH_BUCKET(recency, ARRAY[0, 0, 74, 321, (SELECT MAX(recency) FROM RFM)]) AS recency_quantile,
    NTILE(5) OVER (ORDER BY frequency) AS frequency_quantile,
    NTILE(5) OVER (ORDER BY monetary_value) AS monetary_quantile
FROM RFM;

简化最初的percentile_disc写法 #

如果还是想用分位数函数生成动态边界，可以把分位数计算放到CTE里，避免重复代码：

WITH RFM AS (
    SELECT 
        SRC_USER_ID,
        COUNT(DISTINCT PICKUP_DATE) - 1 AS frequency,
        (MAX(PICKUP_DATE) - MIN(PICKUP_DATE)) AS recency,
        (TO_DATE('2018/05/12', 'yyyy/mm/dd') - MIN(PICKUP_DATE)) AS T,
        SUM(PRICE_TOTAL) AS monetary_value
    FROM TRANSACTIONS
    GROUP BY SRC_USER_ID
),
QUANTILE_THRESHOLDS AS (
    SELECT
        PERCENTILE_DISC(0.2) WITHIN GROUP (ORDER BY recency) AS r_q20,
        PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY recency) AS r_q40,
        PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY recency) AS r_q60,
        PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY recency) AS r_q80,
        PERCENTILE_DISC(0.2) WITHIN GROUP (ORDER BY frequency) AS f_q20,
        PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY frequency) AS f_q40,
        PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY frequency) AS f_q60,
        PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY frequency) AS f_q80,
        PERCENTILE_DISC(0.2) WITHIN GROUP (ORDER BY monetary_value) AS m_q20,
        PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY monetary_value) AS m_q40,
        PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY monetary_value) AS m_q60,
        PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY monetary_value) AS m_q80
    FROM RFM
)
SELECT
    r.SRC_USER_ID,
    r.recency,
    r.frequency,
    r.monetary_value,
    CASE
        WHEN r.recency <= q.r_q20 THEN 1
        WHEN r.recency <= q.r_q40 THEN 2
        WHEN r.recency <= q.r_q60 THEN 3
        WHEN r.recency <= q.r_q80 THEN 4
        ELSE 5
    END AS recency_quantile,
    CASE
        WHEN r.frequency <= q.f_q20 THEN 1
        WHEN r.frequency <= q.f_q40 THEN 2
        WHEN r.frequency <= q.f_q60 THEN 3
        WHEN r.frequency <= q.f_q80 THEN 4
        ELSE 5
    END AS frequency_quantile,
    CASE
        WHEN r.monetary_value <= q.m_q20 THEN 1
        WHEN r.monetary_value <= q.m_q40 THEN 2
        WHEN r.monetary_value <= q.m_q60 THEN 3
        WHEN r.monetary_value <= q.m_q80 THEN 4
        ELSE 5
    END AS monetary_quantile
FROM RFM r, QUANTILE_THRESHOLDS q;

关键注意事项 #

• 分位数函数选择：PERCENTILE_DISC返回数据集中的实际值（离散分位数），PERCENTILE_CONT返回插值后的连续值，根据业务需求选择即可。
• 等频vs等宽分箱：NTILE()是等频分箱（保证每个分箱的行数大致相等），WIDTH_BUCKET是等宽分箱（每个分箱的数值范围相等），按需选用。

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