3. 查询各品类销售商品的种类数及销量最高的商品

题目需求

从订单明细表(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	户外

实现一(啰嗦版本)

-- 各品类销售出的商品种类数及累积销量最好的商品 ---> 各品类销售出的商品种类数sku_cnt join 各品类累积销量最好(sku_num加和最大)的商品sku_id、name、order_num

with t4 as 
(
    -- 1） 先将 order_detail、sku_info、category_info join起来
    select
        t1.order_id order_id,
        t1.sku_id sku_id,
        t1.sku_num sku_num,
        t2.name name,
        t3.category_id category_id,
        t3.category_name category_name
    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
)

select
    t5.category_id category_id,
    t5.category_name category_name,
    t5.sku_cnt sku_cnt,
    t6.sku_id sku_id,
    t6.name name,
    t6.order_num order_num
from
(
  -- 2） 按照 category_id 进行分组。统计每个 category_id 销售出的商品种类数
  select
      category_id,
      category_name,
      count(distinct sku_id) sku_cnt
  from t4
  group by category_id,category_name
)t5
join
(
  -- 4) 查询各品类累积销量最好的商品的信息
  select
      category_id,
      category_name,
      sku_id,
      name,
      order_num
  from
  (
    -- 3）查询各品类所有商品的累积销量
      select
          category_id,
          category_name,
          sku_id,
          name,
          sum(sku_num) order_num,
          rank() over(partition by category_id order by sum(sku_num) desc) rn
      from t4
      group by category_id,category_name,sku_id,name
  )t7
  where rn=1
)t6
on t5.category_id=t6.category_id;

实现二(精简版本)

with t4 as 
(
    -- 1） 先将 order_detail、sku_info、category_info join起来
    select
        t1.order_id order_id,
        t1.sku_id sku_id,
        t1.sku_num sku_num,
        t2.name name,
        t3.category_id category_id,
        t3.category_name category_name
    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
)

select
	category_id,
    category_name,
    sku_id,
    name,
	sku_cnt,
    order_num
from
(
  select 
      category_id,
      category_name,
      sku_id,
      name,
      -- note1: 在分组后使用 开窗函数，对应的数据是 '分组字段+组内数据'。不用考虑 分组字段和组内数据的区别。总之，他们还是一条记录。
      count(distinct sku_id) over(partition by category_id) sku_cnt,
      sum(sku_num) order_num,
      rank() over(partition by category_id order by sum(sku_num) desc) rn
  from t4
  group by category_id,category_name,sku_id,name
)t5
where rn=1

总结

在 group by 分组后使用 over()开窗函数，对应的数据是 ‘分组字段+组内数据’。不用考虑 分组字段和组内数据的区别。总之，他们还是一条记录。

题目来源

http://practice.atguigu.cn/#/question/3/desc?qType=SQL