Hive 刷题——查询各品类销售商品的种类数及销量最高的商品

需求描述

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

需要用到的表

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

解题思路

这个题目可用拆解为两个部分

1. 取各品类下的商品数量
2. 取各品类下销售最好的那一款商品

然后将两部分逻辑通过 品类ID进行关联一下就行了。

参考SQL

select t.category_id, t1.category_name, t1.sku_id, t1.name, order_num, sku_cnt
from (select category_id, category_name, sum(1) sku_cnt
      from (select si.category_id, category_name, od.sku_id
            from order_detail od
                     join sku_info si on od.sku_id = si.sku_id
                     join category_info ci on si.category_id = ci.category_id
            group by si.category_id, category_name, od.sku_id) t
      group by category_id, category_name) t
         join
     (select category_id, category_name, sku_id, name, order_num
      from (select category_id,
                   category_name,
                   sku_id,
                   name,
                   order_num,
                   rank() over (partition by category_id,category_name order by order_num desc) rk
            from (select si.category_id, od.sku_id, category_name, name, sum(sku_num) order_num
                  from order_detail od
                           join sku_info si on od.sku_id = si.sku_id
                           join category_info ci on si.category_id = ci.category_id
                  group by si.category_id, od.sku_id, category_name, name) t) t
      where rk = 1) t1 on t.category_id = t1.category_id
order by 1;