如何基于聚合排名区分Top300客户并合并其余为统一名称？

核心思路 #

利用窗口函数在查询内完成客户排名与分组标记，无需修改原表或复制数据，适配百万级数据量的性能需求：

• 通过CASE WHEN生成new_name字段，遵循big_name > med_name > small_name的优先级
• 按new_name分组，用窗口函数对客户按net_sales降序排名，筛选Top300客户
• 对非Top300客户统一标记为SMALL CLIENT
• 最终按status、region、class及处理后的客户维度聚合销售数据

分步实现逻辑 #

1. 生成new_name：通过嵌套CASE WHEN依次匹配big_name、med_name，未匹配的默认使用small_name
2. 客户排名：用ROW_NUMBER()窗口函数，以new_name为分区键，net_sales DESC为排序规则，给每个客户分配排名（若需允许并列排名，可替换为RANK()或DENSE_RANK()）
3. 客户分组标记：根据排名判断，Top300客户保留原客户标识，其余归为SMALL CLIENT
4. 聚合计算：按status、region、class及标记后的客户维度，聚合销售数据

完整SQL示例 #

假设原表名为customer_sales，包含字段：customer_id、status、region、class、big_name、med_name、small_name、net_sales

WITH customer_ranked AS (
    SELECT
        status,
        region,
        class,
        customer_id,
        net_sales,
        -- 生成new_name，按优先级匹配
        CASE
            WHEN big_name IS NOT NULL THEN big_name
            WHEN med_name IS NOT NULL THEN med_name
            ELSE small_name
        END AS new_name,
        -- 按new_name分组，对客户按net_sales降序排名
        ROW_NUMBER() OVER (PARTITION BY 
            CASE
                WHEN big_name IS NOT NULL THEN big_name
                WHEN med_name IS NOT NULL THEN med_name
                ELSE small_name
            END 
            ORDER BY net_sales DESC) AS sales_rank
    FROM customer_sales
),
customer_grouped AS (
    SELECT
        status,
        region,
        class,
        new_name,
        -- 标记Top300客户或合并为SMALL CLIENT
        CASE
            WHEN sales_rank <= 300 THEN customer_id
            ELSE 'SMALL CLIENT'
        END AS grouped_client,
        net_sales
    FROM customer_ranked
)
-- 最终聚合
SELECT
    status,
    region,
    class,
    new_name,
    grouped_client,
    SUM(net_sales) AS total_net_sales,
    COUNT(DISTINCT customer_id) AS customer_count
FROM customer_grouped
GROUP BY status, region, class, new_name, grouped_client
ORDER BY status, region, class, new_name, total_net_sales DESC;

示例表格 #

原数据示例（简化版） #

处理后结果示例（按new_name='BigCorp'的Top300及其他合并） #

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