本文分析了电商平台商品绩效查询的SQL实现难点，重点讨论了利润率过滤时机对查询结果的影响。

---

通过对比错误与正确解法，指出应先计算销售排名再过滤利润率（>20%）的关键步骤。

---

错误解法因过早过滤导致排名不准确，正确解法采用CTE分步处理：先计算所有商品利润率，再按类别分组排名，最后筛选排名前三且利润率达标的数据。

---

两种正确写法（AI修正版和老师版）均采用窗口函数ROW_NUMBER()实现分组排序，但老师版本额外使用SUM聚合处理多记录情况，体现了更严谨的业务逻辑处理。

示例：查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息

描述

【背景】：电商平台想要评估商品的销售绩效和利润贡献，以便优化商品策略。
【原始表】：
product_category （商品类别）表:

• product_id (商品 ID): 商品的唯一标识符
• category_id (类别 ID): 商品所属的类别 ID
• product_name (商品名称): 商品的名称

sales_and_profit （销售与利润）表:

• product_id (商品 ID): 商品的唯一标识符，用于关联商品类别表中的商品
• sales_amount (销售金额): 商品的销售金额
• cost_amount (成本金额): 商品的成本金额

【要求】：根据上面这两个表格，查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息，包含的字段：商品 ID、商品名称、所属类别 ID、销售金额、利润率（（销售金额 - 成本金额）/ 销售金额）。查询出来的数据先按照类别 ID 升序排列，再按照销售金额降序排列,如果金额一致按照产品ID升序排列。要求查询出来的表格的字段如下：

• product_id: 商品的唯一标识符。
• product_name: 商品的名称。
• category_id: 类别 ID。
• sales_amount: 销售金额。
• profit_rate: 利润率。

【示例】

product_category （商品类别）表:

sales_and_profit （销售与利润）表:

【按照要求查询出来的表】

【解释】

上述示例中，商品类别是1的产品ID有1、2、3、4，其中符合销售金额排名前三且利润率超过 20%的商品信息要求的只有产品ID2、3的产品，按照要求排序就是产品ID3排在产品ID2前面

解法

业务理解的难点：利润率的过滤要放在后面。

使用Deepseek写了七八遍都没写对，换其他的AI更拉胯，千问直接都写不出来，豆包写出来的差的更多。

---

老师讲解的时候也是卡在利润率这里，利润率过早的过滤会导致排名不准确。

错误的AI解法

WITH product_profit AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category_id,
        s.sales_amount,
        ROUND((s.sales_amount - s.cost_amount) / s.sales_amount, 2) AS profit_rate
    FROM product_category p
    INNER JOIN sales_and_profit s ON p.product_id = s.product_id
    WHERE (s.sales_amount - s.cost_amount) / s.sales_amount > 0.20
),
ranked_products AS (
    SELECT 
        product_id,
        product_name,
        category_id,
        sales_amount,
        profit_rate,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_amount DESC, product_id ASC) AS sales_rank
    FROM product_profit
)
SELECT 
    product_id,
    product_name,
    category_id,
    ROUND(sales_amount, 2) AS sales_amount,
    profit_rate
FROM ranked_products
WHERE sales_rank <= 3
ORDER BY category_id ASC, sales_amount DESC, product_id ASC;

对比输出

实际输出		预期输出
...	你期望输出的第 2 至 6 行与实际输出的第 2 至 7 行有不同
2	3|商品 C|1|8000.00|0.50	2	3|商品 C|1|8000.00|0.50
3	1|商品 A|1|900.00|0.94	3	1|商品 A|1|900.00|0.94
4	2|商品 B|1|800.00|0.75	4	2|商品 B|1|800.00|0.75
5	6|商品 2|2|1000.00|0.50	5	6|商品 2|2|1000.00|0.50
6	5|商品 1|2|800.00|0.38	6	5|商品 1|2|800.00|0.38
7	7|商品 3|2|200.00|0.75

正确的解法（AI+人工修改）

先计算排名，后过滤利润率。如果先过滤利润，再计算排名，结果就不对。

WITH product_profit AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category_id,
        s.sales_amount,#对比老师的写法，这里用的sum
        ROUND((s.sales_amount - s.cost_amount) / s.sales_amount, 2) AS profit_rate
    FROM product_category p
    INNER JOIN sales_and_profit s ON p.product_id = s.product_id
),
ranked_products AS (
    SELECT 
        product_id,
        product_name,
        category_id,
        sales_amount,
        profit_rate,
        row_number() OVER (PARTITION BY category_id ORDER BY sales_amount DESC, product_id ASC) AS sales_rank
    FROM product_profit
)
SELECT 
    product_id,
    product_name,
    category_id,
    ROUND(sales_amount, 2) AS sales_amount,
    profit_rate
FROM ranked_products
WHERE sales_rank <= 3 and profit_rate > 0.2 #业务理解的难点：利润率的过滤要放在后面
ORDER BY category_id ASC, sales_amount DESC, product_id ASC;

老师的解法

select 
product_id,product_name,category_id,sales_amount,
round(profit_rate,2) as profit_rate
from 
(select 
product_id,product_name,category_id,sales_amount,
profit_rate,
row_number() over (partition by category_id order by sales_amount desc) as rn
from 
(select 
m.product_id,
product_name,
category_id,
sum(sales_amount ) as sales_amount ,
sum(sales_amount - cost_amount )  /
sum(sales_amount )  as profit_rate
from product_category  m 
inner join sales_and_profit  n on m.product_id  = n.product_id 
group by m.product_id,
product_name,
category_id
) k ) m 
where rn <= 3 and profit_rate > 0.2
order by category_id,sales_amount desc,product_id