从订单明细表统计各品类销售出的商品种类数及累积销量最高的商品
下面使用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学习日记