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

题目主要是考察连接on条件的运用

需要注意的是，题目本身考虑的是订单的取消，所以实际上根本用不到用户订车记录

所以司机接单之前的取消不算在内（如果考虑了乘客在接单前取消订单，会导致有一个用例不一致）

with tb_log as
         (
             select
                    date(end_time) order_dt,
                    isnull(start_time) or null 'if_cancel'
			-- 如果要考虑乘客在司机接单前取消的情况，只需要把这里的left join替换成right即可
             from tb_get_car_order
                      left join tb_get_car_record using (order_id)
         )

select dt,
       round((count(1) - sum(if_cancel)) / 7, 2)  finish_num_7d,
       round(sum(if_cancel) / 7, 2) cancel_num_7d
from tb_log
    right join
    (
    select distinct order_dt dt from tb_log where order_dt between '2021-10-01' and '2021-10-03'
    ) tb_dt on order_dt between date_sub(dt, interval 6 day ) and dt
group by dt
order by dt;

当然了还有更简单的题解方式

select dt,
       round((count(1) - sum(if_cancel)) / 7, 2)  finish_num_7d,
       round(sum(if_cancel) / 7, 2) cancel_num_7d
from 

(
	select distinct date(order_time) dt 
	from tb_get_car_order 
	where order_dt between '2021-10-01' and '2021-10-03'  
) tb_dt 

left join 

(
	select date(order_time) order_dt,
		   isnull(start_time) or null 'if_cancel'
	from tb_get_car_order
) tb_log

on order_dt between date_sub(dt, interval 6 day ) and dt
group by dt
order by dt;