从订单明细表统计各品类销售出的商品种类数及累积销量最高的商品

从订单明细表统计各品类销售出的商品种类数及累积销量最高的商品

下面使用MYSQL8语法，用到窗口函数，且sql_mode=only_full_group_by的模式下编写

导入表结构

CREATE TABLE `3_order_detail` (
  `order_detail_id` int DEFAULT NULL,
  `sku_num` int DEFAULT NULL,
  `price` int DEFAULT NULL,
  `sku_id` int DEFAULT NULL,
  `create_date` varchar(255) DEFAULT NULL,
  `order_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `3_sku_info` (
  `category_id` int DEFAULT NULL,
  `from_date` varchar(255) DEFAULT NULL,
  `price` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sku_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `3_category_info` (
  `category_name` varchar(255) DEFAULT NULL,
  `category_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

思路：首先根据订单表统计每个商品的最高销量，以及每个商品类型卖出的商品种类，最后根据ROW_NUMBER() 根据商品种类分区，按照销量降序排序，最后获取销量最高的记录

-- 从订单明细表(order detail)统计各品类销售出的商品种类数及累积销量最高的商品	
SELECT * FROM (
	SELECT
		category_id,
		category_name,
		sku_id,
		best_selling_product,
		sku_cnt,
		ROW_NUMBER() OVER ( PARTITION BY category_name ORDER BY sale_count DESC ) AS sales_rank 
	FROM
		(
		SELECT
			ci.category_id,
			ci.category_name,
			si.sku_id,
			si.NAME AS best_selling_product,
			SUM( od.sku_num ) AS sale_count,
			COUNT( si.sku_id ) OVER ( PARTITION BY ci.category_id ) AS sku_cnt 
		FROM
			3_order_detail od
			INNER JOIN 3_sku_info si ON od.sku_id = si.sku_id
			INNER JOIN 3_category_info ci ON si.category_id = ci.category_id 
		GROUP BY
			ci.category_name,
			si.NAME,
			si.sku_id,
			ci.category_id 
		) t1 
	) t2 
WHERE
	sales_rank = 1

SQL学习日记