大数据SQL题10 查询销售件数高于品类平均数的商品

原题链接：http://practice.atguigu.cn/#/question/10/desc?qType=SQL

题目需求

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

解题思路

SELECT  sku_id,
        name,
        sum_num,
        cate_avg_num
FROM
(
	SELECT  sku_id,
	        name,
	        sum_num,
	        AVG(sum_num) OVER (PARTITION BY category_id) AS cate_avg_num
	FROM
	(
		SELECT  ord.sku_id,
		        sku.name,
		        sku.category_id,
		        SUM(sku_num) AS sum_num
		FROM order_detail ord
		JOIN sku_info sku
		ON ord.sku_id = sku.sku_id
		GROUP BY  ord.sku_id,
		          sku.name,
		          sku.category_id
	) t1
) t2
WHERE sum_num > cate_avg_num