统计各品类最畅销的商品

统计各品类最畅销的商品

问题:
统计各品类售出商品的种类数和销量最好的商品

1- 数据准备

表1: t_order_detail (订单明细表)
	id(订单明细id), order_id(订单id), sku_id(商品id), create_date(订单创建日期), price(商品单价), quantity(商品数量)

表2: t_sku_info (商品信息表)
	sku_id(商品id), goods_name(商品名称), category_id(品类id), price(商品单价), del_flag(删除状态; 0:正常;1:删除)

表3; t_category_info (品类信息表)
	category_id(品类id), category_name(品类名称)

-- 数据准备
WITH t_order_detail AS (
SELECT * FROM (
  VALUES 
      (1002, 1101, 1203, '2023-01-01', 1100, 3)
    , (1012, 1101, 1211, '2023-01-03', 1200, 2)
    , (1016, 1102, 1205, '2023-01-02', 2000, 1)
    , (1015, 1103, 1204, '2023-01-04', 1500, 3)
    , (1018, 1103, 1206, '2023-01-05', 1700, 5)
    , (1022, 1104, 1207, '2023-01-12', 1100, 6)
    , (1025, 1105, 1202, '2023-01-25', 1200, 3)
    , (1035, 1106, 1203, '2023-01-13', 1100, 2)
    , (1023, 1107, 1204, '2023-02-02', 1500, 3)
    , (1036, 1108, 1202, '2023-02-04', 1200, 1)
    , (1041, 1109, 1211, '2023-02-03', 1200, 2)
    , (1052, 1110, 1204, '2023-02-01', 1500, 3)
    , (1058, 1111, 1206, '2023-02-05', 1700, 4)
    , (1043, 1112, 1205, '2023-02-06', 2000, 2)
    , (1066, 1113, 1203, '2023-02-11', 1100, 5)
    , (1061, 1114, 1204, '2023-02-16', 1500, 4)
) AS table_name(id, order_id, sku_id, create_date, price, quantity)
)

, t_sku_info AS (
SELECT * FROM (
  VALUES 
      (1203, '小米', 6 , 1100, 0)
    , (1211, '苹果', 6 , 1200, 0)
    , (1205, 'OPPO', 6 , 2000, 0)
    , (1204, '热水器', 3 , 1500, 0)
    , (1206, '吹风机', 3 , 1700, 0)
    , (1207, '跑步机', 7 , 1100, 0)
    , (1202, '划船机', 7 , 1200, 0)
    , (1209, '椭圆机', 7 , 1800, 1)
) AS table_name(sku_id, goods_name, category_id, price, del_flag)
)

, t_category_info AS (
SELECT * FROM (
  VALUES 
      (6 , '数码产品')
    , (3 , '家用电器')
    , (7 , '运动器材')
) AS table_name(category_id, category_name)
)

2- 代码实现

-- 1. 统计每种品类在售的商品数量 -> t1
SELECT 
      a.category_id, b.category_name
    , COUNT(DISTINCT a.sku_id) AS sku_ct
FROM t_sku_info a 
LEFT JOIN t_category_info b 
ON a.category_id = b.category_id
WHERE a.del_flag = 0
GROUP BY a.category_id, b.category_name
;

category_id	category_name	sku_ct
3	家用电器	2
6	数码产品	3
7	运动器材	2

SELECT -- 统计每种商品的销量
      b.category_id, a.sku_id, b.goods_name
    , SUM(quantity) AS order_sum
FROM t_order_detail a 
LEFT JOIN t_sku_info b  
ON a.sku_id = b.sku_id
WHERE b.del_flag = 0
GROUP BY a.sku_id, b.goods_name, b.category_id
;

category_id	sku_id	goods_name	order_sum
7	1202	划船机	4
6	1203	小米	10
3	1204	热水器	13
6	1205	OPPO	3
3	1206	吹风机	9
7	1207	跑步机	6
6	1211	苹果	4

-- 2. 统计每个品类中 最畅销的产品, 销量最高的产品  -> t2
SELECT 
    category_id, sku_id, goods_name, order_sum
FROM (
SELECT 
      category_id, sku_id, goods_name, order_sum
    , RANK() OVER(PARTITION BY category_id ORDER BY order_sum DESC) AS rk  -- 每种品类中, 销量降序排列
FROM (
SELECT -- 统计每种商品的销量
      b.category_id, a.sku_id, b.goods_name
    , SUM(quantity) AS order_sum
FROM t_order_detail a 
LEFT JOIN t_sku_info b  
ON a.sku_id = b.sku_id
WHERE b.del_flag = 0
GROUP BY a.sku_id, b.goods_name, b.category_id
) c
) d
WHERE rk = 1
;

category_id	sku_id	goods_name	order_sum	rk
3	1204	热水器	13	1
3	1206	吹风机	9	2
6	1203	小米	10	1
6	1211	苹果	4	2
6	1205	OPPO	3	3
7	1207	跑步机	6	1
7	1202	划船机	4	2

category_id	sku_id	goods_name	order_sum
3	1204	热水器	13
6	1203	小米	10
7	1207	跑步机	6

-- 3. 合并t1,t2 数据
SELECT 
      a.category_id, b.category_name, b.sku_ct, a.sku_id, a.goods_name, a.order_sum
FROM t2 a
LEFT JOIN t1 b  
ON a.category_id = b.category_id
;

category_id	category_name	sku_ct	sku_id	goods_name	order_sum
3	家用电器	2	1204	热水器	13
6	数码产品	3	1203	小米	10
7	运动器材	2	1207	跑步机	6

end