with date_check as
(select distinct date(event_time) as dt
from tb_order_overall
where date(event_time) between '20211001' and '20211003'
),
b as
(select date(event_time) dt,td.product_id,release_time
from tb_order_detail td 
join tb_order_overall too on td.order_id = too.order_id
join  tb_product_info ti on td.product_id = ti.product_id
where shop_id=901)

select d.dt,round(count(distinct b.product_id)/(select count(product_id) from tb_product_info where release_time <= d.dt and shop_id =901),3) sale_rate,
1-round(count(distinct b.product_id)/(select count(product_id) from tb_product_info where release_time <= d.dt and shop_id =901),3) unsale_rate
from date_check d
left join b on b.dt between date_sub(d.dt,interval 6 day) and d.dt

group by d.dt
order by d.dt

三刷 还是不熟练。

  1. date_check d提取10月头三天，
  2. b无需算每天的销量，因为要求的是头三天中每天的近七天的商品种类数，提取出每天的关键字段，
  3. 主查询将d和b连接，在售的商品数通过select查询，而非从b取，且不要忘记shop_id=901