Hive综合案例练习（中级）第三题：查询各品类销售商品的种类数及销量最高的商品*

查询各品类销售商品的种类数及销量最高的商品

题目需求

从订单明细表(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;