SQL统计各品类销售出的商品种类及累计销量最好的商品

订单明细表： order_detail
字段：sku_id sku_num
商品信息表： sku_info
字段：sku_id name category_id
商品分类表： category_info
字段：category_id category_name

需求：从订单明细表order_detail 统计各品类销售出的商品种类及累计销量最好的商品
结果展示：品类id category_id 品类名称 category_name 商品id sku_id 商品名称 name 订单数量 order_num 商品种类数 sku_cnt

select b.category_id,b.category_name,b.sku_id,b.name,b.order_num,sku_num from (
    select 
     si.category_id
    ,si.category_name
    ,a.sku_id
    ,si.name
    ,a.order_num
    ,dense_rank() over(partition by si.category_id order by a.order_num desc) rn 
    ,count(*) over(partition by si.category_id) sku_cnt
     
     
     from (
        select sku_id,sum(sku_num) order_num from order_detail group by sku_id
    ) a left join sku_info si on a.sku_id = si.sku_id  left join category_info ci on si.category_id = ic.category_id
) b where rn = 1