Hive综合案例练习（中级）第三十九题：国庆期间的7日动销率和滞销率

国庆期间的7日动销率和滞销率

题目需求

动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。

滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。

只要当天任一店铺有任何商品的销量就输出该天的结果

从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率

结果如下（截取部分）：

Category_id（品类id）	1号（动销)	1号（滞销)	2号（动销)	2号（滞销)	3号（动销)	3号（滞销)
1	1.0	0.0	0.5	0.5	0.75	0.25
2	0.75	0.25	0.75	0.25	0.75	0.25
3	0.25	0.75	0.75	0.25	0.75	0.25

2.39.2 代码实现

hive> 
-- 国庆每一天 每个商品品类有多少商品被销售了
select
  t1.category_id,
  sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
  sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
  sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
  sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
  sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
  sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
  sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
from
  (
    select 
      distinct 
      si.category_id,
      od.create_date,
      si.name
    from 
      order_detail od
    join
      sku_info si
    on
      od.sku_id=si.sku_id
    where
      od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
)t1
group by
  t1.category_id

-- 每一天的动销率 和 滞销率
select
  t2.category_id,
  t2.`第1天`/t3.cn,
  1-t2.`第1天`/t3.cn,
  t2.`第2天`/t3.cn,
  1-t2.`第2天`/t3.cn,
  t2.`第3天`/t3.cn,
  1-t2.`第3天`/t3.cn,
  t2.`第4天`/t3.cn,
  1-t2.`第4天`/t3.cn,
  t2.`第5天`/t3.cn,
  1-t2.`第5天`/t3.cn,
  t2.`第6天`/t3.cn,
  1-t2.`第6天`/t3.cn,
  t2.`第7天`/t3.cn,
  1-t2.`第7天`/t3.cn
from
  (
   select
     t1.category_id,
     sum(if(t1.create_date='2021-10-01',1,0)) `第1天`,
     sum(if(t1.create_date='2021-10-02',1,0)) `第2天`,
     sum(if(t1.create_date='2021-10-03',1,0)) `第3天`,
     sum(if(t1.create_date='2021-10-04',1,0)) `第4天`,
     sum(if(t1.create_date='2021-10-05',1,0)) `第5天`,
     sum(if(t1.create_date='2021-10-06',1,0)) `第6天`,
     sum(if(t1.create_date='2021-10-07',1,0)) `第7天`
   from
     (
       select 
         distinct 
         si.category_id,
         od.create_date,
         si.name
       from 
         order_detail od
       join
         sku_info si
       on
         od.sku_id=si.sku_id
       where
         od.create_date>='2021-10-01' and od.create_date<='2021-10-07'
   )t1
   group by
     t1.category_id
   )t2
join
  (
    select
      category_id,
      count(*) cn
    from
      sku_info
    group by
      category_id
    )t3
on 
  t2.category_id=t3.category_id