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

因为数据是比较干净的，按日期升序，且中间没有缺失天数。故可使用窗口函数一步到位（偷懒了）

取固定窗口大小的数据进行统计用的也比较少，特此记录一下：

rows between 6 preceding and current row

select *
from (
    select date(co.finish_time) as dt,
        round(avg(count(co.fare)) over(rows 6 preceding), 2) as finish_num_7d,
        round(avg(sum(if(co.fare is null, 1, 0))) over(rows 6 preceding), 2) as cancel_num_7d
    from tb_get_car_order co
    group by date(co.finish_time)
) t1
where t1.dt between '2021-10-01' and '2021-10-03'

当然还有另一个方法，通过表的自连接

# 如下，算出9月25到10月3日每一天的接单量和未成单量
with temp as (
	select date(order_time) as dt,
		count(fare) as finish_order,
		sum(if(start_time is null, 1, 0)) as unfinish_order
	from tb_get_car_order
	where date(order_time) between '2021-09-25' and '2021-10-03'
	group by dt
)

# 在此基础上，使用表的自连接, 此处巧妙在于，连接产生笛卡尔积。而将笛卡尔积通过时间条件限制在仅产生7天的笛卡尔积，在根据时间进行分组即可得到结果
select t1.dt as dt，
	t2.dt as dt2，
	t2.finish_order as finish_order,
    t2.unfinish_order as unfinish_order
from temp as t1
join temp as t2 on t2.dt >= date_sub(t1.dt, interval 6 day) and t2.dt <= t1.dt
where t1.dt between '2021-10-01' and '2021-10-03'