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

with
    combineday as (
        select
            '2021-10-01' as date
        union
        select
            '2021-10-02'
        union
        select
            '2021-10-03'
    ),
    dataList as (
        select
            tb_get_car_record.uid,
            order_id,
            start_time,
            finish_time
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
    )
select
    date,
    round(count(start_time)/7,2),#已完成的订单总量
    round(sum(if(start_time is null ,1 ,0))/7,2)#取消订单量
from
    combineday,
    dataList
where
    datediff (date, date(finish_time)) between 0 and 6
group by date

本题解法一：

先定义一个临时表，内含10-1，10-2, 10-3，与组合表进行一个连接，只要判断finish_time在国庆前三日的时间差为0-6即可，

根据国庆前三日进行聚合，得出总计的订单量和总计的取消量除以7即可。