Hive 刷题——查询销售件数高于品类平均数的商品

需求描述

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品

期望结果如下：

sku_id	name	sum_num	cate_avg_num
2	手机壳	6044	1546
5	破壁机	242	194
7	热水壶	252	194
8	微波炉	253	194
12	遮阳伞	20682	5373

需要用到的表：

商品信息表：sku_info

sku_id(商品id)	name(商品名称)	category_id(分类id)	from_date(上架日期)	price(商品价格)
1	xiaomi 10	1	2020-01-01	2000
6	洗碗机	2	2020-02-01	2000
9	自行车	3	2020-01-01	1000

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

参考SQL

select sku_id, name, sum_num, cate_avg_num
from (select od.sku_id, name, category_id, sum(sku_num) sum_num
      from order_detail od
               join sku_info si on od.sku_id = si.sku_id
      group by od.sku_id, name, category_id) t
         join
     (select category_id,
             cast(avg(sum_num) as bigint) cate_avg_num
      from (select od.sku_id, name, category_id, sum(sku_num) sum_num
            from order_detail od
                     join sku_info si on od.sku_id = si.sku_id
            group by od.sku_id, name, category_id) t
      group by category_id) t1
     on t.category_id = t1.category_id and sum_num > cate_avg_num;