大数据SQL题03 查询各品类销售商品的种类数及销量最高的商品

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

题目需求

从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：

category_id	category_name	sku_id	name	order_num	sku_cnt
1	数码	2	手机壳	302	4
2	厨卫	8	微波炉	253	4
3	户外	12	遮阳伞	349	4

需要用到的表：

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

商品信息表：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

商品分类信息表：category_info

category_id(分类id)	category_name(分类名称)
1	数码
2	厨卫
3	户外

解题思路

group by + 开窗函数共同使用

SELECT  category_id,
        category_name,
        sku_id,
        name,
        order_num,
        sku_cnt
FROM
(
	SELECT  t1.sku_id,
	        t2.name,
	        t3.category_id,
	        t3.category_name,
	        SUM(sku_num)                                                               AS order_num,
	        ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY  SUM(sku_num) DESC ) AS rn,
	        COUNT(sku_id) OVER ( PARTITION BY category_id )                            AS sku_cnt
	FROM order_detail t1
	LEFT JOIN sku_info t2
	ON t1.sku_id = t2.sku_id
	LEFT JOIN category_info t3
	ON t2.category_id = t3.category_id
	GROUP BY  t1.sku_id,
	          t2.name,
	          t3.category_id,
	          t3.category_name
) t4
WHERE rn = 1