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

with t as (
	select product_id,order_id,date(event_time) as dt,shop_id
	from tb_order_detail  #order_id is not null，左连接选出的店铺和product_id都是有销量的
	left join tb_order_overall using(order_id)
	left join tb_product_info using(product_id)
	#where order_id is not null
	order by dt
	)
select distinct t1.dt as dt,round(sale_cnt1/cnt,3) as sale_rate,round(1-sale_cnt1/cnt,3) as unsale_rate
from t
left join (
		select '2021-10-01' as dt,
					 count(distinct if(shop_id='901',product_id,null)) as sale_cnt1
		from t
		where datediff('2021-10-01',dt)<=6 and datediff('2021-10-01',dt)>=0 
		union all
		select '2021-10-02' as dt,
					 count(distinct if(shop_id='901',product_id,null)) as sale_cnt1
		from t
		where datediff('2021-10-02',dt)<=6 and datediff('2021-10-02',dt)>=0 
		union all
		select '2021-10-03' as dt,
					 count(distinct if(shop_id='901',product_id,null)) as sale_cnt1
		from t
		where datediff('2021-10-03',dt)<=6 and datediff('2021-10-03',dt)>=0 
) as t1 using(dt)
left join (select '2021-10-01' as dt,count(product_id) as cnt
					 from tb_product_info
					 where date(release_time)<='2021-10-01' and shop_id='901'
					 union all
					 select '2021-10-02' as dt,count(product_id) as cnt
					 from tb_product_info
					 where date(release_time)<='2021-10-02' and shop_id='901'
					 union all
					 select '2021-10-03' as dt,count(product_id) as cnt
					 from tb_product_info
					 where date(release_time)<='2021-10-03' and shop_id='901') as t2 using(dt)	
where t1.dt between '2021-10-01' and '2021-10-03' and t.dt is not null #保证设定的三个日期一定是有卖出产品的日期
order by dt