题解 | #店铺901国庆期间的7日动销率和滞销率#

#算一下对应月份之前有上架的商品
  with tmp1 as (select product_id from tb_product_info where date_format(release_time,'%Y-%m-%d')<='2021-10-01' and shop_id='901'),
  tmp2 as (select product_id from tb_product_info where date_format(release_time,'%Y-%m-%d')<='2021-10-02' and shop_id='901'),
  tmp3 as (select product_id from tb_product_info where date_format(release_time,'%Y-%m-%d')<='2021-10-03' and shop_id='901')
#取出在日期内有销量且是上架商品的数量，然后除以再该日期前上架的商品，因为有三个日期，所以采用union分别计算再连接
( select  '2021-10-01'as dt,count(distinct a.product_id)/(select count(distinct product_id)from tmp1) as sale,
 1-count(distinct a.product_id)/(select count(distinct product_id)from tmp1) as unsale from
 (select order_id, product_id from tb_order_detail) b
inner join 
(select order_id, event_time from tb_order_overall
 where  `status`=1
and datediff('2021-10-01',event_time) between 0 and 6 ) c
on b.order_id=c.order_id
inner join 
  tmp1 a 
  on a.product_id=b.product_id)
  union
  ( select  '2021-10-02'as dt,count(distinct a.product_id)/(select count(distinct product_id)from tmp2) as sale,
 1-count(distinct a.product_id)/(select count(distinct product_id)from tmp2) as unsale from
 (select order_id, product_id from tb_order_detail) b
inner join 
(select order_id, event_time from tb_order_overall
 where  `status`=1
and datediff('2021-10-02',event_time) between 0 and 6 ) c
on b.order_id=c.order_id
inner join 
  tmp2 a 
  on a.product_id=b.product_id)
  union
     ( select  '2021-10-03'as dt,count(distinct a.product_id)/(select count(distinct product_id)from tmp3) as sale,
 1-count(distinct a.product_id)/(select count(distinct product_id)from tmp3) as unsale from
 (select order_id, product_id from tb_order_detail) b
inner join 
(select order_id, event_time from tb_order_overall
 where  `status`=1
and datediff('2021-10-03',event_time) between 0 and 6 ) c
on b.order_id=c.order_id
inner join 
  tmp3 a 
  on a.product_id=b.product_id)