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

with t1 as (select a.product_id,event_time,(select count(1) from tb_product_info where shop_id = 901) total_num
from tb_product_info a 
left join tb_order_detail b 
on a.product_id = b.product_id
left join tb_order_overall c 
on b.order_id = c.order_id
where shop_id = 901
and status = 1
)



select dt,sale_rate,unsale_rate from (select '2021-10-01' dt,round(count(distinct case when date(event_time) between '2021-09-25' and '2021-10-01' then product_id else null end)/total_num,3) sale_rate,
round(1-count(distinct case when date(event_time) between '2021-09-25' and '2021-10-01' then product_id else null end)/total_num,3) unsale_rate,
(select count(1) from tb_order_overall where date(event_time) = '2021-10-01') yes_or_no
from t1
union 
select '2021-10-02' dt,round(count(distinct case when date(event_time) between '2021-09-26' and '2021-10-02' then product_id else null end)/total_num,3) sale_rate,
round(1-count(distinct case when date(event_time) between '2021-09-26' and '2021-10-02' then product_id else null end)/total_num,3) unsale_rate,
(select count(1) from tb_order_overall where date(event_time) = '2021-10-02') yes_or_no from t1
union
select '2021-10-03' dt,round(count(distinct case when date(event_time) between '2021-09-27' and '2021-10-03' then product_id else null end)/total_num,3) sale_rate,
round(1-count(distinct case when date(event_time) between '2021-09-27' and '2021-10-03' then product_id else null end)/total_num,3) unsale_rate,
(select count(1) from tb_order_overall where date(event_time) = '2021-10-03') yes_or_no from t1) u
where yes_or_no != 0