题解 | #国庆期间近7日日均取消订单量#不用窗口函数

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

策略是筛选出10.1-10.3之间并去重的dt

基于这个基础在执行两个select字句

查询与当前dt间隔0-6天的数据并汇总