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

不得不说最坑的点就是那一句，理解了就简单了

• ”只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0“

with dts  as( -- 2021国庆头三天 有销量的日期
select distinct 
    date(event_time) dt
from tb_order_overall 
where date(event_time) between '2021-10-01' and '2021-10-03'
),
 sale as (	 -- 901 店铺 每天销售的商品
 select distinct
     date(a.event_time) dt 
     ,b.product_id
 from tb_order_overall a 
 inner join tb_order_detail b
    on a.order_id = b.order_id
 inner join tb_product_info c
    on b.product_id = c.product_id
 where date(a.event_time) between '2021-09-25'  and '2021-10-03'
     and c.shop_id=901
 ),
 total as (-- 901 店铺 每天上线的商品
  select date(release_time) rel_dt
        ,product_id
  from tb_product_info
  where shop_id=901
 )
 -- 做笛卡尔积 判断时间差即可
 select 
    a.dt
    ,round(count(distinct if(datediff(a.dt,b.dt) between 0 and 6,b.product_id,null))
    /count(distinct if(c.rel_dt <=a.dt,c.product_id,null)),3) as sale_rate
    ,1-round(count(distinct if(datediff(a.dt,b.dt) between 0 and 6,b.product_id,null))
    /count(distinct if(c.rel_dt <=a.dt,c.product_id,null)),3) as unsale_rate
 from dts a
 join sale b
 join total c
 group by 1
 order by 1