SQL累计销售件数高于所属品类平均数的商品

表：sku_info
字段：sku_id name category_id
表：order_detail
字段：sku_id order_id create_date sku_num

需求：从订单明细表 order_detail 累计销售件数高于所属品类平均数的商品，期望结果：sku_id name sum_num cate_avg_num

select c.sku_id,c.name,c.sum_num,c.cate_avg_num from (
    select a.sku_id,b.name,a.sum_num,b.category_id,cast(avg(a.sum_num) over(partition by b.category_id ) as int) cate_avg_num from (
        select sku_id,sum(sku_num) sum_num from order_detail group by sku_id
    ) a left join (
        select sku_id,name,category_id from sku_info
    ) b on a.sku_id = b.sku_id 
) c where c.sum_num > c.cate_avg_num