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

with orders as(
    select date(order_time) d, sum(if(start_time is null, 1, 0)) ccnt, sum(if(start_time is null, 0, 1)) fcnt from tb_get_car_order where order_time between '2021-09-25' and '2021-10-07'
group by 1
)

select '2021-10-01', round(sum(fcnt)/7, 2), round(sum(ccnt)/7, 2) from orders where d between '2021-09-24' and '2021-10-01'
union
select '2021-10-02', round(sum(fcnt)/7, 2), round(sum(ccnt)/7, 2) from orders where d between '2021-09-26' and '2021-10-02'
union
select '2021-10-03', round(sum(fcnt)/7, 2), round(sum(ccnt)/7, 2) from orders where d between '2021-09-27' and '2021-10-03'

不滑动窗口版本，注意是sum(if(start_time is null, 1, 0))，而不是count(if(start_time is null, 1, 0))