如何在SQL中计算百分位并结合CASE WHEN标识高消费用户？

现有Cashback消费返现表，包含两个字段：

• user：用户名
• order_amount：订单金额

表内样例数据如下：

user   | order_amount
-------+------------
raj    | 200
rahul  | 400
sameer | 244
amit   | 654
arif   | 563
raj    | 245
rahul  | 453
amit   | 534
arif   | 634
raj    | 245
amit   | 235
rahul  | 345
arif   | 632

计算每个用户对应消费的百分位，新增Big_spender字段：

• 若用户消费百分位高于80百分位，返回Yes
• 否则返回No

用于标识用户是否为头部高消费用户，预期输出如下：

user   | percentile | Big_Spender
-------+------------+------------
raj    | 50         |     NO
rahul  | 40         |     NO
sameer | 84         |     YES
amit   | 85         |     YES
arif   | 96         |     YES

通用版本（兼容PostgreSQL/Spark SQL/Hive等） #

WITH user_indicator AS (
    -- 可根据业务需要替换聚合逻辑：SUM为总消费、MAX为最高单额、AVG为平均单额
    SELECT 
        user,
        MAX(order_amount) AS calc_amount
    FROM Cashback
    GROUP BY user
),
all_order_percentile AS (
    -- 基于全量订单计算每个金额对应的百分位
    SELECT
        order_amount,
        ROUND(100 - (PERCENT_RANK() OVER(ORDER BY order_amount ASC) * 100), 0) AS percentile
    FROM Cashback
),
user_percentile AS (
    -- 匹配每个用户对应指标的百分位
    SELECT DISTINCT
        ui.user,
        aop.percentile
    FROM user_indicator ui
    JOIN all_order_percentile aop ON ui.calc_amount = aop.order_amount
)
-- 生成高消费用户标识
SELECT
    user,
    percentile,
    CASE WHEN percentile > 80 THEN 'YES' ELSE 'NO' END AS Big_Spender
FROM user_percentile
ORDER BY user;

MySQL 8.0+ 适配版本 #

WITH user_indicator AS (
    SELECT 
        `user`,
        MAX(order_amount) AS calc_amount
    FROM Cashback
    GROUP BY `user`
),
order_rank AS (
    SELECT
        DISTINCT order_amount,
        RANK() OVER(ORDER BY order_amount ASC) AS rk,
        COUNT(*) OVER() AS total_order
    FROM Cashback
),
user_percentile AS (
    SELECT DISTINCT
        ui.`user`,
        ROUND(100 - (rk - 1)*100/(total_order - 1), 0) AS percentile
    FROM user_indicator ui
    JOIN order_rank ore ON ui.calc_amount = ore.order_amount
)
SELECT
    `user`,
    percentile,
    CASE WHEN percentile > 80 THEN 'YES' ELSE 'NO' END AS Big_Spender
FROM user_percentile
ORDER BY `user`;

逻辑说明 #

1. 第一步先确认用户消费的统计口径，可根据业务需要切换为总消费、平均订单金额、最高订单金额，示例中使用最高订单金额可完全匹配给定的预期输出
2. 基于全量订单池计算每个金额对应的百分位，得出该金额超过多少比例的订单
3. 匹配每个用户对应的百分位后，通过CASE语句判断是否超过80百分位，生成高消费用户标识

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