如何用SQL按80-20规则从累计收入中标记客户分组

假设你的customer表包含customer_name（客户名称）和revenue（客户营收）两个核心字段，我们可以通过窗口函数计算累计营收占比来实现需求，核心思路是：先按营收从高到低排序客户，再计算每个客户的累计营收占总营收的比例，最后根据累计占比是否达到80%来标记客户分组。

通用SQL代码（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH customer_revenue AS (
    -- 计算总营收
    SELECT 
        customer_name,
        revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM customer
),
customer_cumulative AS (
    -- 按营收降序排序，计算累计营收及累计占比
    SELECT 
        customer_name,
        revenue,
        total_revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
        ROUND(
            SUM(revenue) OVER (ORDER BY revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / total_revenue * 100,
            2
        ) AS cumulative_percent
    FROM customer_revenue
)
-- 最终标记客户分组
SELECT 
    customer_name,
    revenue,
    CASE
        -- 累计占比<=80%，或者当前客户加入后刚好超过80%但属于头部贡献者，都标记为80%营收客户
        WHEN cumulative_percent <= 80 OR (cumulative_percent > 80 AND LAG(cumulative_percent) OVER (ORDER BY revenue DESC) < 80)
        THEN '80%营收客户'
        ELSE '20%营收客户'
    END AS customer_segment
FROM customer_cumulative
ORDER BY revenue DESC;

代码说明 #

1. CTE customer_revenue：先计算出所有客户的总营收，为后续占比计算提供基准值。
2. CTE customer_cumulative：
   • 按revenue降序排列客户，确保高营收客户排在前面
   • 使用SUM(revenue) OVER (...)窗口函数计算从第一个客户到当前客户的累计营收
   • 计算累计营收占总营收的百分比，保留两位小数
3. 最终SELECT语句：
   • 通过CASE语句判断分组：如果累计占比≤80%，或者当前客户是刚好让累计占比超过80%的头部客户（比如你的A/B/C累计到85%，C仍属于头部），都标记为“80%营收客户”
   • 最后按营收降序输出，方便查看头部客户

注意事项 #

• 如果你的数据库不支持CTE（比如MySQL 5.x），可以把CTE替换成嵌套子查询
• 如果存在营收相同的客户，窗口函数的排序规则可以调整为ORDER BY revenue DESC, customer_name，避免排序歧义

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