Hive综合案例练习（中级）第十题：查询销售件数高于品类平均数的商品

Hive综合案例练习（中级）第十题：查询销售件数高于品类平均数的商品

查询销售件数高于品类平均数的商品

题目需求

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品，期望结果如下：

代码实现

select sku_id,name,sum_num,cate_avg_num
from
(select t1.sku_id,si.name,t1.sum_num,avg(t1.sum_num) over (partition by si.category_id) cate_avg_num,(t1.sum_num - avg(t1.sum_num) over (partition by si.category_id)) flagfrom (select sku_id,sum(sku_num) sum_numfrom order_detailgroup by sku_id )t1left join sku_info si on t1.sku_id=si.sku_id 
)t2
where flag > 0;set hive.pe.safety=false;
set de=nostrict;
select sku_id,name,sum_num,cate_avg_num
from (select od.sku_id,category_id,name,sum_num,avg(sum_num) over (partition by category_id) cate_avg_numfrom (select sku_id,sum(sku_num) sum_numfrom order_detailgroup by sku_id) odleft join(select sku_id,name,category_idfrom sku_info) skuon od.sku_id = sku.sku_id) t1
where sum_num > cate_avg_num;