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