题解 | #国庆期间近7日日均取消订单量#

提一点，如果没有group 就不能使用having，其次用where的话会在查询前删掉9月底的数据，所以是再子查询一遍进行过滤

select * from
(select dt,round(sum(ok) over(order by dt rows 
                     between 6 preceding and current row)/7,2),
  round(sum(cancel) over(order by dt rows between 6 preceding
                      and current row)/7,2)
from 
(select date_format(order_time,"%Y-%m-%d") as dt,
       sum(if(start_time is null ,1,0)) as cancel,
       sum(if(start_time is not null and
              finish_time is not null ,1,0)) as ok
from tb_get_car_order
group by dt) base) tmp
where dt between "2021-10-01" and "2021-10-03"