需求描述
从订单明细表(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 | 户外 |
解题思路
这个题目可用拆解为两个部分
- 取各品类下的商品数量
- 取各品类下销售最好的那一款商品
然后将两部分逻辑通过 品类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;
标签：category,sku,name,Hive,order,si,数及,id,刷题
From： https://www.cnblogs.com/wdh01/p/17008011.html