查询各品类销售商品的种类数及销量最高的商品
题目需求
从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品,期望结果如下:
category_id(分类id) | category_name(分类名称) | sku_id(销量最好的商品id) | name(商品名称) | order_num(销量最好的商品销量) | order_cnt(商品种类数量) |
---|---|---|---|---|---|
1 | 数码 | 2 | 手机壳 | 6044 | 4 |
2 | 厨卫 | 8 | 微波炉 | 253 | 4 |
3 | 户外 | 12 | 遮阳伞 | 20682 | 4 |
代码实现
select
category_id,
category_name,
sku_id,
name,
order_num,
sku_cnt
from
(
select
od.sku_id,
sku.name,
sku.category_id,
cate.category_name,
order_num,
rank() over (partition by sku.category_id order by order_num desc) rk,
count(distinct od.sku_id) over (partition by sku.category_id) sku_cnt
from
(
select
sku_id,
sum(sku_num) order_num
from order_detail
group by sku_id
) od
left join sku_info sku
on od.sku_id = sku.sku_id
left join category_info cate
on sku.category_id = cate.category_id
) t1
where rk = 1;