如何在Amazon Redshift中按五分位数（Quintiles）分组分析客户订单数据？

看起来你在处理2021年11月的客户订单数据时遇到了SQL语法和逻辑上的问题，我先帮你梳理下现有查询的问题，再给出符合需求的解决方案。

现有查询的问题 #

我先帮你排查下当前SQL里的几个关键问题：

• WHERE子句语法错误：子查询里的from data_table and date_part(month ,order_date) = 11缺少了WHERE关键字，正确的写法应该是FROM data_table WHERE date_part(month, order_date) = 11 AND date_part(year, order_date) = 2021
• 分区字段不匹配：外层查询用了partition by customer_account_id，但子查询只返回了customer_number，这个字段不存在会直接导致语法错误
• 分位逻辑不符合需求：你需要的是五分位数（Quintiles，即5个分组，每组占20%客户），但当前查询取的是单个客户的95%、90%分位值——单个客户只有一条总金额记录，分位值完全没有意义，应该基于所有客户的总金额来划分分组
• 多余的GROUP BY：外层查询不需要再做GROUP BY，因为子查询已经按customer_number完成了分组聚合

正确的五分位数分组解决方案 #

下面是满足你需求的完整SQL，我用CTE（公共表表达式）来拆分逻辑，让代码更清晰：

WITH customer_monthly_data AS (
    -- 第一步：计算每个客户在2021年11月的基础指标
    SELECT
        customer_number,
        COUNT(ordernum) AS total_orders,
        SUM(amount_paid) AS total_amount,
        -- 单个客户的平均订单价值：总金额除以订单数
        SUM(amount_paid)::FLOAT / COUNT(ordernum) AS avg_order_value
    FROM data_table
    WHERE
        date_part('year', order_date) = 2021
        AND date_part('month', order_date) = 11
    GROUP BY customer_number
),
customer_quintiles AS (
    -- 第二步：按客户总金额划分五分位分组（5组，每组20%客户）
    SELECT
        *,
        -- NTILE(5) 按总金额降序分组，第1组是最高价值的20%客户，第5组是最低价值的20%
        NTILE(5) OVER (ORDER BY total_amount DESC) AS quintile_group,
        -- 可选：添加分位阈值，方便查看每组的金额边界
        PERCENTILE_CONT(0.8) OVER (ORDER BY total_amount DESC) AS top20_threshold,
        PERCENTILE_CONT(0.6) OVER (ORDER BY total_amount DESC) AS top40_threshold,
        PERCENTILE_CONT(0.4) OVER (ORDER BY total_amount DESC) AS top60_threshold,
        PERCENTILE_CONT(0.2) OVER (ORDER BY total_amount DESC) AS top80_threshold
    FROM customer_monthly_data
)
-- 第三步：按五分位分组聚合，得到各组的核心指标
SELECT
    quintile_group,
    CONCAT('第', quintile_group, '组（', 
           CASE quintile_group
               WHEN 1 THEN '最高20%客户'
               WHEN 2 THEN '次高20%客户'
               WHEN 3 THEN '中间20%客户'
               WHEN 4 THEN '次低20%客户'
               WHEN 5 THEN '最低20%客户'
           END, '）') AS group_description,
    COUNT(DISTINCT customer_number) AS customer_count,
    -- 组内客户的平均订单价值的均值
    ROUND(AVG(avg_order_value), 2) AS avg_order_value_per_customer,
    -- 组内的总下单数量
    SUM(total_orders) AS total_orders_in_group,
    -- 组内的总营收
    SUM(total_amount) AS total_revenue_in_group,
    -- 组内营收占整体营收的比例（保留2位小数）
    ROUND(SUM(total_amount)::FLOAT / (SELECT SUM(total_amount) FROM customer_monthly_data) * 100, 2) AS revenue_share_percent
FROM customer_quintiles
GROUP BY quintile_group
ORDER BY quintile_group;

代码逻辑说明 #

1. customer_monthly_data：先聚合每个客户在目标月份的总订单数、总金额，以及单个客户的平均订单价值
2. customer_quintiles：使用NTILE(5)将所有客户按总金额降序分成5个均等分组；同时添加的分位阈值可以帮你快速了解每组的金额范围
3. 最终聚合查询：按五分位分组，计算每组的客户数量、平均订单价值、总订单数、总营收以及营收占比，直观展示不同价值客户群的贡献

备选：如果需要前5%、次5%的百分位分组 #

如果你实际需求是查看**前5%、次5%**这类百分位数分组（而不是五分位），可以用PERCENT_RANK()来实现，示例如下：

WITH customer_monthly_data AS (
    SELECT
        customer_number,
        COUNT(ordernum) AS total_orders,
        SUM(amount_paid) AS total_amount,
        SUM(amount_paid)::FLOAT / COUNT(ordernum) AS avg_order_value
    FROM data_table
    WHERE
        date_part('year', order_date) = 2021
        AND date_part('month', order_date) = 11
    GROUP BY customer_number
),
customer_percentiles AS (
    SELECT
        *,
        -- 计算客户的营收百分排名（0=最低，1=最高）
        PERCENT_RANK() OVER (ORDER BY total_amount DESC) AS revenue_percent_rank
    FROM customer_monthly_data
)
SELECT
    CASE
        WHEN revenue_percent_rank <= 0.05 THEN '前5%客户'
        WHEN revenue_percent_rank <= 0.10 THEN '次5%客户（5%-10%）'
        WHEN revenue_percent_rank <= 0.20 THEN '10%-20%客户'
        ELSE '其他客户'
    END AS percentile_group,
    COUNT(DISTINCT customer_number) AS customer_count,
    ROUND(AVG(avg_order_value), 2) AS avg_order_value_per_customer,
    SUM(total_orders) AS total_orders_in_group,
    SUM(total_amount) AS total_revenue_in_group,
    ROUND(SUM(total_amount)::FLOAT / (SELECT SUM(total_amount) FROM customer_monthly_data) * 100, 2) AS revenue_share_percent
FROM customer_percentiles
GROUP BY percentile_group
ORDER BY
    CASE percentile_group
        WHEN '前5%客户' THEN 1
        WHEN '次5%客户（5%-10%）' THEN 2
        WHEN '10%-20%客户' THEN 3
        ELSE 4
    END;

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