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

select dt,
       round(count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = 901),3),
       round(1-count(distinct product_id)/(select count(product_id) from tb_product_info where shop_id = 901),3)
from
    (select
         date(event_time) dt
    from
         tb_order_overall
    where
          date(event_time) between '2021-10-01' and '2021-10-03') a
    left join(select
                     date(c.event_time) fdt,
                     b.product_id product_id
              from
                   tb_order_detail b
              left join
                    tb_order_overall c
              using(order_id)
              left join
                    tb_product_info d
              using(product_id)
              where d.shop_id = 901) f
    on
        datediff(a.dt,f.fdt) between 0 and 6
group by
         dt
order by
         dt;

之前整过一个类似的国庆期间每类视频的点击量与转发量

这次里面的窗口函数方法就不能用了，其实非要用也不是不可以，用一个select生成10天对应日期把空白日期填上就行，这里用了自连接，或用union也可以