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

写一个不用窗口函数的题解～

第一步，计算2021年10月1号到10月3号近七天的完成订单总数和取消订单总数

select distinct date(order_time) dt,
(select sum(if(start_time is null,0,1))
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  finish_num,
(select sum(if(start_time is null,1,0))
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  cancel_num
from
tb_get_car_order t1
where
date(order_time) between '2021-10-01' and '2021-10-03'

得到下表

最后计算7天的日平均值得到最终的sql

select dt,round(finish_num/7,2) finish_num_7d,
round(cancel_num/7,2) cancel_num_7d
from
(select distinct date(order_time) dt,
(select sum(if(start_time is null,0,1)) 
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  finish_num,
(select sum(if(start_time is null,1,0)) 
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  cancel_num
from 
tb_get_car_order t1
where 
date(order_time) between '2021-10-01' and '2021-10-03')t2