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

### 这一道题是我抄写的。没有别的意思。就是不明白怎么做不明白怎么做这件事合理合法。


#窗口函数统计每天的 日动销率和滞销率。结果保留3位小数  窗口函数不能使用distinct 
   select dt,
          round(daysCnt/prodSum,3) sale_rate,
          round(1-daysCnt/prodSum,3) unsale_rate 
     from (
   select distinct 
          date_format(to1.event_time,'%Y-%m-%d') dt,
          (select count(distinct if(shop_id!='901',null,product_id)) 
             from tb_order_detail td join tb_order_overall too using(order_id) 
             join tb_product_info ti using(product_id)
            where datediff(to1.event_time,too.event_time) between 0 and 6) daysCnt , #统计7天内的商品销售个数
          (select count(distinct product_id) from tb_product_info where shop_id='901') prodSum  #901商铺总的商品数
     from tb_order_overall to1 
    where date_format(to1.event_time,'%Y%m%d') in ('20211001','20211002','20211003')  
    ) A 
    order by dt ;