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

select o7.date_time,o7.sale_rate,o7.unsale_rate from (
select distinct DATE_FORMAT(o5.event_time,'%Y-%m-%d')  as event_time from 
                            tb_order_overall as o5 ) o6 
 inner join (
select a2.date_time,round(a2.cnt/a2.cnt_all,3) as sale_rate, 
round(1-a2.cnt/a2.cnt_all,3) as unsale_rate  from 
(select * 
 from 
(
#获取2021-10-01销售的产品数量
select '2021-10-01' as date_time,count(distinct t1.product_id) as cnt,
(select count(*) as cnt_all from tb_product_info as p2 where p2.shop_id='901') as cnt_all
from (
select o1.order_id,o1.product_id,o2.event_time from tb_order_detail as o1 inner join 
tb_order_overall as o2 on o1.order_id=o2.order_id 
where DATEDIFF('2021-10-01',o2.event_time)<7 and DATEDIFF('2021-10-01',o2.event_time)>=0 
and o1.product_id in 
(select p1.product_id from tb_product_info as p1 where p1.shop_id='901')) t1) a1 
union 
# 获取2021-10-02销售的产品数量
select '2021-10-02' as date_time,count(distinct t2.product_id) as cnt,
(select count(*) as cnt_all from tb_product_info as p2 where p2.shop_id='901') as cnt_all
from (
select o1.order_id,o1.product_id,o2.event_time from tb_order_detail as o1 inner join 
tb_order_overall as o2 on o1.order_id=o2.order_id 
where DATEDIFF('2021-10-02',o2.event_time)<7 and DATEDIFF('2021-10-02',o2.event_time)>=0 
and o1.product_id in 
(select p1.product_id from tb_product_info as p1 where p1.shop_id='901')) t2 
union 

# 获取2021-10-03销售的产品数量
select '2021-10-03' as date_time,count(distinct t3.product_id) as cnt,
(select count(*) as cnt_all from tb_product_info as p2 where p2.shop_id='901') as cnt_all
from (
select o1.order_id,o1.product_id,o2.event_time from tb_order_detail as o1 inner join 
tb_order_overall as o2 on o1.order_id=o2.order_id 
where DATEDIFF('2021-10-03',o2.event_time)<7 and DATEDIFF('2021-10-03',o2.event_time)>=0 
and o1.product_id in 
(select p1.product_id from tb_product_info as p1 where p1.shop_id='901')) t3 ) 
    a2   ) o7 on o6.event_time=o7.date_time