最近做了京东的24年春招题，题目如下：

alt

题目分析

本题要求在每个商品类别（category_id）内，选出销售额排名前3且利润率不低于20%的商品，输出商品ID、商品名称、类别ID、销售额和利润率。
输出字段：

  • product_id（商品ID）
  • product_name（商品名称）
  • category_id（类别ID）
  • sales_amount（销售额）
  • profit_rate（利润率，保留两位小数）

输出顺序：

  • 先按类别ID升序（category_id asc）
  • 再按销售额降序（sales_amount desc）
  • 最后按商品ID升序（product_id asc）

涉及知识点：

  • SQL 多表连接（JOIN）
  • 窗口函数（RANK() OVER PARTITION BY ... ORDER BY ...）
  • 分组排名与筛选
  • 利润率计算与ROUND
  • 多条件排序

解答步骤

1. 计算每个商品的销售额、成本额及排名

  • 先将 product_category 与 sales_and_profit 通过商品ID连接，获取每个商品的销售额和成本额。
  • 用窗口函数 rank() over (partition by p.category_id order by s.sales_amount desc)，在每个类别内按销售额降序排名。
select s.sales_amount, s.product_id, s.cost_amount,
rank() over (partition by p.category_id order by s.sales_amount desc) as rk
from product_category p
join sales_and_profit s on p.product_id = s.product_id

2. 关联商品信息并筛选

  • 将上一步结果与 product_category 再次连接，获取商品名称和类别ID。
  • 只保留排名前3（rk <= 3）且利润率不低于20%的商品（(s.sales_amount - s.cost_amount)/s.sales_amount >= 0.2）。
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
join (
    -- 上一步子查询
) s on p.product_id = s.product_id
where rk <= 3 and (s.sales_amount - s.cost_amount)/s.sales_amount >= 0.2

3. 排序输出

  • 按类别ID升序、销售额降序、商品ID升序排序。
order by p.category_id asc, s.sales_amount desc, p.product_id asc

完整代码

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
join (
    select s.sales_amount, s.product_id, s.cost_amount,
    rank() over (partition by p.category_id order by s.sales_amount desc) as rk
    from product_category p
    join sales_and_profit s on p.product_id = s.product_id
) s on p.product_id = s.product_id
where rk <= 3 and (s.sales_amount - s.cost_amount)/s.sales_amount >= 0.2
order by p.category_id asc, s.sales_amount desc, p.product_id asc

近似题目练习推荐

获得积分最多的人(二)

  • 知识点：窗口函数、分组排名、条件筛选