最近做了京东的24年春招题,题目如下:
题目分析
本题要求统计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练习区进行练习。