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

-- 国庆头3天下单日期记录为dt，近7天商品销售数量记录为cnt，总上架商品数量记录为total_cnt
select dt ,round(cnt/total_cnt,3) as sale_rate ,round(1-cnt/total_cnt,3) as unsale_rate 
from (
select 
-- 取国庆前三天的日期
distinct date(event_time) as dt 
-- 计算901店铺前三天对应的7天销售商品数量
,(select count(distinct if(shop_id = 901,product_id,null))
from tb_order_overall
join tb_order_detail using(order_id)
join tb_product_info using(product_id)
where timestampdiff(day,event_time,t1.event_time) between 0 and 6) as cnt 
-- 计算901店铺所有商品数量
,(select count(distinct product_id) 
from tb_product_info 
where shop_id = 901) as total_cnt 
from tb_order_overall t1 
where date(event_time) between '2021-10-01' and '2021-10-03' ) t2
order by dt