获取销售额Top200分销商并实现同额同排名的SQL问题

需求概述 #

现有订单数据集，需获取按总销售额排名前200的分销商，要求总销售额相同的用户排名一致。涉及users、categories、user_category、orders、order_items、products共6张表，原SQL的排名逻辑存在问题，以下是正确实现方案。

正确实现步骤 #

1. 计算分销商总销售额 #

先关联各表，统计每个分销商的总销售额（需根据实际表结构调整关联条件，比如users表是否有分销商标识字段）：

WITH distributor_sales AS (
    SELECT
        u.id AS distributor_id,
        u.name AS distributor_name,
        SUM(oi.quantity * p.price) AS total_sales
    FROM users u
    JOIN user_category uc ON u.id = uc.user_id
    JOIN categories c ON uc.category_id = c.id
    JOIN orders o ON u.id = o.user_id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    -- 若users表有区分分销商的字段，添加WHERE条件过滤，比如：
    -- WHERE u.role = 'distributor'
    GROUP BY u.id, u.name
)

2. 应用同值同排名的函数 #

要实现总销售额相同则排名一致，需使用RANK()或DENSE_RANK()函数：

• RANK()：同销售额获相同排名，后续排名跳过空缺（例：1,1,3）
• DENSE_RANK()：同销售额获相同排名，后续排名不跳过空缺（例：1,1,2）
  根据业务需求选择，以下用RANK()示例：

, ranked_distributors AS (
    SELECT
        distributor_id,
        distributor_name,
        total_sales,
        RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
    FROM distributor_sales
)

3. 筛选前200名 #

由于同销售额排名一致，直接筛选排名≤200的记录，确保所有和第200名销售额相同的分销商都被纳入：

SELECT
    distributor_id,
    distributor_name,
    total_sales,
    sales_rank
FROM ranked_distributors
WHERE sales_rank <= 200
ORDER BY sales_rank, distributor_name;

常见错误点 #

• 误用ROW_NUMBER()：该函数会给同销售额的用户分配不同排名，不符合需求
• 表关联遗漏：未正确关联order_items与products，导致销售额计算错误
• 排序方向错误：未添加DESC，导致按销售额升序排名，拿到的是业绩最差的200名

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