Hive——刷题 国庆期间的7日动销率和滞销率
需求描述
动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。只要当天任一店铺有任何商品的销量就输出该天的结果，从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率
结果如下（截取部分）：
| category_id <string> （品类id） | first_sale_rate <decimal(16,2)> （动销) | first_unsale_rage <decimal(16,2)> （滞销) | second_sale_rate <decimal(16,2)> （动销) | second_unsale_rate <decimal(16,2)> （滞销) |
|---|---|---|---|---|
| 1 | 1.00 | 0.00 | 0.50 | 0.50 |
| 2 | 0.75 | 0.25 | 0.75 | 0.25 |
| 3 | 0.25 | 0.75 | 0.75 | 0.25 |
需要用到的表：
订单明细表：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 |
参考实现
select category_id, cast(first_sale_rate as decimal(16, 2)) first_sale_rate, cast(1 - first_sale_rate as decimal(16, 2)) first_unsale_rate, cast(second_sale_rate as decimal(16, 2)) second_sale_rate, cast(1 - second_sale_rate as decimal(16, 2)) second_unsale_rate, cast(third_sale_rate as decimal(16, 2)) third_sale_rate, cast(1 - third_sale_rate as decimal(16, 2)) third_unsale_rate, cast(fourth_sale_rate as decimal(16, 2)) fourth_sale_rate, cast(1 - fourth_sale_rate as decimal(16, 2)) fourth_unsale_rate, cast(fifth_sale_rate as decimal(16, 2)) fifth_sale_rate, cast(1 - fifth_sale_rate as decimal(16, 2)) fifth_unsale_rate, cast(sixth_sale_rate as decimal(16, 2)) sixth_sale_rate, cast(1 - sixth_sale_rate as decimal(16, 2)) sixth_unsale_rate, cast(seventh_sale_rate as decimal(16, 2)) seventh_sale_rate, cast(1 - seventh_sale_rate as decimal(16, 2)) seventh_unsale_rate from (select t.category_id, t.cnt1 / t1.cnt1 first_sale_rate, t.cnt2 / t1.cnt2 second_sale_rate, t.cnt3 / t1.cnt3 third_sale_rate, t.cnt4 / t1.cnt4 fourth_sale_rate, t.cnt5 / t1.cnt5 fifth_sale_rate, t.cnt6 / t1.cnt6 sixth_sale_rate, t.cnt7 / t1.cnt7 seventh_sale_rate from (select category_id, count(distinct if(create_date = '2021-10-01', sku_id, null)) cnt1, count(distinct if(create_date = '2021-10-02', sku_id, null)) cnt2, count(distinct if(create_date = '2021-10-03', sku_id, null)) cnt3, count(distinct if(create_date = '2021-10-04', sku_id, null)) cnt4, count(distinct if(create_date = '2021-10-05', sku_id, null)) cnt5, count(distinct if(create_date = '2021-10-06', sku_id, null)) cnt6, count(distinct if(create_date = '2021-10-07', sku_id, null)) cnt7 from (select category_id, create_date, od.sku_id, sku_num from order_detail od join sku_info si on od.sku_id = si.sku_id where sku_num > 0 and create_date between '2021-10-01' and '2021-10-07') t group by category_id) t join (select category_id, count(distinct if(from_date <= '2021-10-01', sku_id, null)) cnt1, count(distinct if(from_date <= '2021-10-02', sku_id, null)) cnt2, count(distinct if(from_date <= '2021-10-03', sku_id, null)) cnt3, count(distinct if(from_date <= '2021-10-04', sku_id, null)) cnt4, count(distinct if(from_date <= '2021-10-05', sku_id, null)) cnt5, count(distinct if(from_date <= '2021-10-06', sku_id, null)) cnt6, count(distinct if(from_date <= '2021-10-07', sku_id, null)) cnt7 from sku_info where from_date <= '2021-10-07' group by category_id) t1 on t.category_id = t1.category_id) t order by 1;
