根据商品销售情况进行商品分类
题目需求
从订单详情表中(order_detail)对销售件数对商品进行分类,0-5000为冷门商品,5001-19999位一般商品,20000往上为热门商品,并求出不同类别商品的数量
结果如下:
Category(类型) | Cn(数量) |
---|---|
一般商品 | 1 |
冷门商品 | 10 |
热门商品 | 1 |
代码实现
- 按照商品id计算商品销售件数的总和
select sku_id,
sum(sku_num) order_num
from order_detail
group by sku_id;
- 根据销售件数通过case when对商品进行分类
select sku_id,
order_num,
case
when order_num >= 0 and order_num <= 5000 then '冷门商品'
when order_num >= 5001 and order_num <= 19999 then '一般商品'
else '热门商品' end category
from (
select sku_id,
sum(sku_num) order_num
from order_detail
group by sku_id
) t1;
- 根据分类求出不通分类的商品数量
select category,
count(sku_id) cn
from (
select sku_id,
order_num,
case
when order_num >= 0 and order_num <= 5000 then '冷门商品'
when order_num >= 5001 and order_num <= 19999 then '一般商品'
else '热门商品' end category
from (
select sku_id,
sum(sku_num) order_num
from order_detail
group by sku_id) t1
) t2
group by category;