最近做了京东的24年春招题,题目如下: alt alt

题目分析

本题要求统计2024年内销量大于0且平均评分低于4分的商品,输出商品ID、商品名称、总销量和平均评分(保留两位小数),并按平均评分升序、商品ID升序排序。
输出字段

  • product_id(商品ID)
  • product_name(商品名称)
  • total_quantity(2024年总销量)
  • average_rating(平均评分,保留两位小数,且小于4)

输出顺序

  • 先按平均评分升序(average_rating)
  • 若平均评分相同,则按商品ID升序(product_id)

涉及知识点

  • SQL 多表连接(JOIN)
  • 子查询与分组聚合(SUM、AVG、GROUP BY、HAVING)
  • 条件筛选(WHERE、HAVING)
  • 字段别名与排序(ORDER BY)
  • ROUND函数

解答步骤

1. 统计2024年每个商品的总销量

  • sales_underline 表中,筛选2024年内的销售数据,按商品ID分组,统计每个商品的总销量。
select product_id,
sum(quantity) as total_quantity
from sales_underline
where year(sale_date) = '2024'
group by product_id

2. 统计每个商品的平均评分(低于4分)

  • reviews_underline 表中,按商品ID分组,计算平均评分,只保留平均评分小于4的商品。
select product_id, avg(rating) as average_rating
from reviews_underline
group by product_id
having avg(rating) < 4

3. 关联商品信息

  • products_underline 商品表与上述两个子查询通过商品ID连接,获取商品名称、总销量和平均评分。
from products_underline p
join ( -- 步骤1子查询 ) s on s.product_id = p.product_id
join ( -- 步骤2子查询 ) r on r.product_id = p.product_id

4. 输出与排序

  • 输出商品ID、商品名称、总销量、平均评分(保留两位小数)。
  • 按平均评分升序、商品ID升序排序。
select p.product_id, p.product_name, s.total_quantity, round(r.average_rating,2) as average_rating
group by p.product_id
order by average_rating, product_id

完整代码

select p.product_id, p.product_name, s.total_quantity, round(r.average_rating,2) as average_rating
from products_underline p
join (
    select product_id,
    sum(quantity) as total_quantity
    from sales_underline
    where year(sale_date) = '2024'
    group by product_id
) s on s.product_id = p.product_id
join (
    select product_id, avg(rating) as average_rating
    from reviews_underline 
    group by product_id
    having avg(rating) < 4
) r on r.product_id = p.product_id
group by p.product_id
order by average_rating, product_id

近似题目练习推荐

电商平台想要了解不同商品在不同月份的销售趋势

  • 知识点:子查询与分组聚合、分组统计、JOIN

用户订单信息查询

  • 知识点:分组聚合、SQL连接、聚合函数、排序

如需更多类似题目,可在牛客网SQL练习区进行练习。